Question

Creating a Pro forma income statement Adapted from Advanced Problems for Spreadsheet Application, Financial Management Core Concepts, Brooks 3rd Edition, Problem 2 Green Planet, a restaurant supply company has given you the following financial data for 2017: Green Planet Historical Income Statement 2017 $12,345,000.00 Revenue 185,175.00 $12,159,825.00 Returns Net Revenue Costs $5,555,250.00 COGS $ 1,975,200.00 $ 1,481,400.00 Fixed Costs S G & A $ Depreciation 988,320.00 $ 2,159,655.00 802,425.00 EBIT Interest Expense $ Taxable income 1,357,230.00 The tax rate in 2017 was 38%. Due to changes in the tax laws, the future tax rate is expected to be 21%. Required: 1. Construct Green Planet's pro forma income statements for 2018, 2019 and 2020 given the following assumptions: Projected sales will rise annually at 4.5% Fixed costs will remain the same in 2018, then rise annually by 1% in 2019 and 2020. All other accounts listed (except tax) will be at the same percentage of sales for the next three years as they were in 2017 Use formulas where appropriate (not using formulas will result in a loss of credit) 2. Calculate the percentage growth in net income for each year. 3. Discuss the percentage growth in net income you calculated from 2017 to 2018. What factors influenced the growth in net income? 4. Discuss the change in the percentage growth in net income from 2018 to 2019. What has changed?

Answer 1

Here is the answer

Income Statement	2017	Percent of Revenue	2018	2019	2020
Revenue	$               12,345,000	100%	$          12,900,525	$          13,481,049	$          14,087,696
Returns	$                     185,175	2%	$          193,507.88	$          202,215.73	$          211,315.44
Net Revenue	$               12,159,825	99%	$          12,707,017	$          13,278,833	$          13,876,380
Costs
COGS	$                  5,555,250	45%	$       5,805,236.25	$       6,066,471.88	$       6,339,463.12
Fixed Costs	$                  1,975,200	16%	$       1,975,200.00	$       1,994,952.00	$       2,014,901.52
SG & A	$                  1,481,400	12%	$       1,548,063.00	$       1,617,725.84	$       1,690,523.50
Depreciation	$                     988,320	8%	$       1,032,794.40	$       1,079,270.15	$       1,127,837.30
EBIT	$                  2,159,655	17%	$             2,345,723	$             2,520,413	$             2,703,655
Interest Expense	$                     802,425	7%	$          838,534.13	$          876,268.16	$          915,700.23
Taxable income	$                  1,357,230	11%	$             1,507,189	$             1,644,145	$             1,787,955
Taxes	$                     515,747	4%	$                316,510	$                345,270	$                375,470
NET INCOME	$                     841,483	7%	$             1,190,680	$             1,298,874	$             1,412,484
	Growth in Net Income		41.50%	9.09%	8.75%
	Growth in Net Income (2017 base)		41.50%	54.36%	67.86%

Here is the formula version to get you full credit

A D P G H 6 7 Percent of Revenue 2018 Income Statement 2017 2019 2020 -F8* 1.045 - SD$ 9 * G8 8 C8/$C$8 C8*1.045 E8*1.045 Revenue 12345000 - SD$ 9 * F8 - 9 185175 C9/$C$8 SD$ 9 . E8 Returns C10/SC$8 = F8 - F9 10 - G8- G9 Net Revenue C8-C9 -E8- E9 11 Costs - SD$12 * G8 =F13 1,01 12 COGS C12/$C$8 -SD$12 *E8 -SD$ 12 * F8 -E13 1,01 5555250 13 Fixed Costs C13/ SC$8 1975200 -C13 - SD$ 14 *E8 - SD$ 14 * G8 - SD$ 15 * G8 -G10 - SUM (G12:G15) 14 SG & A 1481400 C14/$C$8 -SD$14 F8 15 C15/$C$8 SD$15*E8 SD$15 * F8 Depreciation 988320 EC16/SC$8 - C10- SUM (C12:C15) =F10- SUM (F12:F15) 16 -E10-SUM(E12:E15) EBIT -SD$17 E8 -SD$17 G8 G16- G17 17 Interest Expense C17/SC$8 C18/$C$8 C19/SC$8 C20/SC$8 802425 -SD$17 F8 18 Taxable income C16-C17 -E16- E17 F16-F17 EE18 21 % EC18*38% EF18*21 % EG18 21% 19 Taxes -G18- G19 20 NET INCOME C18-C19 -E18 - E19 F18-F19 21 (E20-$C$20)/$c$20 F20-SE$20)/SE$20 (G20-SF$20)/$ F$ 20 22 Growth in Net Income 23 Growth in Net Income (2017 base) (E20-$C$20)/$c$20 F20-SC$20)/SC$20 (G20-$c$20)/$C$20 24 S ... Sheet451 Sheet449 Sheet450 Sheet448 Sheet447 Sheet446 Sheet445 Sheet444 +

3 and 4

The Net Income has risen from $ 0.8 million in 2017 to $ 1.2 million in 2018. There is massive increase of 41.5%. This is due to the fact that the fixed costs have not increased and the tax rates are substantially down from 38% to 21%. If you compare increase in 2019 from 2018 the increase is 9% due to increase in sales. But from 2017 the same increase is 54% because of increase in sales and reduction in taxes. Same is the case for 2020.