|
|
|
By Jim Boyce
SPREADSHEETS ARE GREAT for tracking figures (and facts). They keep you up-to-date on the status of your business, your department, a specific project or whatever other entity you call on them to model. They're also great problem solvers-crystal balls that let you play what-if with the numbers.
You probably spend hours plugging numbers into cells, watching the bottom line change as you do so. If you're lucky, you'll eventually hit on numbers that foretell a happy, prosperous future.
But that's too much work. Why plug in numbers manually when you can let the spreadsheet do it for you? Your computer can fill in those numbers faster than you can, testing them until it comes up with exact-ly the ones you're looking for. All you need is a goal.
Both Microsoft Excel and Lotus 1-2-3 include a goal seeker function. In Excel you'll find Goal Seek under the Tools menu. In 1-2-3 the function is under Range/Analyze/Backsolver. I'll use Excel for the example, but 1-2-3 works almost identically.
Assume you're trying to forecast your business growth over the next five years. You're basing your projection on the current year's sales and costs, factoring in annual increases in cost of goods, marketing and other elements. You want to reach a certain net profit by, say, 1998. With Goal Seek, you specify your goal (the net profit figure you want for 1998), select a value on which that goal is based (such as increase in cost of goods for 1997), then let Goal Seek start plugging in numbers in the cost-of-goods cell until it achieves the desired net profit figure. Rather than randomly entering numbers, however, Goal Seek starts with high and low values, converging them until it achieves the specified goal. And it does so with blinding speed.
Using Goal Seek is easy. You just specify three elements: the goal cell, the goal value and the cell to manipulate to achieve the goal. As an example, I'll use a five-year sales forecast for Froboz Foods.
Download froboz.xls.
The projected sales figures for each year are given. Cost of goods, general and administrative (G&A), and marketing expenses for 1996 are actual figures. You'd calculate these three items for subsequent years based on the factors in cells B19 through B21 and the rates of change for each year specified in cells C17 through F17.
Unfortunately, the year 2000 shows a net loss. Now you're going to take over the company and use Goal Seek to make sure the business starts the next millennium in the black.
Select cell F13, which is the net income for 2000. Then choose Tools/Goal Seek. Excel opens a Goal Seek dialog box that prompts you for three elements: Set Cell, To Value and By Changing Cell. Set Cell references the goal cell, and already contains the reference to cell F13. Click on the To Value text box and type the value 1,800, which is your net profit goal for 2000. Next, click on the By Changing Cell text box, then click on cell F17, which is the rate of change for 2000. Choose OK to let Goal Seek do its work.
Goal Seek begins converging the number in cell F17 until the value of F13 (net profit for 2000) reaches $1,800. The resulting number in cell F17 is -0.04, which means you'll have to reduce the rate of change in 2000 to minus 4 percent, or decrease your cost of doing business by 14 percent over 1999.
But decreasing your cost by 14 percent in one year probably isn't realistic, given the rates of change in the previous years. So, you may want to try reducing your current cost-of-goods estimate. You may be able to decrease your current cost of goods by a small percentage and still see light at the end of the tunnel in 2000.
Run Goal Seek again, this time choosing cell B19 as the cell to change. Keep the same target cell and goal value. The result is you'll need to reduce cost of goods in the current year to 0.21, or 21 percent. Although that reduction may not be possible, you can see the impact that reducing your expenses has on your profit margin. Now you can begin trying other changes as well, such as reducing marketing costs.
Click Here to see a
24.6KB bitmap image of artwork
which goes with this article, entitled:
Microsoft Excel
After you plug in a few numbers with Goal Seek, you'll start to wonder if you can automatically manipulate more than one cell to reach your desired goal. For instance, you may want to change not only cost of goods, but also G&A and marketing, to achieve your net profit goal for 2000. Goal Seek can't do this for you, but Solver can.
Unlike Goal Seek, which modifies only one cell, Solver can play with the values of multiple cells to reach a goal.
To start Solver, first select the goal cell (net profit in F13, for this example) and choose Tools/Solver. (In 1-2-3, choose Range/Analyze/Solver.) Excel displays a Solver Parameters dialog box. Use this to set the goal value, select one or more ranges of cells to modify, apply constraints to the problem and set other options.
Next, select the Value radio button and type 1,800 in the associated text box. This is your net profit goal for 2000. Click on the By Changing Cells text box, then select the range of cells B19 through B21, which are your cost of goods, G&A and marketing percentages.
Next, click on the Add button to open the Add Constraint dialog box and apply a constraint to the problem. Assume you don't expect to reduce your cost of goods to less than 26 percent. Select the Cell Reference text box, then click on cell B19 (cost of goods) to select the cell. From the Constraint drop-down list, choose >= (greater than or equal to). Click on the last text box and type 0.26, then select OK. The Solver Parameters dialog box reappears with the specified constraint. Now, choose Solve.
When Solver is finished, you'll find you can reach your net profit goal of $1,800 in 2000 by reducing your cost of goods to 26 percent, G&A to 14 percent, and marketing to 21 percent. As with Goal Seek, you can retain the solution Solver finds or revert the spreadsheet to its original numbers.
By clicking on the Options button in the Solver Parameters dialog box, you can set various options, including the length of time Solver will work on the problem, the number of iterations it will perform, precision and tolerance.
Even though Solver is more complex than Goal Seek, it still offers incredible forecasting ability in an easy-to-use interface. When you apply it to your own spreadsheets, you'll be impressed by the types of problems Solver can resolve, from time management to bottom-line profits and everything in between.
Contributing Editor Jim Boyce is the lead author of Special Edition: Using Windows 95 Communications (Que, 1996). Contact Jim in the "Applications" topic of WINDOWS Magazine's areas on America Online and CompuServe. To find his E-Mail ID Click Here
|
|
|