Back to May 1996 Features
Up to Table of Contents
Ahead to May 1996 Features

May 1996 Features

Chart Your Course

Build a Financial Model in Excel and Sail
Through Business Forecasts

By Lori L. Bloomer, Associate Editor, Features

Click Here to see a 12.7 KB bitmap image of artwork which goes with this article, entitled:
Flagship Consulting

THE FORECAST CALLS for smooth sailing-provided you've got the proper navigational tools. If you're charting your course through the rocky waters of a business projection, all you need is your spreadsheet.

Here's how you can use Excel to build a financial model for predicting the future of your business. Let's look at three scenarios: the worst case, the best case and the most likely outcome. Later on, we'll work on a few more advanced tricks.

From the Ground Up

We'll use a small computer consulting firm, Flagship Consulting, as our case in point. Let's assume the firm was founded in 1991 and employs two consultants and an administrator. To keep things simple, we'll consider just two variables: costs (operating expenses, materials and payroll) and revenues (all monies earned).

Flagship wants to hire a third, and possibly a fourth, consultant. Their salaries and benefits would each amount to 10 percent of the company's operating expenses for the previous year. Our simple modeling technique will tell us if this is feasible.

First, we need to assemble a comprehensive history of the company's financial health, with detailed records of revenues and expenditures for at least the past year. The more time you can cover at the outset, the better. We'll outline the past four years. Our initial spreadsheet looks like the one below.

Flagship's 1995 revenues increased by 10 percent over the prior year. The two previous years showed gains of 4 percent and 7 percent. Expenses, meanwhile, have been growing at about 4 percent per year.

Variable Soup

Let's start with the most likely scenario. If the growth rate remains constant, the company can expect 13 percent growth without the additional staff. Let's say each new consultant will bring in 20 percent more revenue. That means revenue would rise a total of 53 percent. As mentioned earlier, expenses will likely climb 24 percent-20 percent for the new employees' salaries plus the historical 4 percent bump.

Here's a more optimistic view: Assume the company will grow by 15 percent next year. In addition, each new consultant will add 50 percent to Flagship's revenue.

For the least optimistic assumption, let's suppose that revenues grow less than expected and expenses climb more. Growth levels off, and revenues rise at 10 percent, the same as last year, instead of the hoped for 13 percent. To exacerbate matters, the new consultants bring in no new revenues to compensate. Moreover, expenses jump 6 percent over the previous year's, instead of the expected 4 percent.

The Mechanics

Click Here to see a 8.45 KB bitmap image of artwork which goes with this article, entitled:
Chart 1

Once you've built your spreadsheet as outlined above, go to Tools/Scenarios, and add the following scenarios to cover all the cases described here. The changing cells are the 1996 revenue and expenses cells. Following our example, enter $F$3;$F$4 in the box labeled Changing Cells. Then enter the formulas listed above for each scenario when Scenario Manager asks for values for the changing cells.

What we've done is modify the previous year's data with our projections. For example, the 1996, pessimistic, 2 new staff figures were arrived at by calculating a flat 10 percent increase to sales and adding 26 percent to expenses (10 percent for each new employee's salary, plus the 6 percent increase in costs).

Once you've entered all the scenarios, click on Summary and choose Scenario Pivot Table. The cells will be organized in alphabetical order, allowing you to see at a glance the ramifications of each action. You'll see that the most pessimistic is the only scenario in which the company loses money.

More Variables

Click Here to see a 4.34 KB bitmap image of artwork which goes with this article, entitled:
Chart 2

Now, let's try a few tricks. Flagship has decided to hire one additional consultant immediately, but wants to wait on a second new hire until the company shows at least $20,000 in profit. We'll set up a condition that will show approximately when the company can hire that fourth consultant.

Start with the results from the scenarios in which you added a single staffer. Set up the spreadsheet below. In column C, use a formula to determine the average daily profit in this form: "=cell/365." From that, divide the value of your daily profit into $20,000 (your target number) to find the number of days it will take before you can hire the fourth consultant.

From this projection, it appears likely that Flagship will be able to afford another employee in about two months, unless the worst case becomes reality. If that happens, it will take a year and a half before the company can afford to do so.

Look Me Up Sometime

If you keep your business records from previous years in an Excel spreadsheet, you'll probably want to design your financial model to look up values automatically.

Let's assume you don't want every figure in your annual budget on this projection-just the gross amounts of loss and gain, and the net result. Open your datasheet and select the cells you want to include in your financial plan.

Select the relevant columns and copy the cells to Clipboard. Now switch to your target spreadsheet. Select the target columns for the data, then right-click to bring up a menu. Select Paste Special/All and push the Paste Link button. You now have an active link between the two spreadsheets, and every update to the first spreadsheet will update the second.

A Dose of Reality

Click Here to see a 5.39 KB bitmap image of artwork which goes with this article, entitled:
Chart 3

Once you have your projections, you can measure your actual performance against them. You can use links to track your progress dynamically. For example, say Flagship has in fact hired one new consultant. Copy the data from the projection of daily earnings discussed earlier, which reflected one addition to staff. Add a fourth case to your scenario: "Actual." This is based on the actual profit realized to date, which is then projected for the full year. Now, take the current sum of the profits and losses for this year from the financial records, link them to the projection as we did before, and see how you're doing.

Track the calendar by how many days have elapsed out of 365. Divide the number of days into your company's net financial status to see a figure for the average daily profit. You can then measure those numbers against your scenarios.

Let's assume it's currently 120 days into the year. Figure the daily profit as described earlier, multiply it by the number of days elapsed and you'll get a clear picture of profit. As you can see from the above spreadsheet, Flagship is doing better than the realistic projection, but not as well as the optimistic.

The only new formula you need to introduce is "=Daily Profit*Days" for arriving at the current net profit. In place of the header words, add the cell numbers to the top cell, then copy and paste the cell into the other cells. The formulas will update automatically, using the correct cells.

Once you've tweaked your numbers, study them. In this case, the growing Flagship Consulting can certainly afford to hire at least one consultant, and probably a second as well.

Although our example was deliberately simple, it should give you an idea of what Excel can do to help you get a glimpse of your financial future, with no more than existing records and some judiciously applied variables.

Back to May 1996 Features
Up to Table of Contents
Ahead to May 1996 Features