Income Statement
2016 2017 2018 2019 2020 2021 2022
Revenue
Scenarios Base 150,000 165,000 181,500 199,650 219,615 241,577 265,734
Down 133,500 146,850 161,535 177,689 195,457 215,003 236,503
Up 166,500 183,150 201,465 221,612 243,773 268,150 294,965
Revenue 150,000 165,000 181,500 199,650 219,615 241,577 265,734
COGS 67,500 74,250 81,675 89,843 98,827 108,709 119,580
Gross Profit 82,500 90,750 99,825 109,808 120,788 132,867 146,154
SG&A 16,500 18,150 20,000 20,000 20,000 20,000 20,000
EBITDA 66,000 72,600 79,825 89,808 100,788 112,867 126,154
Depreciation 6,600 7,260 9,075 9,983 10,981 12,079 13,287
Interest 1,000 1,000 1,000 1,000 1,000 1,000 1,000
EBT 58,400 64,340 69,750 78,825 88,808 99,788 111,867
Taxes 17,520 19,302 20,925 23,648 26,642 29,936 33,560
Net Income 40,880 45,038 48,825 55,178 62,165 69,852 78,307
Required:
1 Dynamic dates and time periods with 'IF' statements
2 Scenarios with the VLOOKUP and CHOOSE functions
3 Dynamic outputs with 'IF' functions
4 Summary Data with VLOOKUP, INDEX, and MATCH functions
5 What is the level of sales at which Net Income is 10,000 in 2022?
6 Build a dynamic chart that shows the movement of sales and gross profit margin between 2016 and 2022.
7 Build a dynamic chart that shows the proportions of revenue used to cover COGS, SG&A, Depreciation, interest, tax
8 Create the table of contents (raw data, requirement, your 'Solution Page' are the minimum sheets).
NOTE:
1 Do the assignment by yourself, serious punishment may follow submitting others work.
2 Use fill colors 57,192, 200 for the time periods and dates and 205,210, 20 for the modules (grouping).
3 Make sure your works are properly formated, and are clean
ween 2016 and 2022.
&A, Depreciation, interest, taxes, and remains as net income between 2016 and 2022.
nimum sheets).
dules (grouping).
Scenario
Down Base 1
2 Down 2
Up 3
Year 2016
12/31/2016
Total Average
Row Data
Out put
Revenue 133500
COGS 67500
Gross Profit 66000
SG&A 16500
EBITDA 49500
Depreciation 6600
Interest 1000
EBT 41900
Taxes 17520
Net Income 24380
Summary Table
What If Analysis(Goal Seek)
Income Statement Chart(Revenue & Gross profit)
Year 2016
Revenue 133500
Gross Profit 66000
Start 12/31/2016 Total 4
End 12/31/2022 Average 7
No.Of Period 7
2017 2018 2019 2020 2021 2022
12/31/2017 12/31/2018 12/31/2019 12/31/2020 12/31/2021 12/31/2022
146850 161535 177688.5 195457.35 215003.085 236,503
74250 81675 89842.5 98826.75 108709.425 119,580
72600 79860 87846 96630.6 106293.66 116,923
18150 20000 20000 20000 20000 20,000
54450 59860 67846 76630.6 86293.66 96,923
7260 9075 9982.5 10980.75 12078.825 13,287
1000 1000 1000 1000 1000 1,000
46190 49785 56863.5 64649.85 73214.835 82,636
19302 20925 23647.5 26642.25 29936.475 33,560
26888 28860 33216 38007.6 43278.36 49,076
2017 2018 2019 2020 2021 2022
146850 161535 177688.5 195457.35 215003.085 236503.3935
72600 79860 87846 96630.6 106293.66 116923.026
