EXCEL ASSIGNMENT #2

Check Figure: NPV $1,112 in favor of overhauling the old truck

REQUIREMENTS:

• Complete the requirements for P12-28 on page 603 of your textbook.
• Do the Total Cost Approach first then, do an Incremental Cost Approach analysis below your Total Cost Approach. They should both be on the same page.
• REQUIRED COMPONETS:
  • Two Logic IF statements MUST be used to label the Decision as being in favor of overhauling the old truck or in favor of purchasing the new truck. One Logic IF for the Total Cost Approach and one Logic IF for the Incremental Cost Approach. This is a required component for this assignment. If you do not complete both Logic IF stmts, you will not receive credit for this assignment.
  • Excels PV function must be used to calculate the present value of the cash flows. Do not use the factor tables as is illustrated in the text.
  • A data block and cell referencing is required.
• There is NO What IF part to this assignment.

DATA BLOCK AND ANALYSES FORMATS:

• An Excel #2 template has been posted also with the following format illustrated below for your data block page and the analysis report.

• Include the total cost approach analysis and the incremental analysis both on one spreadsheet page.

• The template posted for Excel #2 uses the format illustrated on the following page for your total cost approach analysis:

• Do not include a Factor column as is shown in the power point slides since excel will calculate that for you.
• Under the year(s) column, use 0 where there would be a Now, since no time has passed from now. Be sure to input the year(s) or period(s) in your data block so you can cell reference them to your spreadsheet formulas.
• Under the year(s) or periods column, for the annuity (annual cash operating costs), use the total number of years for the annuity instead of the range. For example, instead of showing the range 1-5, you will use 5 in your spreadsheet.
• To calculate present value, use excel formula function (PV) and NOT the tables in the textbook. See below for instructions on how to use the PV function in excel.
• Use borders for your column headings and data underlines. Double underlines for Net Present Value solution.
• The Excel #2 template posted uses the following format for your incremental cost analysis:

GENERAL INFORMATION:

Please be sure what you turn in is a unique product. You may work together, but you must each do your own spreadsheet. Do NOT turn in duplicate spreadsheets. We will assume you cheated and you both (or all) will get a zero for the assignment.

Save your work frequently! Do not be the next person telling horror stories about lost work! Back up your work on a disk.

SAVING YOUR FILE:

Save the original file according to the following name format:

Original data file: (Your Last Name, First Name Initial) Excel#2.

For Example: SmithJExcel2.xls or SmithJExcel2.xlsx (depending on which version of Microsoft you are using).

USING EXCEL TO CALCULATE PRESENT VALUE (PV) LOGIC IF STATEMENT INSTRUCTIONS:

Present Value Function Instructions:

1. From the standard toolbar, select the button, f_x. This button will bring up a box called Insert Function.
2. Under function category, select Financial.
3. Under function name, find and select PV.
4. After completing step 2 & 3, select ok to bring up the box for the PV function.
5. In the PV function box, enter in the relevant information using cell references. For example, for the rate, put your cursor in the rate area, go to the data block, click on the rate cell, and then click on the next area that information is needed. Some of the areas will have no cell references if it is not relevant to the calculation. When all relevant information is entered, click ok. In the example below, the cell references will be different from your cell references.

6. When placing the cell reference into the Fv area, make sure there is a negative (-) sign before the reference, otherwise your output will have the wrong sign.
7. You will need to use the Pmt box when calculating the PV of an annuity and you will use the Fv box when calculating the PV of a single sum.
8. The Nper box is for the time period of the inflow or outflow.

Logical If Statement Instructions:

To properly label your NPV result, use a Logical IF statement. Instead of typing in Net Present Value to the left of the calculated NPV have excel determine the correct result by using a Logic IF statement instead.

Remember, the general formula for a Logic IF statement is: = If (condition, true, false)

You must tell Excel what the condition is, what to do if that condition is true, and what to do if that condition is false. Here, when doing the total cost approach, the condition is: if the result is = or > than 0, then have excel type in NPV in favor of overhauling the old truck (this is if the condition is true), otherwise have excel type in NPV in favor of buying the new truck (this is if the condition is false).

An easy way to have Excel help you create this formula is to use the formula wizard. Click on the f_x button beside the editing toolbar. Then use the arrow key to locate more formula options (sum should be the default). Choose IF. You will be presented with a box that asks for the necessary data. You can also modify the formula to include multiple results for multiple criteria.