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? fox SG & A P C D G Pro forma Income Statements Name 1 2 Adapted from Problem 2 Advanced Problems for Spreadsheet Application, Pro forma income statements for Green Planet 4 5 Green Planet Historical Pro Forma Income Statements 6 Percent of 2020 Income Statement 2017 2018 2019 Revenue 7 12,345,000.00 185,175.00 $ 12,159,825.00 S Revenue S Returns Net Revenue 10 Costs 11 COGS 5,555,250.00 12 Fixed Costs SG&A Depreciation 1,975,200.00 13 1,481,400.00 14 988,320.00 15 EBIT 2,159,655.00 802,425.00 16 Interest Expense S 17 Taxable income 1,357,230.00 18 Taxes 19 NET INCOME 20 21 Growth in Net Income 22 23 Grading: Requirement: 24 Points Possible Item Points Earned 25 Percent of Revenue Calculations 3.0 1 3.0 26 1,5 Revenue Calculations 1.5 27 Fixed Cost Calculations 1.5 1.5 28 Variable Cost Calculations EBIT and Taxable Income Calculations Tax Calculations Net Income Calculation Growth in Net Income Calculation 2.0 2.0 29 2,0 2,0 30 2,0 2.0 31 1,0 1.0 32 2 3.0 3.0 33 Analysis Analysis 3 2.0 2.0 34 4 2.0 2,0 35 Total 20.0 20.0 36 27