Week 4 Analyzing Pro Forma Statements
Tips and Suggestions
Comments on Part 1a and 1b
In Part 1a you will forecast the income statement and balance sheet. This will create a balance sheet that is not in balance (total assets will not equal total liabilities and equity)
Set up your income statement and balance sheet using the format shown in the Excel file example I provide on how to do this assignment.
Set up your spreadsheet with the following headings:
Current Year Next Year Forecast
Income Statement
Balance Sheet
Location of Data for Current Year Income Statement and Balance Sheet
The current year numbers for the income statement and balance sheet should be taken from the two files titled Sample Financial Statement: Balance Sheet and Sample Financial Statement: P&L. These are located in the student materials part of the original week four assignment Analyzing Pro Forma Statements.
Key Assumption: Sales increase 5 percent next year
Forecast next year’s sales to rise by five percent above the current year’s sales.
How to do the forecasted income statement
Forecast next year’s sales assuming that sales increase five percent above the current year.
Identify which items are fixed cost or which are variable cost. Variable costs are those that change because sales change. If you sell ice cream one variable cost is sugar. The more ice cream you sell the more sugar you need to buy.
Fixed costs are costs that do not change because sales change.
Forecast variable cost items by calculating them as a percentage of sales. For example, commissions are variable so you would use the percent of sales approach as shown below:
Commissions 10,000 dollars in current year
Sales are 100,000 dollars in the current year.
Commissions are 10 percent of sales in the current year
Assume sales are forecasted to increase to 200,000 dollars next year.
Assuming commissions are 10 percent of sales next year the forecasted commissions will be 20,000 dollars (10% x 200,000 dollars)
Forecast fixed costs by keeping them the same as in the current year.
In the practice example there are notes next to the line items indicating if an item is fixed or variable.
How to do the balance sheet forecast
Identify which items are variable or should be assumed to change as sales change. In the example these items are marked as Percentage of Sales items.
An example is cash. As sales increase cash is assumed to increase. So you can use the percentage of sales approach (same approach as for variable costs in the income statement) to do the forecast of cash for next year.
Example using cash:
Cash on the balance sheet 20,000 dollars in current year
Sales are 100,000 dollars in the current year.
Cash is 20 percent of sales in the current year
Assume sales are forecasted to increase to 200,000 dollars next year.
Assuming cash is 20 percent of sales next year the forecasted cash will be 40,000 dollars (20% x 200,000 dollars)
Balance sheet items which do not change as sales change are simply assumed to remain the same in the forecasted year. They are treated in the same way as fixed costs are forecasted in the income statement.
The practice example balance sheet contains comments which tell if an item is to be forecasted using percentage of sales or remains unchanged.
Income Tax Calculation
Income tax is calculated as 45 percent of Pretax Profits for the current and the forecasted years.
Long Term Debt Calculation
The long term debt for the forecasted year should be reduced by the amount of the current portion of long term debt in the current year. You are paying off part of the outstanding balance in the current year so the forecasted long term debt needs to be reduced by this amount.
Retained Earnings Calculation
In the forecasted year, the retained earnings amount from prior years is increased by the retained earnings from the current year. For example, if the current year’s retained earnings were 10,000 (which is the net income for the current year) and the current year’s amount of retained earnings for prior years was 100,000, then the forecast for next year’s retained earnings from prior years would be 110,000.
Part 1b: Your Forecasted Balance Sheet will be “Out of Balance”
A balance sheet which is in balance will have total assets equal total liabilities and equity. When you complete Part 1 next year’s forecasted balance sheet will be out of balance. Total assets will not equal total liabilities and equity.
For part 1b you need to calculate the amount the balance sheet is out of balance. Subtract Total Liabilities and Equity from Total Assets in the next year forecast column. This number needs to be shown in next to a line item called Amount Forecasted Balance Sheet is Out of Balance
Comments on Part 2
In part 2 you will be doing some financial planning based upon the information provided from the out of balance balance sheet in part 1. The out of balance amount is a positive number which means that the company will generate more cash during the year than the forecast assumes. You can do some good things with the extra cash (assuming that your forecasted income statement is correct.).
Because you are projecting significant excess cash to be generated, you set up a financial plan next year to pay off the entire long term debt balance that is in the next year’s forecasted balance sheet. This is the amount that will be found in the long term debt line and the current portion of long term debt in the current liabilities section.
The remainder of the out of balance money shall be added to the forecasted cash balance.
If you set up the financial plan correctly, next year’s forecasted total assets will equal the total liabilities and equity after the reduction of long term debt and increase in cash are applied.
Follow the Practice Example Format Closely
If you follow the practice example format for parts 1a, 1b, and 2 closely then the assignment will be much easier.
A couple of “check figure” comments. When you complete part 1a the forecasted balance sheet should be out of balance. In other words, Total Assets will be significantly less than Total Liabilities and Equity.
When you complete Part 2 the “In Balance Forecasted Balance Sheet” should have total assets equaling total liabilities and equity.
Please set up your spread sheet with three distinctly separate sections: part 1a, part 1b and part 2. (just as is done in the practice example)
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.
Resources:
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.
Please post your work as one spread sheet with Parts 1a, 1b and 2 clearly shown as separate sections. The format shown in the practice example should be followed in your posted work.
