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:

(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…)

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?

(more…)

Overview

• What is net present value (NPV)?
• How do I use NPV to compare the merits of investments for which cash flows are received at several points in time?
• How do I use the Excel NPV function?
• How can I compute NPV when cash flows are received at the beginning of a year or in the middle of the year?
• How can I compute NPV when cash flows are received at irregular intervals?

Consider the following two investments, whose cash flows are listed in the file NPVExample.xls and shown in the following figure.

(more…)

Overview

• When I enter dates into Excel, I often see a number such as 37625 rather than a date such as 1/4/2003. What does this number mean, and how do I change 37625 to a normal date?
• Can I enter a formula that automatically displays today’s date?
• How do I determine a date that is 50 workdays after another date? What if I want to exclude holidays?
• How do I determine the number of workdays between two dates?
• I have 500 different dates entered in Excel. What formulas can I use to extract the month, year, day of the month, and day of the week from each date?

(more…)