EXERCISE 8 – BALANCE SHEETS
In this exercise, we’ll forecast the Balance Sheets of a company. Pull up the spreadsheet for Exercise 8 – Balance Sheets. We are going to continue where we left off working from Exercise 7 in constructing proforma Income Statements
In creating a proforma balance sheet, we first want to look at the assets that are required to support our projected level of sales. We will use Cash as our plug figure. If the projected cash balances are all positive, then we have funds that we can pay out as dividends. If any of the projected cash balances are negative, then we will ultimately have to find funds to finance our shortfall.
First, we want to look at each category of assets and determine how we want to project the future asset levels of each to support our projected sales levels. For Accounts Receivable, a simple percentage of sales seems appropriate. If sales double, then we would expect our receivables to double if we keep the same credit standards and payment terms. For our Inventories, we want to use a regression equation. This will separate out our base level of inventory from the portion that fluctuates with demand. Prepaid Expenses can be handled a couple of different ways: first, we could calculate an average of previous prepaid expenses as a percentage of our previous G&A expenses and apply that percentage to our projections. Alternatively, we could just assume that they stay constant to that of our most recent year. Similarly, Deposits could be calculated as a percentage of COGS or just be kept constant to the most recent year. Let’s just keep them both a constant dollar amount. For Fixed Assets, we could either make projections based on our Gross Fixed Assets and then increase Accumulated Depreciation by our calculation of depreciation each year, or we can just base everything on the Net Fixed Assets. Let’s use Net Fixed Assets (it’s easier) and we’ll use regression analysis to make the projections.
ASSETS
Cash
Since this will be a plug figure, let’s save it for the very last thing that we do in order to make the Balance Sheets balance.
Accounts Receivable
Let’s use a weighted-average percent of sales to project the future Accounts Receivables like we did in Exercise 7. The formula for cell K55 will be
=SUM($C$55:$J$55)/SUM($C$5:$J$5)*K5
This equation calculates the average based on historic A/R relative to Revenues and multiplies it times the projected Revenues for 2010. Again, the figures have been locked with respect to cells with dollar signs ($) for copying purposes. This average is then multiplied by the projected revenues for 2010. Next, we copy this from cell K55 to cells L55 to M55. Note that we cannot use the balance sheet figure for column B, the year 2001, since we don’t have the revenue data for that year.
Inventory
Since inventories include both seasonal build-ups as well as a base level of inventory (such as safety stocks), let’s use regression analysis in projecting our future Inventory requirements. First, we need to copy the actual inventory levels for 2002 – 2009 to the Regressions worksheet and paste it in cell I2 using Paste Special – Values – Transpose like we did for the expense categories. Let’s use column I so we can keep it separated from the expenses. Place the label “Inventory” in cell I1. Now go to Data – Data Analysis – Regression and bring up the regression program. The independent (X) variable will be Cost of Goods Sold (COGS) and the dependent (Y) variable will be the inventories. Set the Output Range for cell A57 and then click OK. Type in “COGS vs. Inventory in cell A57 so we can identify the regression. You can then copy the format that we did for one of the Expense regressions onto the Inventory regression output to format it. Change cell A73 to read “Base Inventory” and cell A74 to “Variable Level”. You might also want to label cell A56 as “Revenues vs. Inventory” so we know what the regression is for. You’ll notice that the equation is not the best in world based upon the Adjusted R2 and the F-Ratio. The P-Value for the variable portion shows that this company basically has a policy of a constant inventory level (as indicated by the Base Level regression statistic). Now go to the Financials spreadsheet and let’s put the following equation into cell K56 to project the 2010 inventory levels that we can anticipate:
=Regressions!$B$73+Regressions!$B$74*Financials!K13
Again, note the “$” so we can copy it to cells L56 – M56.
Prepaid Expenses
For prepaid expenses, let’s just set cell K57 equal to cell J57 and then copy it to cells L57 – M57 keeping it constant to the amount for 2009. Be sure to copy the equation for Total Current Assets in cell J58 to columns K – L.
Deposits
We’ll make deposits going forward equal to the 2009 level just like we did for the Prepaid Expenses. The easy way to do this is to just copy the equations for Prepaid Expenses down and then use the Paint Brush to copy the format of cell J64 to cells K64 – M64 (or just repeat the procedure that we used for Prepaid Expenses).
Net Fixed Assets
In order to do a regression analysis of Net Fixed Assets versus Revenues, we need to copy the values of Net Fixed Assets in cells C64 – J64 from the Financials worksheet to the Regressions worksheet using the Paste Special – Values – Transpose methodology. Place the figures in cell K2 (label cell K1 as “Net Fixed Assets”!). Using the Data – Data Analysis – Regression procedure, re-set the dependent (Y) variable for cells K2 – K9 and set the Output Range cell to be A80. Label the results in cell A79 as “Revenues vs. Net Fixed Assets” and copy the format for the output from one of the previous regression output results using the Paint Brush. As you’ll notice from the regression statistics, this equation is pretty much meaningless. Only the intercept is significant, indicating that the level of Net Fixed Assets is pretty much independent of the Revenues. This virtually constant level can be verified by looking at the historical level of Net Fixed Assets which has average about $5 million over the previous 8 years. Nevertheless, let’s use the regression equation to project future Net Fixed Assets. In cell K64 the equation should be
=Regressions!$B$96+Regressions!$B$97*Financials!K5
Now copy this over to columns L and M. You also need to copy the Total Assets equation from cell J66 to columns K – M. You’ll notice in the income statement that our Depreciation expense which we modeled in the Income Statement portion earlier has now filled in since we have Net Fixed Assets completed on the Balance Sheet.
LIABILITIES & EQUITY
Accounts Payable
We now need to turn our attention to the Liabilities & Equity side of the balance sheet. In projecting the Liabilities, we want to focus on those items that vary directly with sales. In this case, the only liability account that we know is directly to our level of sales is Accounts Payable. More specifically, it is related to Cost of Goods Sold since payables is the credit provided by our suppliers. Let’s use a weighted-average of the Accounts Payable-to-COGS ratio. The equation in cell K69 should look like this:
=SUM($C$69:$J$69)/SUM($C$13:$J$13)*K13
Now copy this equation to columns L and M.
Accrued Income and Other Taxes
If we look at the history of this category of accrued expenses, we can see that it has varied a lot in the past. Since it has been virtually non-existent in the last two years, let’s just keep it at last year’s level of zero.
Bank Revolver
Financing is a choice variable. That is, we use our revolving line of credit, or “revolver”, as a source of financing when and if we need it. For the time being, let’s keep it constant. If we find that we need additional financing, we may want to investigate this source as a viable means of financing our shortfalls. Thus, we can set the level for years 2010 – 2012 equal to the level existing at the end of 2009 (the short-cut is to copy the equations for Deposits down which will maintain the formatting).
Current Portion of Long-term Debt
Like the revolver, we can keep this constant. If we were working inside of the company, we would know the specific repayment terms of the loan and calculate the current portion accordingly (we’ll do this in a later exercise). Since we don’t know the specific terms, let’s just keep it constant for now. Copy the Prepaid Expenses equation down to retain the formatting. Also, copy the equation for Total Current Liabilities across.
Long Term Debt
This category is just like the Current Portion of Long-term Debt category. We don’t know the specifics, so we’ll keep it constant. Copy the Bank Revolver equations down to achieve the constant results that we want.
At this point, the Interest Expense in the income statement will be complete since we have projected all of the interest-bearing debt (bank revolver, current portion of long-term debt and long-term debt) on the balance sheet.
Stockholder’s Equity
In this case, “Stockholder’s Equity” means “Common Stock” since we have a separate category for the Retained Earnings portion of Equity. This, also, is a decision variable. If we need additional financing, we may decide to fund the deficit through additional equity infusions. Thus, let’s keep it constant for now by copying the equations for Long-term debt down.
Retained Earnings
Retained Earnings is a function of Net Income and Dividends. Dividend policy is a function of the company’s level of profitability, debt policy and dividend policy. If we assume that no dividends are paid, then all of net income is added to Retained Earnings. This may change if we have a surplus of dividends available. The relationship between one year’s retained earnings and the next year is
Ending Retained Earnings = Beginning Retained Earnings + Net Income – Dividends
This is a relationship that every banker knows and had better be correct if you want them to believe your story about how you’re going to do in the future and how you can pay them back! Thus, let’s assume that no dividends are paid (for now) and put the following equation in cell K79:
=J79+K45-K132
Finally, copy the Total Equity and Total Liabilities & Equity equations over.
Cash
As we stated previously, we will use Cash as out “plug” figure. Thus, we need to calculate the amount of cash required to make the Balance Sheet “balance”. Copy the equation from cell J84 to columns K – M. This row is entitled “Assets & Liabs.” to indicate the difference between our forecasted assets and our project liabilities and equity. The negative nature of the results for 2010 – 2012 indicate that we will be generating much more income than we need for our financing requirements. We’ll see specifically where cash comes from and where it goes when we complete the Statements of Cash Flows.
To balance our balance sheets, we need to plug-in the amounts that exist in the Assets & Liabs. line. Go back to the cash line in cell K54 and write the following equation:
=K82-K64-K60-SUM(K55:K57)
This equation is taking the Total Liabilities & Equity amount and subtracting our projections of Net Fixed Assets, Deposits, and the sum of our non-cash Current Assets. You’ll see that this results in the Total Assets being equal to the Total Liabilities & Equity so the Balance Sheet balances (as it must). Since the figures are all very large and positive, we can now decide whether we want to pay dividends, or reduce debt, etc. Had the figures been negative, it would indicate that we need to find additional funding, either through more debt, tighter inventory controls or credit policy, or by selling new equity.
Exercise 9 will provide us with means of analyzing the results of both the past performance of the company and the projections that we have made. This information can then be used to assess the financial conditions of the company as well as question the assumptions that we have made in projecting its future operating results. Such information is important for the company, as an agent for the stockholders, as well as outsiders such as bankers and security analysts.