Excel 4 Ur Business

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’s 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 Excel enables you to compute a value for a spreadsheet input that makes the value of a given formula match the goal you specify. For example, in our lemonade store example from post Sensitivity Analysis with Data Tables, suppose we charge $3.00 a glass. We want to know how many glasses we need to sell to break even. We can use Goal Seek to calculate this value. Essentially, Goal Seek embeds a powerful equation solver in your spreadsheet. To use Goal Seek, you need to provide Excel with three pieces of information:

• Set Cell This 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 This cell includes the numerical value for the goal that’s calculated in the set cell. In the lemonade example, because we want to determine a sales volume that lets us break even, To Value would equal 0.
• By Changing Cell This is 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 contains 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.xls, which is shown in the following figure. As in post Sensitivity Analysis with Data Tables , I’ve assumed an annual fixed cost of $45,000 and unit variable cost of $0.45. Let’s assume a price of $3.00. The question is how many glasses do we need to sell in a year to break even.

To start, insert any number for demand in cell D2. Choose Tools, Goal Seek, and then fill in the dialog box shown in the following figure.

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 the following figure. If we sell around 17,647 glasses of lemonade per year (about 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.

Before using Goal Seek, you should choose Tools, Options and then click the Calculation tab. Set the Maximum Change option, shown below, to a very small number (say .000001). The default setting (.001) causes Goal Seek to stop when the set cell is within .001 of To Value, which means that Excel might stop when it finds a demand that yields a profit between -.001 and .001. Entering a smaller value (such as .000001) for Maximum Change ensures that Goal Seek returns a value for the by-changing cell that yields a set cell value within .000001 of the goal.

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

You can begin to answer this question by setting up a spreadsheet 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.xls and in the following figure.

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 the following figure calculates the amount borrowed that results in monthly payments equal to $2000. With a limit of $2000 for monthly payments, we can borrow up to $237,007.03.

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 (most algebra teachers called it x) that solved a particular equation. Goal Seek is an equation solver, so solving story problems is made to order for Goal Seek. Here’s a typical high-school algebra problem. You can find the Excel solution in the file Maria.xls, shown in the following figure.

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?

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 drives.

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 the 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 the following figure.

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 both will have driven a distance of 640 miles.

Explore posts in the same categories: MS Excel Tune Up