between 20,000 and 60,000 cans, in increments of 5,000 in the column. Complete the table with appropriate input cells, and format so that it is easy to read. 5. In the box below the data table (E16:K25), create a two-variable data table that displays Net Income for monthly Coke sales between 20,000 and 60,000 cans, in increments of 5,000 units in the column, and for Price Charged per Coke from $1.00 to $2.00 in increments of $0.20 in the top row. Complete the table with appropriate input cells, and format so that it is easy to read. Using the custom number format, rename the cell at the top left of the table "Units". 7 Assign names to the following cell ranges: B4:B5, B13:B16, B20:B22, using the text contained in column A to the left. 8. Set up four scenarios with the following data: Status Quo Raised Price Increase Sales Increase Sales & Raised Price Monthly units sold 24,000 19,000 28,500 31,000 Price per unit 1.50 2.00 1.00 2.00 Insurance 1000 400 600 600 Salary 2000 2000 2500 2500 Taxes 1800 1800 2100 2100 9. In the "Comment" box of Scenario Manager, enter your name and today's date. 10. Create a Scenario Summary Report, for the four scenarios above that shows total revenue, total expenses and net income. 11. Create a Scenario PivotTable that shows Total Revenue and Net Income. Format the values in the table with commas, no decimals. 12. In A31 answer the question: Which scenario do you recommend and why? 13. Save your workbook. Case 3 - Local Vending - Part II (25 marks) Your boss was happy with the work that you did in case two. 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 Vending 2. 2. In the Forecast column C, enter the appropriate formulas 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 Coke 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