Question

WP Machine shop makes deluxe and regular skis on a yearly schedule for the area skiing enthusiasts. They also supply both regular and deluxe skis to local sporting goods stores. A deluxe pair of skis requires 40 minutes for roughing and 20 minutes for finishing, whereas a regular pair of skis requires 20 minutes for roughing and 30 minutes for finishing. With only 8 hours for roughing and 6 hours of finishing time available per day and a profit realization of 4 Euros and 3 Euros for the deluxe and regular, respectively, what yearly mix of the two types of skis should be produced to meet the contract's requirement and maximize profits. Note: Show all your calculations! (40 pts.)

Answer 1

I have assumes 365 working days in a year.

Below is the screenshot of the LP table -

X FILE HOME INSERT PAGE LAYOUT FORMULAS III Calibri 11 ARA * Cut Еe Copy Format Painter Paste BIU A- III Clipboard Font X C1 : fx Deluxe A B E с D Deluxe Regular 1 2 3 4 5 Constraints Roughing Finishing 0 175200 6 0 131400 7 Profit 0 8 9

Below is the screenshot of the formula applied -

XUS 2020 - Excel FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW fx Insert Function Define Name - Σ ? A e Fx Use in Formula AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Used Time Reference Trigº Functions Manager Create from Selectie Function Library Defined Names c1 : fic Deluxe A B с D E Deluxe Regular 2 3 4 5 6 Constraints Roughing Finishing =C2*40+D2*20 =C2*20+D2*30 =365*8*60 =365*6*60 7 8 Profit =C2*4+D2*3 9

Below is the screenshot of the solver -

2020 - Excel FILE INSERT HOME PAGE LAYOUT FORMULAS ΤΙΓΙΛΙ DATA DELLA Solver Parameters х LÀ From Access Le From Web From Other Lì From Text Sources Get External Data Connections Properties Refresh All Edit Links Connections Existing Connections Set Objective: SCS8 ela To: Max O Min Value of: 0 : ✓ fic Deluxe By Changing Variable Cells: SC$2:$D$2 A B с D E F 1 Deluxe Regular 2 Subject to the Constraints: $C$2:$D$2 >= 0 SC$5:$C$6 <= $E$5:$E$6 Add 3 4 Change 5 Constraints Roughing Finishing 0 <- 175200 131400 6 0 - Delete 7 8 Profit 0 Reset All 9 10 Load/Save 11 Make Unconstrained variables Non-Negative 12 Select a Solving Method: Simplex LP 13 Options 14 15 Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. 16 17 18 19 Help Solve Close 20 21

Below is the screenshot of the result -

XI FILE HOME INSERT PAGE LAYOUT FORMULAS La From Access Connection Le From Web Properties From Other Existing Refresh Lì From Text Sources Connections All Edit Links Get External Data Connections c1 : fox Deluxe A B E 1 с Deluxe 3285 D Regular 2190 2 3 4 5 Constraints Roughing Finishing 175200 175200 6 131400 <= 131400 7 8 Profit 19710