Using the Business Planning Starter Workbook

You can use the business planning starter workbook, shown in Figures 10-1 through 10-5, to construct pro forma financial statements that let you forecast profits and losses, financial condition, and cash flows for a business or organization. To use the workbook, you develop and then enter information on the assets; the creditor and owner equities at the start of the forecasting horizon; the expected changes in the assets and equities over the forecasting horizon; and the revenues and expenses for each period on the forecasting horizon.

Given data that includes your starting assets, liabilities, owner equity balances, and expected changes in these amounts for the forecasting horizon, this workbook constructs a balance sheet. Given data that includes sales and costs of sales, operating expenses, interest income and expenses, and marginal income tax rates, this workbook constructs an income statement. From the balance sheet and income statement, this workbook constructs a cash flow statement.

To enter your own data in the business planning starter workbook, use the following steps. Enter positive balances or increases as positive amounts, and enter negative balances or decreases as negative amounts.

1. Open the business planning starter workbook, BIZPLAN.XLS, from the companion CD.
   The starter workbook initially contains the default inputs shown in Figure 10-1.
2. Enter the cash and equivalents balance for the start of the forecasting horizon.
   The value you enter for Cash and Equivalents is the starting cash and cash equivalents (marketable securities), the dollar total of all the cash held at the beginning of the fore- casting period.
3. Enter the forecasted period yield that you expect the cash and equivalents to deliver.
   The model estimates the period interest income by multiplying the cash and equivalents balance by the yield on cash and equivalents.
4. Enter the accounts receivable balance for the start of the forecasting horizon.
   The value you enter for Accounts Receivable (A/R) is the starting accounts receivable balance, the balance at the beginning of the forecasting horizon, excluding any allow- ance for uncollectible amounts.
5. Enter the number of periods of sales in accounts receivable.
   The value you enter for # of Periods of Sales in A/R, or number of periods of sales in accounts receivable, is the number of periods or the fraction of a period for which sales are held in accounts receivable. If accounts receivable typically amount to about 30 days of sales and you use months as your forecasting periods, you hold one period of sales in accounts receivable. Alternatively, if accounts receivable typically amount to about 30 days of sales and you use years as your forecasting periods, you hold one-twelfth of a period of sales in accounts receivable.
6. Enter the dollar amount of the inventory held at the start of the forecasting horizon.
   The Inventory value is the starting inventory balance, the total dollar amount of the inventory purchased for resale or manufactured for resale and held at the beginning of the forecasting horizon.
7. Enter the forecasted dollar amount of inventory purchased or produced for each period of the forecasting horizon.
8. The Inventory Purchased/Produced value is the dollar total of items purchased or produced over the period.
9. Enter the amount of the other current assets held at the start of the forecasting horizon.
   The Other Current Assets starting balance is the dollar total of any other current assets with which you begin the forecasting horizon. These other current assets might include prepaid expenses, short-term investments, and deposits made with vendors.
10. Enter the amount of the change in the other current assets for each period in the forecasting horizon.
    The value for Chgs in Other Current Assets, or changes in other current assets for the period, is the dollar total of increases or decreases in the accounts included in the start- ing other Current Assets balance.
11. Enter the amount of the plant, property, and equipment at the start of the forecasting horizon.
    The starting Plant, Property, and Equipment balance is the dollar total of the fixed assets. This amount includes such items as realty, manufacturing equipment, and furniture.
12. Enter the amount of the change in the plant, property, and equipment (P,P,&E) for each period of the forecasting horizon.
    The Chgs in P, P, & E value is the dollar total of decreases or increases in the plant, property, and equipment accounts for the period. Increases in these accounts probably stem from purchases of additional fixed assets. Decreases in these accounts probably stem from disposal of assets.
13. Enter the amount of the accumulated depreciation on the plant, property, and equip- ment at the start of the forecasting horizon.
    The starting Accumulated Depreciation balance represents the depreciation expenses charged to date on the assets identified in the starting Plant, Property, & Equipment balance.
14. Enter the amount of the change in the accumulated depreciation for each period of the forecasting horizon.
    The Chgs in Accum. Depreciation value is the dollar total of increases and decreases in the accumulated depreciation account for the period. Increases in the accumulated de- preciation balance probably stem from the current period depreciation expense. Decreases in the accumulated depreciation balance probably stem from removing the accumulated depreciation attributed to a fixed asset that you disposed of.
15. Enter the amount of the other noncurrent assets at the start of the period.
    The starting other Noncurrent Assets balance is the dollar total of all other noncurrent assets held at the start of the forecasting period. Other noncurrent assets might include copyrights, patents, and goodwill.
16. Enter the amount of the change in the other noncurrent assets for each period of the forecasting horizon.
    The Chgs in Other Noncurrent Assets value is the dollar total increase or decrease for the period in the accounts included in the starting Other Noncurrent Assets balance.
17. Enter the amount of the accounts payable balance at the start of the forecasting horizon.
    The starting Accounts Payable (A/P) balance is the dollar total of amounts owed ven- dors for inventory at the start of the forecasting horizon. This starter workbook calcu- lates future Accounts Payable balances, based on the cost of sales volumes. To add precision to the forecasts of accounts payable, the model assumes that accounts payable represent debt incurred for the cost of sales.
