MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Overview

• For a given price, how many glasses of lemonade does a lemonade store need to sell per year to break even?

• We want to pay off our mortgage in 15 years. The annual interest rate is 6 percent. The bank told us we can afford monthly payments of $2,000. How much can we borrow?

• I always had trouble with “story problems” in high-school algebra. Can Excel make solving story problems easier?

The Goal Seek feature in Microsoft Office Excel 2007 enables you to compute a value for a worksheet input that makes the value of a given formula match the goal you specify. For example, in our lemonade store example from Chapter 15, “Sensitivity Analysis with Data Tables,” suppose we have fixed overhead costs, fixed per-unit costs, and a fixed sales price. Given this information, we can use Goal Seek to calculate the number of glasses of lemonade we need to sell to break even. Essentially, Goal Seek embeds a powerful equation solver in your worksheet. To use Goal Seek, you need to provide Excel with three pieces of information:

• Set Cell

• Specifies that the cell contains the formula that calculates the information you’re seeking. In the lemonade example, the Set Cell would contain the formula for profit.

• To Value

• Specifies the numerical value for the goal that’s calculated in the Set Cell. In the lemonade example, because we want to determine the sales volume that represents the breakeven point, the To Value would be 0.

• By Changing Cell

• Specifies the input cell that Excel changes until the Set Cell calculates the goal defined in the To Value cell. In the lemonade example, the By Changing Cell would contain annual lemonade sales.

• For a given price, how many glasses of lemonade does a lemonade store need to sell per year to break even?

• Our work for this section is in the file Lemonadegs.xlsx, which is shown in Figure 16-1. As in Chapter 15, I’ve assumed an annual fixed cost of $45,000.00 and variable unit cost of $0.45. Let’s assume a price of $3.00. The question is how many glasses of lemonade we need to sell each year to break even.

  Figure 16-1: We’ll use this data to set up the Goal Seek feature to perform a breakeven analysis.

• To start, insert any number for demand in cell D2. In the What-If Analysis group on the Data tab, click Goal Seek. Now fill in the Goal Seek dialog box shown in Figure 16-2.

  Figure 16-2: The Goal Seek dialog box filled in with entries for a breakeven analysis

• The dialog box indicates that we want to change cell D2 (annual demand, or sales) until cell D7 (profit) hits a value of 0. After clicking OK, we get the result that’s shown in Figure 16-1. If we sell approximately 17,647 glasses of lemonade per year (or 48 glasses per day), we’ll break even. To find the value we’re seeking, Excel varies the demand in cell D2 (alternating between high and low values) until it finds a value that makes profit equal $0. If a problem has more than one solution, Goal Seek will still display only one answer.

• We want to pay off our mortgage in 15 years. The annual interest rate is 6 percent. The bank told us we can afford monthly payments of $2,000. How much can we borrow?

• You can begin to answer this question by setting up a worksheet to compute the monthly payments on a 15-year loan (we’ll assume payments at the end of the month) as a function of the annual interest rate and a trial loan amount. You can see the work I did in the file Paymentgs.xlsx and in Figure 16-3.

  Figure 16-3: You can use data such as this with the Goal Seek feature to determine the amount you can borrow based on a set monthly payment.

• In cell E6, the formula –PMT(annual_int_rate/12,years,amt_borrowed) computes the monthly payment associated with the amount borrowed, which is listed in cell E5. Filling in the Goal Seek dialog box as shown in Figure 16-4 calculates the amount borrowed that results in monthly payments equal to $2,000. With a limit of $2,000 for monthly payments, we can borrow up to $237,007.03.

  Figure 16-4: The Goal Seek dialog box set up to calculate the mortgage example

• I always had trouble with “story problems” in high-school algebra. Can Excel make solving story problems easier?

• If you think back to high-school algebra, most story problems required you to choose a variable (usually called it x) that solved a particular equation. Goal Seek is an equation solver, so perfectly suited to solving story problems. Here’s a typical high-school algebra problem:

• Maria and Edmund have a lover’s quarrel while honeymooning in Seattle. Maria storms into her Mazda Miata and drives 64 miles per hour toward her mother’s home in Los Angeles. Two hours after Maria leaves, Edmund jumps into his BMW in hot pursuit, driving 80 miles per hour. How many miles will each person have traveled when Edmund catches Maria?

• You can find the Excel solution in the file Maria.xlsx, shown in Figure 16-5.

  Figure 16-5: Goal Seek can help you solve story problems.

• Our Set Cell will be the difference between the distance Maria and Edmund have traveled. We will set this to a value of 0 by changing the number of hours Maria drives. Of course, Edmund drives two hours less than Maria.

• I entered a trial number of hours that Maria drives in cell D2. Then I associated the range names in the cell range C2:C8 with cells D2:D8. Because Edmund drives two fewer hours than Maria, in cell D4 I’ve entered the formula Time_Maria_drives–2. In cells D6 and D7, we use the fact that distance=speed*time to compute the total distance Maria and Edmund travel. The difference between the distances traveled by Edmund and Maria is computed in cell D8 with the formula Maria_distance–Edmund_distance. Now I can fill in the Goal Seek dialog box as shown in Figure 16-6.

  Figure 16-6: The Goal Seek dialog box filled in to solve an algebra story problem

• We change Maria’s hours of driving (cell D2) until the difference between the miles traveled by Edmund and Maria (cell D8) equals 0. We find that after Maria drives 10 hours and Edmund 8 hours, they will each have driven a distance of 640 miles.