CompetencyEmploy accounting data for business analysis and prediction.ScenarioThe Corporate Controller has called you into a planning session and asked you to help with the preparation of an income statement forecast for the coming three years. You both discuss the project and decide that the data you will need it is:The last 5 years of income statementsA forecast of economic data for key economic indicatorsThe basic assumptions the company is making in their business planInstructionsThe Corporate Controller would like you to use Excel to prepare a high-level, 3-year forecast (income statement format) incorporating the historical, economic forecast, and assumptions provided by the Corporate Controller. The Corporate Controller also states that the Board of Directors loves to see “pictures” of summary data rather than a “bunch of numbers.” The Corporate Controller suggests that on a separate tab in Excel to use the graphing function to display total revenues and expenses for the periods covered.Create and Excel spreadsheet with two tabs labeled:Tab 1: 3-Year Income Statement ForecastTab 2: GraphsOn Tab 1: 3-Year Income Statement Forecast, use the same income statement format and titles as the historical data provided.
Assessment #5
Financial Data
Income Statement
All numbers in thousands
Revenue
2018
2017
Total Revenue
Cost of Revenue
Gross Profit
806,338
206,052
766,083
187,289
600,286
578,794
116,445
262,699
99,205
220,728
379,144
221,142
319,933
258,861
Total Other Income/Expenses Net
13,432
7,603
Net Income
207,710
251,258
Operating Expenses
Research Development
Selling General and Administrative
Total Operating Expenses
Operating Income or Loss
Income from Continuing Operations
Summary Table 1.
CBO’s Projections of Key Economic Indicators for Calendar Years 2018 to 2028
Annual Average
Actual,
2017
2018
2019
2020
2021–
2022
2023–
2028
Percentage Change From Fourth Quarter to Fourth Quarter
Gross Domestic Product
Reala
Nominal
Inflation
PCE price index
Core PCE price indexb
Unemployment Rate (Percent)
Payroll Employment (Monthly change, in thousands)c
Interest Rates (Percent)
Three-month Treasury bills
Ten-year Treasury notes
Source: Congressional Budget Office
2.6
3.3
2.4
1.8
1.5
1.7
4.5
5.2
4.5
3.9
3.7
3.9
1.7
1.8
2.0
2.1
2.1
2.0
1.5
1.9
2.1
2.2
2.1
2.0
4.4
181
3.8
211
Annual Average
3.3
3.6
182
62
4.4
25
4.8
57
0.9
1.9
2.9
3.6
3.7
2.8
2.3
3.0
3.7
4.1
4.1
3.7
Assumptions specific to Company
Category
Total Revenue
Cost of Revenue
Research Development
Selling General and Administrative
Assumption
Management projects revenue to continue same trend as
Management is implementing cost savings programs that
Management believes that new products are the life blood
Management projects that all other expense increases wil
2016
2015
2014
529,515
131,070
450,557
102,571
486,280
121,020
398,445
347,986
365,260
78,269
166,110
69,791
151,651
59,880
244,379
154,066
221,442
126,544
8,011
418
146,055
126,126
018 to 2028
Annual Average
2019
2020
2021–
2022
2023–
2028
e From Fourth Quarter to Fourth Quarter
2.4
1.8
1.5
1.7
4.5
3.9
3.7
3.9
2.0
2.1
2.1
2.0
2.1
2.2
2.1
2.0
4.4
25
4.8
57
Annual Average
3.3
3.6
182
62
2.9
3.6
3.7
2.8
3.7
4.1
4.1
3.7
383,240
443,120
(77,860)
504
(78,364)
ementing cost savings programs that should reduce the cost of revenue to approximately 2016 levels as a percentage
s that new products are the life blood of the company and will continue with it current trend of R& D expenses
a percentage

Purchase answer to see full
attachment