Excel 4 Ur Business

Overview

Suppose I have a list of songs that are played on the radio. For each song, I know the singer, the date the song was played, and the length of the song. How can I answer questions such as

• How many songs were sung by each singer?
• How many songs were not sung by Eminem?
• How many songs lasted at least 4 minutes?
• How many songs were longer than average?
• How many songs were sung by a singer whose last name begins with S?
• How many songs were sung by a singer whose last name contains six letters?
• How many songs were sung after June 15, 2005?
• How many songs were sung before the beginning of 2009?
• How many songs lasted exactly 4 minutes?

(more…)

Overview

• I need to run a sensitivity analysis that has many key inputs, such as year 1 sales, annual sales growth, year 1 price, and year 1 unit cost. Is there a way I can quickly vary these inputs and see the effect of the variation on the calculation of net present value, for example?

The Scenario Manager lets you change a group of input cells and see how various spreadsheet outputs change. Unfortunately, the Scenario Manager requires you to enter each scenario individually, which makes it difficult to create more than a few scenarios. For example, suppose you believe that four key inputs to our car net present value (NPV) model are year 1 sales, sales growth, year 1 price, and year 1 cost. (See the file NPVAudit.xls.) We’d like to see how NPV changes as these inputs change in the following ranges:

(more…)

Overview

• I’d like to create best, worst, and most-likely scenarios for the sales of an automobile by varying the values of year 1 sales, annual sales growth, and year 1 sales price. Data tables for sensitivity analysis allow me to vary only one or two inputs, so I can’t use a data table. Does Excel have a tool that allows me to vary more than two inputs in a sensitivity analysis?

The Scenario Manager enables you to perform sensitivity analysis by varyingas many as 32 input cells. With the Scenario Manager, you first define the set of input cells you want to vary. Next you name your scenario and enter for each scenario the value of each input cell. Finally, you select the output cells (also called result cells) that you want to track. The Scenario Manager then creates a beautiful report containing the inputs and the values of the output cells for each scenario.

(more…)

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:

(more…)

Overview

• I’m thinking of starting a store to sell gourmet lemonade in the local mall. Before opening the store, I’m curious about how my profit, revenue, and variable costs will depend on the price I charge and the unit variable cost.
• I am going to build a new house. The amount of money I need to borrow (with a 15-year repayment period) depends on the price I sell my current house for. I’m also unsure about the annual interest rate I’ll receive when I close. Can I determine how my monthly payments will depend on the amount borrowed and the annual interest rate?
• A major Internet company is thinking of purchasing another online retailer. The retailer’s current annual revenues are $100 million, with expenses of $150 million. Current projections indicate that the retailer’s revenues are growing at 25 percent per year and its expenses are growing at 5 percent per year. We know projections might be in error, however, and we would like to know, for a variety of assumptions about annual revenue and expense growth, the number of years before the retailer will show a profit.

(more…)

Overview

• I’ve just been handed a 5000-row spreadsheet that computes the net present value (NPV) for a new car. In the spreadsheet, my financial analyst made an assumption about the annual percentage of the growth in the product’s price. What cells in the spreadsheet are affected by this assumption?
• I think my financial analyst made an error in computing year 1 before-tax profit. What cells in the spreadsheet model were used for this calculation?
• How does the auditing tool work when I’m working with data in more than one worksheet or workbook?

(more…)

Overview

• How can I move the results of calculations (not the formulas) to a different part of a worksheet?
• I have a list of names in a single column. How can I make the list appear in one row instead of one column?
• I’ve downloaded U.S. Treasury bill interest rates from a Web site into

Excel. The data lists a 5 when the interest rate is 5 percent, 8 when the interest rate is 8 percent, and so on. How can I easily divide my results by 100 so that a 5 percent interest rate, for example, is listed as .05?

(more…)

Overview

• If I order up to 500 units of a product, I pay $3.00 per unit. If I order between 501 and 1200 units, I pay $2.70 per unit. If I order between 1201 and 2000 units, I pay $2.30 per unit. If I order more than 2000 units, I pay $2.00 per unit. How can I write a formula that expresses the purchase cost as a function of the number of units purchased?
• I’ve just purchased 100 shares of stock at a cost of $55 per share. To hedge the risk that the stock might decline in value, I purchased 60 six-month European put options. Each option has an exercise price of $45 and costs $5. How can I develop a spreadsheet that indicates the six-month percentage return on my portfolio for a variety of possible future prices?
• Many stock market analysts believe that moving-average trading rules can outperform the market. A commonly suggested movingaverage trading rule is to buy a stock when the stock’s price moves above the average of the last 15 months and to sell a stock when the stock’s price moves below the average of the last 15 months’ price. How would this trading rule have performed against buying and holding the Standard and Poor’s Index?
• In the game of craps, two dice are tossed. If the total of the dice on the first roll is 2, 3, or 12, you lose. If the total of the dice on the first roll is 7 or 11, you win. Otherwise, the game keeps going. How can I write a formula to determine the status of the game after the first roll?
• In most pro forma financial statements, cash is used as the plug to make assets and liabilities balance. I know that using debt as the plug would be more realistic. How can I set up a pro forma statement having debt as the plug?

(more…)

Overview

• I often get a circular reference message from Excel. Does this mean I’ve made an error?
• How can I resolve circular references?

When you receive a message from Excel that your workbook contains a circular reference, it means there is a ‘loop,’ or dependency, between two or more cells in a worksheet. For example, a circular reference occurs if the value in cell A1 influences the value in C2, the value in cell C2 influences the value in cell D2, and the value in cell D2 influences the value in cell A1. In the Following figure illustrates the pattern of a circular reference.

(more…)

Overview

• Should I pay $11,000 today for a copier or $3,000 a year for 5 years?
• If I invest $2,000 a year for 40 years toward my retirement and earn 8 percent a year on my investments, how much will I have when I retire?
• I am borrowing $10,000 on a 10-month loan with an annual interest rate of 8 percent. What will my monthly payments be? How much principal and interest am I paying each month?

When we borrow money to buy a car or a house, we always wonder whether we’re getting a good deal. When we save for retirement, we’re curious how large a nest egg we’ll have when we retire. In our daily work and personal life, financial questions similar to these questions often arise. Knowing how to use the PV, FV, PMT, PPMT, and IPMT functions in Excel makes answering these types of questions easy.

Should I pay $11,000 today for a copier or $3,000 a year for 5 years?

(more…)