Retrieve Exercise 1. Be sure that the parameters are the original figures, then perform the following steps:

1. Click Data -> What-IF Analysis. Set up a Data Table with one variable to determine how varying the gross sales growth rate from 1 percent to 7 percent affects the after-tax profit in each of the years 2019 through 2021. Show the growth rate variation in increments of 1 percent. Place the northwest corner of the data table in cell B36. After generating the table, copy it to another area of the worksheet, then add suitable title and labels and format the output area properly. Print the resulting data table into one page without frames.

2. Continue your data table exercise. Set up a Data Table with two variables to determine the effect that varying the labor growth rate from 2 percent to 8 percent in steps of 1 percent and the material growth rate from 5 percent to 9 percent in steps of 1 percent has on the after-tax profit for 2019. Place the northwest corner of the data table in cell B52. Like what we did earlier, after generating the table, copy it to another area of the worksheet and style it for a professional presentation and printing. Print the resulting data table into one page with frames.

3. Based on the output from Step 1 above, prepare a column chart to show the after-tax profit for sales growth rate of 2, 4, and 6 percent only, including suitable titles and legends. Print the resulting chart into one page.

4. Based on the output from Step 2 above, prepare a line chart with labor growth rate on the x-axis and with suitable titles and legends. Print the resulting chart into one page.

5. Based on information in Column F (total figures), create a pie chart to provide analytical data such as breakdown of costs and expenses that will be useful for management decision making. Again, provide suitable titles and legends at the professional level. Make sure that the pie chart you create will make sense to corporate management. (You will need to design your own pie chart, no further information will be provided as for how this pie chart is going to look like.)

Exercise 1:

The Lemmon Road Manufacturing, Co.
Assumptions
Gross Sales Growth	4.0%
Direct Materials Growth	3.0%
Direct Labor Growth	5.0%
Factory Overhead Growth	6.0%
General & Admin Exp. Rate	18.0%
Tax Rate	21.0%
Ret & All Rate	2.00%
The Lemmon Road Manufacturing, Co.
Pro Forma Income Statement
	2018	2019	2020	2021	Total
Gross Sales	3985.00	4144.40	4310.18	4482.58	16922.16
Less: Returns & Allowances	79.70	82.89	86.20	89.65	338.44
Net Sales	3905.30	4061.51	4223.97	4392.93	16583.72
Direct Materials	811.30	835.64	860.71	886.53	3394.18
Direct Labor	749.50	786.98	826.32	867.64	3230.44
Factory Overhead	1371.02	1453.28	1540.48	1632.91	5997.69
Cost of Goods Sold	2931.82	3075.90	3227.51	3387.08	12622.30
Gross Profit	973.48	985.62	996.46	1005.86	3961.41
General & Admin Exp.	702.95	731.07	760.32	790.73	2985.07
Profit before Tax	270.53	254.54	236.15	215.13	976.35
Taxes	56.81	53.45	49.59	45.18	205.03
Profit after Tax	213.72	201.09	186.56	169.95	771.31