Goal of the Assignment:
Chapter 19 discusses how to do pro forma or forecasting of income statements and balance sheets. However, the discussion can be confusing. The goal of this assignment is to have students actually do a pro forma forecast and then apply a financial plan that the company has determined to the forecast.
1. Chapter 19 in the text
2. My guidelines and tips for completing the week four Pro Forma assignment. (a word.doc file which is week four required material.
3. An excel spreadsheet showing an example of the assignment. This is a practice example. You can see how the calculations are done by referencing the cells in the spreadsheet. It is an excel file which is located in the week four required material.
4. The income statement and balance sheet data is found in the student materials part of the week four Analyzing Pro Forma Statements assignment. There are two files, one for the balance sheet and one for the income statement. These statements are to be considered the Current Year part of the analysis. You will be doing a forecast of the income statement and balance sheet that shall be titled Next Year Forecast.
Part 1a. (35 points) Complete a Pro Forma forecast of the income statement and balance sheet. Assume a five percent increase in sales from the current period to next year.
Set up your spreadsheet using the same format as is shown in the practice example in the excel file.
Part 1b. (5 points) Calculate the “Amount the Balance Sheet is Out of Balance”.
Set up your calculation using the same format as is shown in part 1b of the practice example.
Part 2 (10 points) Financial Plan for Forecasted Year
The amount the balance sheet is out of balance represents forecasted cash that can be used by the company. The firm has decided to do two things with this forecasted money. First they want to pay off all long-term debt (both the current portion of the long-term debt and the long-term debt that is due beyond the next 12 months. These amounts are found in the forecasted balance sheet under the liabilities section.
Any remaining cash shall be used to increase the cash balance of the company.
Please prepare a spreadsheet layout just like the Part 2 layout in the excel practice example. As is shown in the practice example, show the breakdown of cash that will go to pay down the long term debt items (current portion and long term debt) and the amount of cash that will be added to the cash balance.
Next prepare a forecasted balance sheet with three columns as shown below:
Forecasted Balance Sheet Adjustments Forecasted Balance Sheet
Out of balance In Balance
You will know that part 2 is done correctly when you see the In Balance “Total Assets” equal the “Total Liabilities and Equity” amounts.
Please note that No Paper is required for this assignment. It should be done on an excel spreadsheet.
Ask Your Question
We have verified professionals who are ready to answer your question.
Save Time and Money
We choose experts who can quickly answer your question and that suit your budget.
Get Your Answer
Your satisfaction is 100% guaranteed. You can keep on asking questions until you get the answer you need.