Build a data table in G21:AB42 that shows what 2021 EPS would be based on the...

  

Transcribed Image Text:

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% 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%

Answer rating: 100% (QA)

The data table is prepared using the Excel spreadsheet as follows Fo... View the full answer