Needing assistance with an assignment for a high level, income statement in excel. I’m attaching the assignment instructions, grading rubric and an excel spreadsheet for this assignment.
Any concerns or pricing concerns please contact me prior to completing the assignment.
Don't use plagiarized sources. Get Your Custom Essay on
Need Assistance with Assignment 5
Just from $13/Page
Thanks
Deliverable 5 – Forecasting Methods and Applications
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:
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.
Financial Statements spreadsheet attached separately.
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
