Be Proactive: Use Forecasts to Plan for the Future

While many accounting tasks focus on documenting past activities, businesses also need to look forward - both short-term and long-term. Microsoft Office Small Business Accounting has a Forecast Cash Flow feature that simplifies short-term planning. This feature on the Company menu enables you to strategically plan upcoming cash receipts and payments. When you need to look long-term, the Scenarios feature in Microsoft Excel serves as an additional tool to use in conjunction with Small Business Accounting.

As we explore this often overlooked feature, we'll also show you how to use Range Names effectively, along with a couple of Fill Handle tricks that speed up building spreadsheets. In this article, we'll compare the total cost of renting space at three different buildings. However, this example only scratches the surface of how you can use Scenarios. Other common uses include:

• Creating best and worst case sales scenarios
• Calculating sales commissions
• Calculating operating profit for your business
• Performing break-even analysis
• Budgeting project costs

For instance, let's say that you're considering an office move to one of three buildings. As shown in Table 1, the square feet, annual rent increases, and starting rental rates all differ. Of course, this isn't the sort of data that you would enter in Small Business Accounting - until you sign a lease. Using the Scenario Manager we can determine which lease has the lowest total cost, which enables you to forecast a crucial fixed expense.

Table 1: We'll use Scenario Manager to compare these 5 year leases

	Building 1	Building 2	Building 3
Square Feet	4,750	5,000	5,250
Annual Rent Increase	3%	2.5%	2%
Year 1 Rental Rate	18	16.50	17

Spreadsheet scenarios can help you quickly compare the differences between differing situations without having to build multiple versions of the same set of calculations. Excel's Scenario Manager enables you to store multiple sets of inputs, which in turn are passed through your calculations and result in a summary report.

To get started with Scenario Manager, open a blank workbook, and then follow these steps:

1. Enter the words Square Feet in cell A1 of a blank worksheet.
2. Enter the number 4700 in cell B1.
3. Assign the range name SquareFeet to cell B1 by clicking in the Name box in the upper left-hand corner of the screen.

   Hint: The Name box typically shows the address of the current cell, but you can click in this box and assign a name. Range names should typically only include letters and numbers. You can also manage range names in Excel by choosing Insert, Name, and then Define.

4. Enter the words Annual Rent Increase in cell A2.
5. Widen column A by choosing Format, Column, and then AutoFit Selection.
6. Enter the number 2% in cell B2.

   Tip: Percentages can either be entered in decimal format, such as .02, or as formatted percentages, 2%. If you include the percent sign, Excel will know that you're entering a percentage, and you'll be able to skip the step of applying the Percentage number format to convert .02 to 2% for display purposes.

7. Assign the range name Increase to cell B2.
8. In cell A3, enter the words Rent - Year 1.

   Do this: Be sure to use these exact words so that you can try a shortcut for filling in the descriptions for the remaining 4 years.

9. Click on cell A3, and then drag the Fill Handle down to cell A7. Cells A4 through A7 should fill with Rent - Year 2 through Rent - Year 5, respectively.

   Time saver: The Fill Handle is the black notch in the lower right-hand corner of the selected area of your worksheet. In this case, the selection is cell A3, but you can also use the Fill Handle when you have multiple cells selected. As you can see, dragging the Fill Handle enables you to avoid repetitive typing when your input is a series.

10. Enter the number 18 in cell B3 for the starting rental rate.
11. Assign the range name RentalRate to this cell.
12. Enter this formula in cell B4:

    =ROUND(B3*(1+Increase),2)

    Note: The ROUND function rounds the rental rate calculation to the nearest two decimals.

    Warning: Use caution when entering this formula - if you click on cell B3 instead of typing in the address, Excel will automatically add the range name of RentalRate to the formula, resulting in:

    =ROUND(RentalRate*(1+Increase),2)

    If you use range name RentalRate in our formula in cell B4, you'll have unwanted consequences when you carry out the next step. Accordingly, be sure that this formula is in cell B4:

    =ROUND(B3*(1+Increase),2)

    Strategy: We used the range name of Increase to refer to cell B2. This makes the formula easier to audit, as we know at a glance that the formula is referring to the starting rental rate multiplied by 1 plus the annual rate increase. Further, when we copy the formula down to the other years, the formula will always reference the Increase cell.

