excel assignment

Step

Instructions

Points Possible

1

Start Excel. Download and open the workbook named: Chapter_10_2nd_Degree_PD_Problem_Start.

0

2

Prepare cells E14-E17 to use the Solver Add-in. In cell E14, by using cell references, calculate the quantity demanded. Use cells F9, H9, and E13. In cell E15, by using cell references, calculate the total revenue for the given price and the quantity demanded. Use cells E13 and E14. In cell E16, by using cell references, calculate the total cost for the quantity demanded. Use cells F10 and E14. In cell E17, by using cell references, calculate the profit. Use cells E15 and E16. Note: The total cost should be a positive value.

4

3

Use the Solver Add-in to find the price for the size S that maximizes the profit. Using Solver Add-in Select the Solver in the Analyze group of the Data tab of the Ribbon (note you should add this Add-in in case you do not have it in the Data tab already). Choose cell E17 in the Set Objective field. Select the Max option below. Choose cell E13 in the By Changing Variable Cells field. There are no additional constraints. Make sure that the “Make Unconstrained Variables Non-Negative” box is checked. Leave the GRG Nonlinear solving method. Click Solve. In the popup window select the Keep Solver Solution option. Do not check any additional boxes and click OK. As the result, you will see the price in cell E13 that gives the maximum profit. The values for the quantity demanded, total revenue, total cost, and profit will update automatically.

1

4

In cells E18 and E19, by using cell references, calculate the price values for sizes M and L. Use cells F7, F8, and E13.

2

5

In cells E22-E25, perform your calculations for the size S if the price is set at the level specified in cell E21. In cell E22, by using cell references, calculate the quantity demanded. Use cells F9, H9, and E21. In cell E23, by using cell references, calculate the total revenue. Use cells E21 and E22. In cell E24, by using cell references, calculate the total cost. Use cells F10 and E22. In cell E25, by using cell references, calculate the profit. Use cells E23 and E24. Note: The total cost should be a positive value.

4

6

In cells E28-E33, perform your calculations for the situation when the size can be increased from S to M for the extra charge specified in cell E27. In cell E28, by using cell references, calculate the price for size M. Use cells E21 and E27. In cell E29, by using cell references, calculate the new amount of quantity demanded. Use cells F9, H9, and E27. In cell E30, by using cell references, calculate the additional quantity demanded. Use cells E22 and E29. In cell E31, by using cell references, calculate the additional total revenue. Use cells E27 and E30. In cell E32, by using cell references, calculate the additional total cost. Use cells F10 and E30. In cell E33, by using cell references, calculate the additional profit. Use cells E31 and E32. Note: The total cost should be a positive value.

6

7

In cells E36-E41, perform your calculations for the situation when the size can be increased from M to L for the extra charge specified in cell E35. In cell E36, by using cell references, calculate the price for size L. Use cells E28 and E35. In cell E37, by using cell references, calculate the new amount of the quantity demanded. Use cells F9, H9, and E35. In cell E38, by using cell references, calculate the additional quantity demanded. Use cells E29 and E37. In cell E39, by using cell references, calculate the additional total revenue. Use cells E35 and E38. In cell E40, by using cell references, calculate the additional total cost. Use cells F10 and E38. In cell E41, by using cell references, calculate the additional profit. Use cells E39 and E40. Note: The total cost should be a positive value.

6

8

In cell E43, by using cell references, calculate the total profit using the second degree price discrimination. Use cells E25, E33, and E41. Note: Do not use the Excel SUM function.

1

9

In cells D45 and J45, determine whether you will decide to price discriminate.

2

10

Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

0

Total Points

26