Build a data table in G21:AB42 that shows what 2021 EPS would be based on the revenue growth rate and cost of goods sold
Build a data table in G21:AB42 that shows what 2021 EPS would be based on the revenue growth rate and cost of goods sold / revenue assumptions listed in Table 1. Input the revenue growth rate assumptions horizontally in row 21 and the cost of goods sold / revenue assumptions vertically in column G When you're done, click the done button below. Income Statement for Lemonade Co. Proiections 2016 2017 2018 2019 2020 2021 Revenues 1,500.0 2,500.0 4,500.0 4,950.0 5,445.0 5,989.5 Cost of goods sold 1,200.0 3,400.0 4,000.0 3,960.0 4,356.0 4,791.6 Gross Profit 300.0 (900.0) 500.0 990.0 1,089.0 1,197.9 Operating costs 55.0 65.0 78.0 78.0 78.0 78.0 Non-operating costs 15.0 15.0 15.0 15.0 15.0 15.0 Pretax Profit 230.0 (980.0) 407.0 897.0 996.0 1,104.9 Taxes 100.0 (200.0) 150.0 314.0 348,6 386.7 Net Profit 130.0 (780.0) 257.0 583.1 647.4 718.2 Shares Outstanding 115.0 120.0 125.0 125.0 125.0 125.0 EPS $1.13 ($6.50) $2.06 $4.66 $5.18 $5.75 Revenue growth rate 67% 80% 10.0% 10.0% 10.0% Cost of goods sold / Revenue 80% 136% 89% 80.0% 80.0% 80.0% Tax rate 43% 20% 37% 35.0% 35.0% 35.0% Table 1. 2021 Assumptions Cost of goods Build data table here: Revenue sold growth rate: / Revenue 1.0% 70.0% 2.0% 71.0% 3.0% 72.0% 4.0% 73.0% 5.0% 74.0% 6.0% 75.0% 7.0% 76.0% 8.0% 77.0% 9.0% 78.0% 10.0% 79.0% 11.0% 80.0% 12.0% 81.0% 13.0% 82.0% 14.0% 83.0% 15.0% 84.0% 16.0% 85.0% 17.0% 86.0% 18.0% 87.0% 19.0% 88.0% 20.0% 89.0% 21.0% 90.0%
Step by Step Solution
3.53 Rating (146 Votes )
There are 3 Steps involved in it
Step: 1
The data table is prepared using the Excel spreadsheet as follows Fo...See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started