13. Double-click the Fill Handle in cell B4. This action should automatically copy the formula down, resulting in a value of 19.48 in cell B7.

    Trick: When you double-click the Fill Handle, Excel automatically copies the contents of the current cell down the column until it encounters a blank cell in the preceding column. This strategy enables you to avoid moving past the desired ending cell, as is often the case when you drag the Fill Handle.

14. Select cells B3 through B7, and then choose Format, and then Cells. Click Currency on the Number tab, and then click OK.
15. The next step is to calculate the annual cost for the first year by multiplying the rental rate by the square feet. To do so, enter this formula in cell C3:

    =B3*SquareFeet

    Caution: Ensure that the formula references B3, and not the range name Rental Rate.

16. Double-click the Fill Handle in cell C3 to copy this formula down, which should result in a value of $91,556.00 for cell C7.
17. Enter the words Total Lease Cost in cell A8.
18. Enter this formula in cell C8:

    =SUM(C3:C7)

19. Assign the range name TotalCost to cell C8, which should have a value of $440,249.00.

    At this point your spreadsheet should look like Figure 1. You're now ready to use Excel's Scenario Manager to compare the total costs of the three leases.

    
    Figure 1: We'll use this spreadsheet to analyze three different scenarios.

Entering Scenarios

Establishing scenarios only involves a few steps:

1. Choose Tools, and then Scenarios.
2. Click the Add button, and enter Building 1 for the Scenario Name.
3. As shown in Figure 2, set the Changing Cells field to B1:B3, and then click OK.

   
   Figure 2: Specify the cells that comprise your scenario inputs.

   Don't miss: In the Scenario Values dialog box, the values for this scenario are automatically captured from the spreadsheet. Note that since we assigned range names to our input cells, the Scenario Values dialog box returns the cell names, rather than addresses. This helps ensure data integrity, since you don't have to cross-reference cell addresses back to the spreadsheet. If you see cell addresses, such as $B$1 or $B$2, you skipped a range naming step when you initially built the worksheet. Your scenario will work, but you'll have to cross reference the addresses to confirm your inputs.

4. Proceed with the next scenario by clicking the Add button.
5. Enter the scenario name of Building 2, and then click OK.
6. Add the inputs of 5,000, 2.5%, and 16.50, as shown in Figure 3, and then click Add.

   
   Figure 3: Use these inputs for the second scenario.

7. Repeat the steps to create the third scenario named Building 3 with inputs of 5,250, 2%, and 17.00, as shown in Figure 4, and then click OK.

   
   Figure 4: Use these inputs for the third scenario.

8. Choose Tools, Scenarios, and then click Summary.
9. As shown in Figure 5, choose the report type of Scenario Summary, set the result cells to C8, and then click OK.

   
   Figure 5: Use these inputs for your scenario summary.

Excel automatically inserts a new worksheet named Scenario Summary that provides an instant comparison of the three scenarios. You can see that Building 2 provides the lowest occupancy cost. Keep in mind, though, that the Increase percentages are rounded. To correct this, follow these steps:

1. Select cells E7:G7.
2. Choose Format, then Cells.
3. Change the Decimal Places to 1, and then click OK.
4. The completed scenario summary is shown in Figure 6.

   
   Figure 6: The Scenario Summary displays the differences among the three leases.

You may notice that row 4 of the Scenario Summary sheet is hidden. Display this row by clicking the + symbol to the left of row 3 on the worksheet frame. You can now see the default comments that Excel automatically entered as you built each individual scenario. You can override these comments with your own thoughts as you're entering or editing scenario inputs and add instant documentation to your summary table.

And There's More

Other uses for Scenario Manager involve toggling through each scenario onscreen. To do so, return to the rental rate worksheet, and choose Tools, and then Scenarios. Choose Building 2 from the list, and then click Show. The inputs on the worksheet automatically change to those of the Building 2 scenario, and the total lease cost recalculates automatically. Next, click on Building 3, and click Show. As you can see, Scenario Manager enables you to view each scenario individually onscreen. Alternatively, as shown above you can create an overview of all scenarios with just a click of the mouse.