How to Enter a Business Planning Starter Workbook in QuickBooks

By Stephen L. Nelson

To use a business planning workbook, you develop and then enter information about the following: assets, creditor and owner’s equities at the start of the forecasting horizon, expected changes in the assets and equities over the forecasting horizon, and revenues and expenses for each period on the forecasting horizon.

To enter your own data in the business planning starter workbook, perform the following steps, entering positive balances or increases as positive amounts, and negative balances or decreases as negative amounts:

  1. Open the business plan workbook, bizplan.xls, by choosing Excel’s File→Open command and then entering www.stephenlnelson.com/bizplan.xls in the Filename box.

    The starter workbook initially contains the default inputs shown. Note that you can see only about the first 29 rows and the first 11 or so columns of the inputs area.

    image0.jpg

  2. Enter the Cash & Equivalents balance for the start of the forecasting horizon.

    The value that you enter for Cash & Equivalents is the dollar total of all the cash held at the beginning of the forecasting 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 that you enter for Accounts Receivable (A/R) is the starting accounts receivable balance, which is the balance at the beginning of the forecasting horizon, excluding any allowance for uncollectible amounts.

  5. Enter the number of periods of sales in accounts receivable.

    The value that you enter for # 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 (one month) 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, which is 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.

    The Inventory Purchased/Produced value is the dollar total of items purchased or produced over the period.

  8. 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 may include prepaid expenses, short-term investments, and deposits made with vendors.

  9. 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 starting Other Current Assets balance.

  10. Enter the amount of the plant, property, and equipment at the start of the forecasting horizon.

    The starting Plant, Property, & Equipment balance is the dollar total of the fixed assets. This amount includes such items as real estate, manufacturing equipment, furniture, and the Learjet.

  11. Enter the amount of the change in 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.

  12. Enter the amount of the accumulated depreciation on plant, property, and equipment 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 P, P, & E balance.

  13. 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 depreciation 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.

  14. 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 may include copyrights, patents, and goodwill.

  15. 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.

  16. 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 vendors for inventory at the start of the forecasting horizon. This starter workbook calculates 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.

  17. Enter the number of periods of the cost of sales in accounts payable.

    The # Periods Cost of Sales in A/P entry 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 (one month) of cost of sales in accounts payable.

  18. 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 vendors 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 expenses represent debt incurred for operating expenses.

  19. 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 fraction 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.

  20. 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 liabilities held at the start of the forecasting period. Other current liabilities may include income tax payable, product warranty liability, and the current portion of a long-term liability.

  21. 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.

  22. 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.

  23. Enter the amount of the change in the long-term liabilities for each period of the forecasting horizon.

    The Chgs in Long-Term Liabilities value is the increase or decrease for the period in the outstanding long-term debt. These changes may 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.

  24. 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 may include deferred income tax, employee pension plan liabilities, and capitalized lease obligations.

  25. 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 may include decreases stemming from the amortization of principal through debt service payments and increases stemming from additional funds provided by creditors.

  26. Enter the amount of the owner’s equity balance at the start of the forecasting horizon.

    The Owner Equity starting balance is the dollar total of the capital originally contributed by owners and the earnings retained by the business at the start of the forecasting horizon.

  27. Enter the amount of the change in the owner’s equity balance for each period of the forecasting 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’s equity, other than those stemming from the profits of a business, and all decreases in owner’s equity. For example, increases in the Owner Equity balance may result from additional offerings of common or preferred stock and treasury stock transactions; decreases in the Owner Equity balance may result from dividends and other distributions to stockholders.

    Changes to the owner’s equity balance resulting from the profit or loss for the period are calculated on the income statement; they aren’t entered.

  28. Enter the sales revenue forecasted for each period of the forecasting horizon.

    The Sales Revenue values represent the forecasted sales revenues generated by the business over each period of the forecasting horizon.

  29. 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 forecasting horizon.

  30. Enter those costs that fall into the first, second, and third operating expense classifications or categories 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 may be three expense classifications related to operating the business, or they may be the total expenses for three groups of expenses.

  31. 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.

  32. Enter the income tax rate that, when multiplied against the profit or loss for the period, calculates the income tax expense (or savings).

    The Income Tax Rate value is the percentage that, when multiplied by the operating profit (or loss), calculates the income tax expense (or savings). This can be a little tricky because business income taxes are progressive.

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