Question

Can you please help me set this up in an a excel worksheet.

Section 8.1 Expanded: Constructing the nonlinear profit contribution expression

Let P_S and P_D represent the prices charged for each standard golf bag and deluxe golf bag respectively. Assume that “S” and “D” are demands for standard and deluxe bags respectively.

S = 2250 – 15P_S                                                                                                                                                  (8.1)

D = 1500 – 5P_D                                                                                                                                                   (8.2)

Revenue generated from the sale of S number of standard bags is P_S*S. Cost per unit production is $70 and the cost for producing S number of standard bags is 70*S.

So the profit for producing and selling S number of standard bags = revenue – cost = P_SS – 70S                      (8.3)

By rearranging 8.1 we get                            

15P_S = 2250 – S or

                                P_S = 2250/15 – S/15 or

                                P_S = 150 – S/15                                                                                                                                                  (8.3a)

Substituting the value of P_S from 8.3a in 8.3 we get the profit contribution of the standard bag:

                                (150 –S/15)S – 70S = 150S – S²/15 – 70S = 80S – S²/15                                                                      (8.4)

Revenue generated from the sale of D number of deluxe bags is P_D*D. Cost per unit production is $150 and the cost for producing D number of deluxe bags is 150*D.

So the profit for producing and selling D number of deluxe bags = revenue – cost = P_DD – 150D                     (8.4a)

By rearranging 8.2 we get                            

5P_D = 1500 – D or

                                P_D = 1500/5 – D/5 or

                                P_D = 300 – D/5                                                                                                                                                   (8.4b)

Substituting the value of P_D from 8.4b in 8.4a we get the profit contribution of the deluxe bags:

                                (300 -D/5)D – 150D = 300D – D²/5 – 150D = 150D – D²/5                                                                 (8.4c)

By adding 8.4 and 8.4c we get the total profit contribution for selling S standard bags and D deluxe bags.

                                Total profit contribution = 80S –S²/15 + 150D – D²/5                                                                         (8.5)

Homework assignment:

Reconstruct new objective function for 8.5 by changing “15P_S” to “8P_S” in 8.1, “5P_D” to “10P_D” in 8.2, cost per unit standard bag from 70 to “last two digits of your UTEP student ID 55” and cost per unit deluxe bagfrom 150 to 125. Keep other parameter values unchanged. Use up to 2 decimal points accuracy. Substitute the new expression for 8.5 in the excel solver workbook as explained in the class and solve for the optimal combination values for S and D. (just one page excel printout). Write/type your full name (first name first)Use S and D instead of b15 or c15 in the formulation. If you fail to follow the instructions, you will lose points.

Answer 1