18. Enter the number of periods of the cost of sales in accounts payable.
    The # Periods Cost of Sales in A/P is the number of periods or the fraction of a period for which the cost of sales is held in accounts payable. If accounts payable typically amount to about 30 days of cost of sales and you use months as your forecasting periods, you hold one period of cost of sales in accounts payable. Alternatively, if accounts payable typi- cally amount to about 30 days of cost of sales and you use years as your forecasting pe- riods, you hold one-twelfth of a period of cost of sales in accounts payable.
19. Enter the amount of the accrued expenses balance at the start of the forecasting horizon.
    The starting Accrued Expenses (A/E) balance is the dollar total of amounts owed ven- dors for operating expenses at the start of the forecast horizon. This starter workbook calculates future Accrued Expenses balances, based on the operating expenses levels. To add precision to the forecasts of accrued expenses, the model assumes that accrued ex- penses represent debt incurred for operating expenses.
20. Enter the number of periods of operating expenses in accrued expenses.
    The # Periods Operating Expenses in A/E value is the number of periods or the frac- tion of a period for which operating expenses are held in accrued expenses. If accrued expenses typically amount to 30 days of operating expenses and you use months as your forecasting periods, you hold one period of operating expenses in accrued expenses. Alternatively, if accrued expenses typically amount to about 30 days of operating expenses and you use years as your forecasting periods, you hold one-twelfth of a period of oper- ating expense in accrued expenses.
21. Enter the amount of the other current liabilities at the start of the forecasting period.
    The Other Current Liabilities starting balance is the dollar total of all other current li- abilities held at the start of the forecasting period. Other current liabilities might include income tax payable, product warranty liability, and the current portion of a long-term liability.
22. Enter the amount of the change in the other current liabilities for each period of the forecasting horizon.
    The Chgs in Other Current Liabilities value is the dollar total of increases or decreases for the period in the accounts included in the starting Other Current Liabilities balance.
23. Enter the amount of the long-term liabilities balance at the start of the forecasting horizon.
    The starting Long-Term Liabilities balance is the dollar total of debt that will be paid back sometime after the next year.
24. Enter the amount of the change in the long-term liabilities for each period of the fore- casting horizon.
    The Chgs in Long-Term Liabilities value is the increase or decrease for the period in the outstanding long-term debt. These changes might include decreases stemming from the amortization of principal through debt service payments and increases stemming from additional funds provided by creditors. You need to include the principal component of debt service payments as negative amounts because they decrease the amount of long- term liability.
25. Enter the amount of the other noncurrent liabilities at the start of the forecasting horizon.
    The Other Noncurrent Liabilities starting balance is the dollar total of all other noncurrent liabilities held at the start of the forecasting period. These might include deferred income tax, employee pension plan liabilities, and capitalized lease obligations.
26. Enter the amount of the change in the other noncurrent liabilities for each period of the forecasting horizon.
    The Chgs in Other Noncurrent Liabilities value is the dollar total of increases or decreases for the period in the accounts included in the starting Other Noncurrent Liabilities balance. These changes might include decreases stemming from the amortization of principal through debt service payments and increases stemming from additional funds provided by creditors.
27. Enter the amount of the owner equity balance at the start of the forecasting horizon.
    The Owner Equity starting balance is the dollar total of the capital originally contrib- uted by owners and the earnings retained by the business at the start of the forecasting horizon.
28. Enter the amount of the change in the owner equity balance for each period of the fore- casting horizon stemming from additional capital contributions, dividends, and other special distributions to owners.
    The Chgs in Owner Equity value is the dollar total of increases for the period in owner equity, other than those stemming from the profits of a business and all decreases in owner equity. For example, increases in the Owner Equity balance might result from additional offerings of common or preferred stock and treasury stock transactions; decreases in the Owner Equity balance might result from dividends and other distributions to stockholders.

29. Enter the sales revenue forecasted for each period of the forecasting horizon.
    The Sales Revenue values represent the forecasted sales revenues generated by the busi- ness over each period of the forecasting horizon.
30. Enter the cost of sales forecasted for each period of the forecasting horizon.
    The Cost of Sales values represent the forecasted costs of the inventory sold for the fore- casting horizon.
31. Enter those costs that fall into the first, second, and third operating expense classifica- tion or category for each period of the forecasting horizon.
    The operating expenses for Cost Centers 1, 2, and 3 represent the operating expenses for the forecasting horizon. These figures might be three expense classifications related to operating the business, or they might be the total expenses for three groups of expenses.
32. Enter the interest expense of carrying any debt used to fund operations or asset purchases.
    The Interest Expense values represent the period interest expenses of carrying any debt related to the business.
33. Enter the marginal income tax rate that, when multiplied against the profit or loss for the period, calculates the income tax expense (or savings).
    The Marginal Income Tax Rate value is the percentage that, when multiplied by the operating profit (or loss), calculates the income tax expense (or savings). If you are in- terested only in calculating pretax profits and losses, enter this amount as 0.

After you enter the required inputs, the starter workbook makes the calculations necessary to construct pro forma financial statements and calculate a set of rather standard financial ratios.