Excel Problems: Calculations and Analysis

1 A local utility company needs to make a decision regarding which tire type to use for the truck it uses when servicing the local electric grid. The time period for the analysis is 6 years. The following assumptions will be used for the analysis: gasoline price of $3.00 per gallon, and 25,000 miles driven per year.

�

Two tire types are available for purchase (need to buy 4 tires for the truck). Type A, a lower quality tire, sells for $250 per tire, the tire is expected to last 50,000 miles, and result in average gasoline use for the truck of 20 miles per gallon. Type B, a higher quality tire, sells for $400 per tire, the tire is expected to last 75,000 miles, and result in an average gasoline use for the truck of 25 miles per gallon.

�

a. Calculate the annual savings in gasoline costs that would result from using Type B instead of Type A.

�

b. For the overall 6-year period, calculate the overall cost (include both the cost of gasoline and the cost of tires) for both tire types, and determine which tire type should be purchased. Write your answer in a text box inserted at the bottom of your Excel sheet.

�

�2 RC Sport produces portable soccer goals. Its total fixed cost for the following year is expected to be $490,000, while its variable cost per unit is expected to be $33 per soccer goal. Assume the selling price is $40 per soccer goal. (This question doesn�t require a text box with written answers, but you need to clearly label your answers to parts a and b.)

�

a. Calculate the contribution margin per unit (the contribution to profit from each soccer goal sold).

�

b. Calculate the number of soccer goals that RC Sport needs to sell to break even (to make zero profit).

�

c. Construct a table with Number of units sold, Total Revenue, Total Cost, and Profit in its 4 columns. Fill the units sold column with the following values: 0, 10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000, 90000, and 100000. Fill in the remaining 3 columns with formulas and cell references, calculating the correct values (use dollar signs to lock cell references and copy from the first row to the other rows of the table). Insert two Excel Charts (select the Line type), one showing the number of units (x) and profit (y), the other showing number of units (x), Total Revenue (y1), and Total Cost (y2). Use labels in your charts and make them look good for full credit!

Question 1

�

�

3 Assume that you want to buy a new car in 5 years, and that the price of the car will be $30,000. (Assume all cash flows occur at the end of the period throughout all of the Excel problems throughout our entire course.) Use Excel time value of money functions to solve the problems outlined below (one or more of these: PV, FV, RATE, NPER, PMT). (This question doesn�t require a text box with written answers, but you need to clearly label your answers to parts a, b, and c.)

�

a. How much do you need to deposit in an account today, if you want to have $30,000 in the account in 5 years, assuming the account earns 8% interest rate annually?

�

b. If you deposit $22,000 in the account today, what rate of interest would you need to earn annually in order to have exactly $30,000 in the account in 5 years?

�

c. Assuming your account earns 0.5% interest rate every month, and that you make an initial deposit of $10,000 today, how much do you need to deposit every month in your account in order to have exactly $30,000 in 5 years? (Hint: use the PMT variable to represent the monthly deposit; make sure you use the number of months for the number of periods.)

�

4 Assume that you are planning on retiring in 30 years, and that you decide to open a retirement account and deposit $5,000 in the account every year (as in the previous problem, use the end of the year convention). Once you retire, you start making annual withdrawals of $40,000 from the account (again, use the end of the year convention). Assuming the account is earning 6% rate of interest, how many years will it take, after you retire, before the funds in your account are completely exhausted? Please provide a written answer in a text box at the bottom of your Excel sheet. Use Excel time value of money functions to solve the problem. (Hint: this problem needs to be modeled as two parts.)

�

5 Assume that you are planning on purchasing a new car. You are considering financing the $40,000 purchase price using a car loan arranged through the car dealership. The terms of the loan are: 8 years of fixed monthly payments, and 2.4% quoted annual periodic rate of interest (this will need to be converted to a monthly rate by dividing the annual rate by 12). Assuming the loan will be completely paid off by the end of the 8 years, determine the monthly payment associated with loan, and then prepare an amortization table in a similar way as shown in the class Excel example. Include all of the months in your table, and make sure you show not only the beginning and ending balance for each month, but also the monthly payment and the breakdown between the part used to pay interest and the part used to pay down the principal. (Hint: use functions and formulas in the first row, and the beginning balance in the second row, in a way that will allow you to copy and paste to the remaining cells of your table)

Question 2

�

6 Triton Metals is considering an expansion of its existing operations, which would require a purchase of a new machine, MACHINE A. While the life of the expansion is indefinite, the machine has a useful life of 5 years and is expected to be replaced every 5 years with a new one of the same type. The incremental net cash flows from the expansion are listed in the table below. Assume 12% cost of capital. Please include written answers to all parts of the problem in a text box at the bottom of your Excel sheet.

a) Use the present worth method to determine whether the expansion should be undertaken or not. Use the NPV function. Explain why you determined to accept/reject the expansion.

�

b) Use the annual worth method to determine whether the expansion should be undertaken. Use the PMT function. Again, explain your answer.

�

c) Use the internal rate of return method to determine whether the expansion should be undertaken. Use the IRR function. Again, explain your answer.

�

d) Assume that an alternative machine model becomes available, MACHINE B, with the incremental net cash flows given below. Its useful life is 6 years, and it would be replaced every 6 years with a new machine of the same type, if purchased for the expansion project. Use the equivalent annual worth method to determine which of the two available machines should be selected for the project. Do not worry about accepting or rejecting the expansion itself, your task is only to determine which of the two machines should be used. Explain your answer.

7 BrickTon Cement Inc. is considering launching a new product. Using the information provided below, calculate the after-tax cash flows resulting from the new project for each year (0-8), and determine whether the new project should be accepted or rejected, and explain why.

�

A new machine will need to be purchased for $825,000. The project is expected to last 8 years, and the machine to be sold for $150,000 at the end of the 8 years of the project. The machine will be depreciated using a 10-year recovery period; you will need to use the depreciation percentages given in Table 7-3 in the textbook (the 10-year column) to compute annual depreciation for each of the 8 years
of the project.

�

Assume that in addition to the cost of the machine, the initial outlay will include a negative cash flow resulting from the increase in net working capital of $15,000 required to start the project. In each of the subsequent years the change in net working capital will be equal to 10% of the change in revenue in the following year (note that an increase in net working capital represents a negative cash flow, while a decrease in net working capital represents a positive cash flow). At the end of year 8, net working capitalwill return to the level that existed before the project was started.

�

Assume the project will result in additional 300 loads of cement sold each year during the first 4 years of the project (1-4), and 400 loads of cement sold each year during the second 4 years of the project (5-8). Each load of cement will sell for a price of $2,500 in year 1. The price is assumed to rise by 5% in each year that follows. Each load of cement costs $1,500 to produce during the entire 8 years (treat the revenues and costs as cash flows). Further assume 14% cost of capital, and 23% tax rate.