5. What-if analysis (25 points)

a. On the Q5 sheet, please format the Title row at the top of the worksheet stating Detailed Sales What-if Analysis. Make sure it is centered across columns A G and has a heading format.

b. Enter formulas in cells B6, B7, and B8 to calculate the total sales, total cost, and net income for a new product line, based on the projected unit sales, unit price and unit cost provided.

c. Adjust column widths as needed and format all values except unit sales as currency with no decimal places.

d. Complete the one-variable data table under the heading Effect of Varying Unit Sales on Total Sales, Total Cost, and Net Income to calculate the total sales, total cost and net income for projected sales of 80, 90, 100, 110, and 120 units.

e. Complete the two-variable data table under the heading Effect on Net Income of Varying Unit Sales and Unit Price to calculate the net income for projected sales of 80, 90, 100, 110, and 120 units at unit prices of $4, $5, $6, $7, and $8.

f. Format cells A3 B8 with a thick box border.

g. Format the one-variable data table with themed cell style. Make sure the Initial values, Total Sales, Total Cost and Net Income have an accent color.

h. Format the two-variable data table with a themed cell style. Make sure the initial values for the horizontal and vertical have an accent color.

i. Add conditional formatting to the one-variable data table. Format the top 10% with green fill. Format the bottom 10% with red fill. j. Save your work (CTRL+s) and close.

Detailed Sales What-if Analysis 100 3 Unit sales 4 Unit price 5 Unit cost 6 Total sales 7 Total cost 8 Net income 10 Effect of Varying Unit Sales on Total Sales, Total Cost, and Net Income Initial Values 80 90 100 110 120 12 Total Sales 13 Total Cost 14 Net Income 15 16 Effect on Net Income of Varying Unit Sales and Unit Price 17 18 19 20 80 90 100 110 120 4 6 21 23 24 25 26