Competency
Employ accounting data for business analysis and prediction.
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
s specific to Company
Assumption
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)
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.