When it comes to solving problems where any number of variables can affect the outcome, Excel’s Solver tool can help you find the answer. Many Excel courses use Solver to introduce beginning students to using what-if analysis in decision making. Typically, students are guided through a step-by-step exercise demonstrating how Solver can be used to make corporate budgeting decisions.
However, after working with this type of exercise a number of times with my students, I’ve found that while most were able to get through it fairly well, they had no idea how Solver arrived at the results, much less how to apply the tool to their own problems.
Start with something familiar
Most students in my classes had little or no background in financial accounting. Hence, they found it difficult to understand exactly what was being asked for in the corporate budgeting problem, let alone how to solve it with Solver. So instead of using corporate budgets to teach Solver, I decided to work with something closer to the student’s own experience—making and spending money. Before introducing Solver, I had them first work with a template they could use to develop a personal budget, similar to the one below.
|Students began the exercise with a personal budget worksheet.|
I suggested they all start out with a monthly salary of $2,000. Their assignment was to fill in what they considered would be typical values for each of the expense categories. Then, they were to adjust those values to answer the question “What if I wanted to save 5 percent of what I earn each quarter?” Or “How can I get G21 to equal 5 percent of $6,000 (or $300)?”
After working with the template, the students quickly learned that in order to meet the savings requirement, they had to play extensively with the numbers. Some students found that trying to work out the budget manually by plugging in different numbers was just too tedious a task and asked if they would still get credit if they at least came close to $300, such as $275 or $325. I insisted they not give up until they got the exact figure, because I knew that after struggling through the problem manually, they would be ready to have Solver do it for them.
Let Solver do it
Once the students demonstrated they could perform a what-if analysis with the template, it was time to introduce them to Solver. I presented them with a worksheet that looked something like this:
|This worksheet posed the problem of adjusting expenses to eliminate debt.|
From this worksheet, the students could see that even though Amy is earning what seems to be a fair amount of money ($30,000 a year), she is definitely running herself into serious debt.
I then proposed the following problem to the class: What if Amy wants to get out of debt? To do so, she must first stop spending more than she earns by either eliminating or reducing some of her monthly expenses. She must also begin saving a small amount each month to cover any unforeseen emergency that might push her back into debt.
Before you begin
Before attempting to solve any problem with Solver, make sure it has been installed on the system. Solver does not install automatically with the typical MS Office 2000 setup, so there is a good chance it may not be available on the machine you are using. If you don’t see the Solver command on the Tools menu, choose Add-ins from the Tools menu instead. If Solver appears in the Available Add-ins list box, you can load it without having to rerun the Setup program. Otherwise, you will need to get the Install Disk for Excel and install it separately.
Once Solver is installed and loaded, you can choose Solver from the Tools menu to open the Solver Parameters dialog box, shown below.
|Excel opens the Solver Parameters dialog box when you choose Solver from the Tools menu.|
In the above example, we first set the target cell C18 to the value 25. This is what income ($2,500) minus expenses ($2,550) must equal in order for Amy to achieve the goal of paying down her debt and adding a small amount to her savings each month.
Next, we specified what cells Solver would be allowed to change to arrive at that figure. Because some items were fixed costs (taxes, rent, credit card payment, and the car loan and insurance), we couldn’t choose them. From among the remaining items, we selected the cells containing the more flexible categories: clothes, entertainment, and miscellaneous (C6, C10, and C13, respectively).
To lower Amy’s expenses, we could have just eliminated some budgeted items entirely. Fortunately, we didn’t have to take such drastic action. Using Solver, we could choose two or three budgeted expense items that are not fixed costs and let Solver decide how much we must reduce those expenses to meet Amy’s goal.
After we clicked the Solve button, Solver filled in the new expense figures for the chosen budgeted items. By following Solver’s suggestions for making minor adjustments to these expenses—basically subtracting $25 from each category—Amy not only will eventually get out debt, but will also be able to build up her savings.
|Solver can adjust multiple variables to achieve a set result.|
After this demo, I had the students use Solver to solve similar personal budgeting problems. In most cases, over 80 percent of the class could solve simple problems with Solver, while at least 95 percent could at least solve the problems manually by plugging in numbers.
After working with the personal budget problems, they were also ready to tackle the corporate budgeting example in the course materials. More importantly, however, many students found that they were able to relate what they had learned to their own life decisions.
Personal budgeting can get personal
When teaching personal budgeting, be prepared for some strong reactions from the students. For example, one student reacted angrily at the thought that Amy, the student in the demo, would spend $300 a month on clothes! Another felt that instead of going through all the trouble of budgeting, Amy should just get a new job that pays more money. Yet another student said she couldn’t see any reason for working out a budget for the sole purpose of saving money. After all, since the U.S. government (at the time) was billions of dollars in debt, what was wrong with a U.S. citizen owing a few hundred dollars? Without a doubt, these exercises really hit home.
Many also reacted strongly against using the figures and expense categories in the personal budget template, saying that they’d rather use their own salary and expenses. Why waste time with an example? I gladly let them apply the exercise to their own circumstances; but I cautioned them that the final paper they handed in should disguise the numbers in some way, for privacy purposes.
However, using their own figures really paid off. Many found that what they learned from the analysis did help them make some important decisions. For example, one student was concerned because everyone was telling her and her husband that they would not be able to afford a baby on their current salaries. When she and her husband worked on their budget together as part of the assignment, they found that they could!
How do you explain business concepts to people who don’t have business backgrounds? Personal budgets, credit card bills, car or house loans? Send us your tips for explaining Excel tools such as Goal Seek and Solver.