Case 4 - National Distributing - Part II (25 marks) Your boss was happy with the work that you did in case one. He's now interested in determining whether a different price per can would lead to higher sales and profits. WARNING: Do not change the formula in cell C4 - your spreadsheet won't work if you do. 1. Open the worksheet "National 2" 2. In the Forecast column C, enter the appropriate formula in the Total cells highlighted in yellow. Do not enter anything in cell C5 at this time. 3. The company's "Other Expenses" will vary with the number of cans of Pepsi sold. Higher volumes of sales would lead to higher expenses. The lookup table in F2:G6 contains the information for Maintenance expenses; F8:G12 contains Insurance expenses; and F15:G19 contains Salary expenses by volume sold. 4. Complete each of cells C13, C14, and C15 to determine the Maintenance, Insurance, and Salary expenses respectively. 5. Your boss would like to know the price per unit that will lead to the highest Net Income. The minimum price you can charge is $0.50 and the maximum is $1.25. In addition, you are contractually bound to purchase a minimum of 15,000 cans per month, while the maximum that Pepsi is able to supply is 40,000 cans. Use solver to determine the optimal price per can. 6. Save the Answer Report on a separate worksheet. 7. Answer the question in cell A26. 8. Save your workbook. Check your work and submit the excel file prior to the deadline.