You can use the business planning starter workbook for many business projections. However, you might want to change the starter workbook so that it more closely matches your requirements. For example, you can add text that describes the business and the forecasting horizon. You can 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.
Changing the Number of Periods
You can rather easily increase or decrease the number of forecasting periods. To increase the number of periods, remove the borders from the last column; then copy the current last column to the right as needed. To decrease the number of periods, simply delete any unneeded column from the right side of the schedule. When you finish these steps, you can replace the borders on the right and reinstate cell protection as needed.
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 planning starter 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), following the steps described in the preceding section, “Changing the Number of Periods.” 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 there is a current period net loss before income taxes, you need to 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)
Once 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).
Combining This Workbook with Other Workbooks
Other starter workbooks on the MBA’s Guide to Microsoft Excel 2000’s companion CD are specifically designed to provide data to the financial statements with ratios workbook. For example, you might 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 planning starter workbook.
If you want to use workbooks together in this manner, you should 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.)
If you wanted to combine an asset depreciation workbook with the business planning starter workbook, for example, you might open both workbooks, copy the asset depreciation worksheet to the clipboard, add a new sheet to the business planning starter workbook (such as by choosing the Insert menu’s Worksheet command), and then paste the asset depreciation worksheet into the newly-added, blank worksheet in the business planning starter workbook.
Leave a Reply