excel assignment

Step

Instructions

Points Possible

1

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

0

2

Perform your calculations for the customers who use the medication to stay awake under plan a. Prepare cells E11-E14 to use the Solver Add-in. In cell E11, by using cell references, calculate the quantity demanded for the given price. Use cells F5, H5, and E10. In cell E12, by using cell references, calculate the total revenue for the given price and the quantity demanded. Use cells E10 and E11. In cell E13, by using cell references, calculate the total cost for the quantity demanded. Use cells F7 and E11. In cell E14, by using cell references, calculate the profit generated by the customers who use the medication to stay awake. Use cells E12 and E13.

4

3

Use the Solver Add-in to find the price that maximizes the profit generated by the customers who use the medication to stay awake. 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 E14 in the Set Objective field. Select the Max option below. Choose cell E10 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 E10, that gives the maximum profit generated by the customers who use the medication to stay awake. The values for the quantity demanded, total revenue, total cost, and profit will update automatically.

1

4

In cells E16-E20, perform your calculations for the customers who use the medication as a diuretic under plan a. In cell E16, by a using cell reference, enter the price set for the customers who use the medication as a diuretic. Refer to an appropriate cell among E10-E14. In cell E17, by using cell references, calculate the quantity demanded. Use cells F6, H6, and E16. In cell E18, by using cell references, calculate the total revenue for the given price and the quantity demanded. Use cells E16 and E17. In cell E19, by using cell references, calculate the total cost for the quantity demanded. Use cells F7 and E17. In cell E20, by using cell references, calculate the profit generated by the customers who use the medication as a diuretic. Use cells E18 and E19.

5

5

In cell F21, calculate the combined profit for plan a. Use cells E14 and E20.

1

6

Perform your calculations for the customers who use the medication as a diuretic under plan b. Prepare cells E25-E28 to use the Solver Add-in. In cell E25, by using cell references, calculate the quantity demanded for the given price. Use cells F6, H6, and E24. In cell E26, by using cell references, calculate the total revenue for the given price and the quantity demanded. Use cells E24 and E25. In cell E27, by using cell references, calculate the total cost for the quantity demanded. Use cells F7 and E25. In cell E28, by using cell references, calculate the profit generated by the customers who use the medication as a diuretic. Use cells E26 and E27.

4

7

Use the Solver Add-in to find the price that maximizes the profit generated by the customers who use the medication as a diuretic. Using Solver Add-in Select the Solver in the Analyze group of the Data tab of the Ribbon. Choose cell E28 in the Set Objective field. Select the Max option below. Choose cell E24 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 E24, that gives the maximum profit generated by the customers who use the medication as a diuretic. The values for the quantity demanded, total revenue, total cost, and profit will update automatically.

1

8

In cells E30-E34, perform your calculations for the customers who use the medication to stay awake under plan b. In cell E30, by using a cell reference, enter the price set for the customers who use the medication to stay awake. Refer to an appropriate cell among E24-E28. In cell E31, by using cell references, calculate the quantity demanded. Use cells F5, H5, and E30. In cell E32, by using cell references, calculate the total revenue for the given price and the quantity demanded. Use cells E30 and E31. In cell E33, by using cell references, calculate the total cost for the quantity demanded. Use cells F7 and E31. In cell E34, by using cell references, calculate the profit generated by the customers who use the medication to stay awake. Use cells E32 and E33.

5

9

In cell F35, calculate the combined profit for plan b. Use cells E28 and E34.

1

10

In cells E38-E42, perform your calculations for the customers who use the medication to stay awake under plan c. In cell E38, by using a cell reference, enter the price set for the customers who use the medication to stay awake. Refer to an appropriate cell among E10-E14 and E30-E34. In cell E39, by using cell references, calculate the quantity demanded. Use cells F5, H5, and E38. In cell E40, by using cell references, calculate the total revenue for the given price and the quantity demanded. Use cells E38 and E39. In cell E41, by using cell references, calculate the total cost for the quantity demanded. Use cells F7 and E39. In cell E42, by using cell references, calculate the profit generated by the customers who use the medication to stay awake. Use cells E40 and E41.

5

11

In cells E44-E48, perform your calculations for the customers who use the medication as a diuretic under plan c. In cell E44, by using a cell reference, enter the price set for the customers who use the medication as a diuretic. Refer to an appropriate cell among E16-E20 and E24-E28. In cell E45, by using cell references, calculate the quantity demanded. Use cells F6, H6, and E44. In cell E46, by using cell references, calculate the total revenue for the given price and the quantity demanded. Use cells E44 and E45. In cell E47, by using cell references, calculate the total cost for the quantity demanded. Use cells F7 and E45. In cell E48, by using cell references, calculate the profit generated by the customers who use the medication as a diuretic. Use cells E46 and E47.

5

12

In cell F49, calculate the combined profit for plan b. Use cells E42 and E48.

1

13

In cell E51, determine which plan should be pursued.

1

14

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

0

Total Points

34