Competency
Employ accounting data for business analysis and prediction.
Don't use plagiarized sources. Get Your Custom Essay on
Forecasting Methods and Applications
Just from $13/Page
Scenario
The 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 statements
- A forecast of economic data for key economic indicators
- The basic assumptions the company is making in their business plan
Instructions
The 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 Forecast
- Tab 2: Graphs
On Tab 1: 3-Year Income Statement Forecast, use the same income statement format and titles as the historical data provided.
Sheet1
Assessment #5 |
| Financial Data |
| Income Statement |
| All numbers in thousands |
Revenue |
2018 |
2017 |
2016 |
2015 |
2014 |
|
| Total Revenue |
806,338 |
766,083 |
529,515 |
450,557 |
486,280 |
|
| Cost of Revenue |
206,052 |
187,289 |
131,070 |
102,571 |
121,020 |
| Gross Profit |
600,286 |
578,794 |
398,445 |
347,986 |
365,260 |
| Operating Expenses |
|
| Research Development |
116,445 |
99,205 |
78,269 |
69,791 |
59,880 |
|
| Selling General and Administrative |
262,699 |
220,728 |
166,110 |
151,651 |
383,240 |
| Total Operating Expenses |
379,144 |
319,933 |
244,379 |
221,442 |
443,120 |
| Operating Income or Loss |
221,142 |
258,861 |
154,066 |
126,544 |
(77,860) |
| Income from Continuing Operations |
| Total Other Income/Expenses Net |
13,432 |
7,603 |
8,011 |
418 |
504 |
| Net Income |
207,710 |
251,258 |
146,055 |
126,126 |
(78,364) |
| Source: Congressional Budget Office |
|
| Assumption |
s specific to Company
| Category |
Assumption
Total Revenue
| Management projects revenue to continue same trend as previous 5 years |
Cost of Revenue
| Management is implementing cost savings programs that should reduce the cost of revenue to approximately 2016 levels as a percentage |
Research Development
| Management believes that new products are the life blood of the company and will continue with it current trend of R& D expenses |
Selling General and Administrative
| Management projects that all other expense increases will be in line with Inflation |
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
2.6
3.3
2.4
1.8
1.5
1.7
Nominal 4.5 5.2 4.5 3.9
3.7
3.9
Inflation
PCE price index
1.7
1.8
2.0
2.1
2.1
2.0
Core PCE price indexb 1.5
1.9
2.1 2.2 2.1 2.0
Annual Average
Unemployment Rate (Percent) 4.4 3.8 3.3
3.6
4.4 4.8
Payroll Employment (Monthly change, in thousands)c 181 211 182 62 25 57
Interest Rates (Percent)
Three-month Treasury bills
0.9
1.9
2.9
3.6
3.7
2.8
Ten-year Treasury notes 2.3 3.0 3.7 4.1 4.1 3.7
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
2.6
3.3
2.4
1.8
1.5
1.7
Nominal 4.5 5.2 4.5 3.9 3.7 3.9
Inflation
PCE price index
1.7
1.8
2.0
2.1
2.1
2.0
Core PCE price indexb 1.5 1.9 2.1 2.2 2.1 2.0
Annual Average
Unemployment Rate (Percent) 4.4 3.8 3.3 3.6 4.4 4.8
Payroll Employment (Monthly change, in thousands)c 181 211 182 62 25 57
Interest Rates (Percent)
Three-month Treasury bills
0.9
1.9
2.9
3.6
3.7
2.8
Ten-year Treasury notes 2.3 3.0 3.7 4.1 4.1 3.7
/
Grading Rubric
F F C B A
0 1 2 3 4
Did not
Submit
No Pass Competence Pro�ciency Mastery
Not
Submitted
Did not provide a
mid or high-level
Excel spreadsheet
that includes a
thoroughly
completed 3-year
forecast; did not
use
income
statement format
for the
forecast.
Provided a
su�cient high-
level 3-year
forecast; used
income
statement
format.
Provided a
well-
developed
high-level 3-
year forecast;
used income
statement
format.
Provided a
detailed high-
level 3-year
forecast; used
income
statement
format.
Not
Submitted
Did not include a
summary of 5
years of
data
and
assumptions from
income
statements.
Included a
basic summary
of 5 years of
data and
assumptions
from income
statements.
Included an
expanded
summary of 5
years of data
and
assumptions
from income
statements.
Included a
detailed
summary of 5
years of data
and
assumptions
from income
statements.
Not
Submitted
Did not provide
economic data for
key
economic
indicators in the
forecast.
Provided basic
economic data
for the key
economic
indicators
included in the
forecast.
Provided
expanded
economic
data
for key
economic
indicators
included in
the forecast.
Provided
comprehensive
economic data
for key
economic
indicators
included in
forecast.
