How to Customize the QuickBooks Starter Workbook

By Stephen L. Nelson

You can use the business plan workbook in QuickBooks for many business projections. However, you may want to change the starter workbook so that it more closely matches your requirements. For example, you can add text that describes your business and the forecasting horizon.

You can also increase or decrease the number of periods; for example, you can increase the number of periods to 12 if your periods are months and you want to forecast an entire year.

Before you change anything on the starter workbook other than the forecasting inputs, unprotect the document. To do this, choose the Review tab and click the Unprotect Sheet button.

Unless you turn off cell protection, input cells in the inputs area of the business planning starter workbook are the only cells into which you can enter data.

Changing the number of periods

You can easily increase or decrease the number of forecasting periods.

To increase the number of periods, remove the borders from the last column and then copy the current last column to the right as needed.

To decrease the number of periods, simply delete any unnecessary columns from the right side of the schedule. (To delete a column, highlight the entire column by clicking at the top of that column, right-click to bring up Excel’s shortcut menu, and then choose Delete.) After you finish these steps, you can replace the borders on the right and reinstate cell protection as needed.

Performing ratio analysis on existing financial statements

If you want to perform financial ratio analysis on a set of existing financial statements, copy the contents of column C from the row in the inputs area of the business plan workbook that contains the sales revenue forecast (row 31) through the last row of the ratios table into column B.

Then remove the columns for periods 1 through 10 (columns C through L). Optionally, you can delete the Cash Flow Statement and add appropriate column headings as needed.

To use the modified starter workbook, enter the necessary Balance Sheet and Income Statement data in each of the unshaded cells in column B of the inputs area of the business planning starter workbook. (Typically, the As Of date of the Balance Sheet and the ending date of the Income Statement period are the same.)

Calculating taxes for a current net loss before taxes

To calculate the income tax expense as 0 when you have a current period net loss before income taxes, you edit the formula in the cell that calculates the income tax expense (or savings) for the first period (cell C115) so that it takes the maximum of the calculated expense amount or 0 by using the MAX function:

=MAX(C37*C113,0)

After you’ve done this, you can copy the formula into the rest of the cells in the forecasting horizon that calculate the income tax expense (or savings). To do this, select the cell with the formula you want to copy, click the Home button, and then click the Copy button. Next, select the range of cells into which you want to copy the formula, and click the Paste button.

Combining this workbook with other workbooks

A quick and perhaps obvious point: You may want to construct other workbooks to supply numbers to the business plan workbook discussed in this chapter. For example, you can construct an asset depreciation schedule that uses the straight-line depreciation convention for a $25,000 asset representing your entire plant, property, and equipment investment and then use this data in the business plan workbook.

If you want to use workbooks together in this manner, you should probably combine the workbooks into a single workbook. The easiest way to copy one of the workbooks is to copy the workbook’s worksheet to a blank worksheet in the other workbook. (Each of the starter workbooks uses only a single worksheet to make this process both easy and possible.)