Business Finance
A T Still University Health Module 5 Forecasting Methods Income Statement Project

Question Description

I’m studying and need help with a Accounting question to help me learn.

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:

  1. The last 5 years of income statements
  2. A forecast of economic data for key economic indicators
  3. 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.


RUBRIC:

F

F

C

B

A

0

1

2

3

4

Did not Submit

No Pass

Competence

Proficiency

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 sufficient 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.

Not Submitted

Did not provide a separate Excel tab that includes a graph to display total revenues and expenses for the time periods covered in the forecast.

Excel tab includes a basic graph to display total revenues and expenses for the time periods covered in the forecast.

Excel tab includes a well-developed graph to display total revenues and expenses for the time periods covered in the forecast.

Excel tab includes a detailed graph to display total revenues and expenses for the time periods covered in the forecast.


Template with 3 tabs is attached

Unformatted Attachment Preview

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 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 Source: Congressional Budget Office Assumptions specific to Company Category Total Revenue Assumption Management projects revenue to continue same trend as Cost of Revenue Research Development Selling General and Administrative 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 383,240 443,120 (77,860) 504 (78,364) 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 s revenue to continue same trend as previous 5 years 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 Income Statement All numbers in thousands 2021 FORECASTED DATA 2020 2023-2028 1.7 3.9 2 2021-2022 1.5 3.7 2.1 2 4.8 57 2.8 3.7 2.1 4.4 25 3.7 4.1 Revenue Total Revenue Cost of Revenue Gross Profit Operating Expenses Research Development Selling General and Administrative Total Operating Expenses Operating Income or Loss Income from Continuing Operations Total Other Income/Expenses Net Net Income CBO's Projections of Key Economic Indicators Annual Average GDP Percentage Nominal Inflation PCE Price Index Core PCE Price Index Unemployment Rate (Percent) Payroll Employment (Monthly change, in thousands) Interest Rates (Percent) Ten - year Treasury notes ASTED DATA 2019 2020 2018 HISTORIC DATA 2017 2016 806,338 206,052 766,083 187,289 600,286 2015 2014 529,515 131,070 450,557 102,571 486,280 121,020 578,794 398,445 347,986 365,260 116,445 262,699 99,205 220,728 78,269 166,110 69,791 151,651 59,880 379,144 221,142 319,933 258,861 244,379 154,066 221,442 126,544 13,432 7,603 8,011 418 207,710 251,258 146,055 126,126 2019 2018 2017 1.8 3.9 2.1 2.4 4.5 2 3.3 5.2 1.8 2.6 4.5 1.7 2.2 3.6 62 3.6 4.1 2.1 3.3 182 2.9 3.7 1.9 3.8 211 1.9 3 1.5 4.4 181 0.9 2.3 383,240 443,120 (77,860) 504 (78,364) Category Total Revenue Cost of Revenue Research Development Selling General and Administrative Assumption Same trend as previous 5 years Reduce the cost of revenue to approximately 2016 levels as a percentage Current trend of R& D expenses Management projects that all other expense increases will be in line with Inflation ...
Purchase answer to see full attachment
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

Final Answer

Attached.

Income Statement
All numbers in thousands
Revenue
Total Revenue
Cost of Revenue
Gross Profit
Operating Expenses
Research Development
Selling General and Administrative
Total Operating Expenses
Operating Income or Loss
Income from Continuing Operations
Total Other Income/Expenses Net
Net Income

CBO's Projections of Key Economic Indicators
Annual Average GDP Percentage
Nominal Inflation
PCE Price Index
Core PCE Price Index
Unemployment Rate (Percen...

Purdue University

Anonymous
Solid work, thanks.

Anonymous
The tutor was great. I’m satisfied with the service.

Anonymous
Goes above and beyond expectations !

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4
Similar Questions
Related Tags