Excel 4 Ur Business

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:

Read the rest of this post »

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.

Read the rest of this post »

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:

Read the rest of this post »

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.

Read the rest of this post »

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?

Read the rest of this post »

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?

Read the rest of this post »

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?

Read the rest of this post »

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.

Read the rest of this post »

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?

Read the rest of this post »

Overview

• What is internal rate of return (IRR)?
• How can I find the IRR of cash flows?
• Does a project always have a unique IRR?
• Are there conditions that guarantee a project will have a unique IRR?
• If two projects both have a single IRR, how do I use the projects’ IRRs?
• How can I find the IRR of irregularly spaced cash flows?

Read the rest of this post »