Check Figure: NPV $1,112 in favor of overhauling the old truck
REQUIREMENTS:
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:
You can use the following format illustrated below for your data block page.
Include the total cost approach analysis and the incremental analysis both on one spreadsheet page.
You can use 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.
You can use the following format for your incremental cost analysis:
USING EXCEL TO CALCULATE PRESENT VALUE (PV) LOGIC IF STATEMENT INSTRUCTIONS:
Present Value Function Instructions:
1. From the standard toolbar, select the button, fx. 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 fx 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.
U data block and cell referencing is required There is NO What IF part to this assignment. DATA BLOCK AND ANALYSES FORMATS: You can use the following format illustrated below for your data block page. Data Block Bilboa Freightlines Required Rate of Retum Present Truck New Year! TruckP eriods Purchase cost new Overhaul needed now Annual cash operating costs Salvage value now Salvage value five years from now Include the total cost approach analysis and the incremental analysis both on one Spreadsheet page you can use the format illustrated on the following page for your total cost approach analysis Biboa Freightlines, S.A. Total Cost Approach Net Present Value Calculation Amount of Year's Cash Flows Overhaul old truck Overhaul now Annual cash operating costs Salvage value in five years Net Present Value Item Purchase new truck Purchase cost new Salvage value of old truck now Annual cash operating costs Salvage value of new truck in five years Net Present Value Logo IF statement belongs her - Do not include a Factor column as is shown in the power point slides since excel calculate that for ou sm e where there would be a Non since no time D'Focus DIE Bilboa Freightlines, SA Incremental Cost Approach Net Present Value Calculation Amount of Year(s) Cash Flows Incremental investment - in favor of old truck Adu tiona annual cash operating costs Lost salvage value in five years Lost savage value for old truck now Looc statement belongs here crosoft Excel A207 Spring 2002 Excel 24 de Edt View Insert Format Tools Data Window X =PV(Data Block 183,813.. C13) Rate Datos Nper BT 91-02 12000 Returns the present ents who 12 the total amount that a series of future pas de Form When placing the cell reference into the FT area, make sure there is a negative sign before the reference, otherwise your output will have the wrong sign You will need to use the Pmt box when calculating the PV of an annuity and use the F box when calculating the PV of a single sum The box is for the time period of the inflow or outflow hodical D pro einen Instuctions l attau e sult use a Logical IF statement Instead of typing in Ner determine the correct result by Border Arrange Size A2. 30 DATA BLOCK AND ANALYSES FORMATS: You can use the following format illustrated below for your data block page. Data Block Biboa Freightlines Required Rate of Return Present Truck New Truck Year Periods Purchase cost new Overhaul needed now Annual cash operating costs Salvage value now Salvage value five years from now Include the total cost approach analysis and the incremental analysis both on one spreadsheet page You can use the format illustrated on the following page for your total cost approach analysis Size Bilboa Freightlines, S.A. Total Cost Approach Net Present Value Calculation Amount of Item Year(s) Cash Flows Overhaul old truck Overhaul now Annual cash operating costs Salvage value in five years Net Present Value Purchase new truck Purchase cost new Salvage value of old truck now Annual cash operating costs Salvage value of new truck in five years Net Present Value Logic IF statement belongs here - Do not include a "Factor column as is shown in the power point slides since excel dow % Picture Border E Position Wrap Text Bring Forward o Send Backward N E Le Group Selection Pane Rotate Crop Height 3.73 Width: 5.69 Size Shadow Effects Border Arrange Bilboa Freightlines, SA Incremental Cost Approach Net Present Value Calculation Amount of Year(s) Cash Flows Item Incremental investment - in favor of old truck Additional annual cash operating costs Lost salvage value in five years Lost salvage value for old truck now Logic IF statement belongs here GENERAL INFORMATION: Please be sure what you tum 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 stones about lost work! Back up your work on a disk award Backward Pane Rotate Width dow Effects Border CHICCO Arrange CHIES Size ! crosoft Excel A207 Spring 2002 Excel 14 de Ede Yen Insert Form Looks Reta Window Bebe EX V =PV(Data Block1B3.813.. C13) 120000 Return the present value of a vestment the total amount that a les of future payments north viste future or a cathb Le yo nan to state that payment made IZOLO 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. 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 The Nper box is for the time period of the inflow or outflow Logical in Statement Instructions 1, 5.A., of Panama, has a small truck that it uses for intracity deliveries. The truck is worn out and must be either overhauled or replaced with a new truck. The company has assembled the following information: Zaba650254%2F150X5Bdat. I Present Truck New Truck $21,000 $30,000 $ 11,500 Purchase cost (new) Remaining book value Overhaul needed now Annual cash operating costs Salvage value-now Salvage value-five years from now $7,000 $6,500 $10,000 $9.000 $1,000 $4,000 If the company keeps and overhauls its present delivery truck, then the truck will be usable for five more years. If a new truck is purchased, it will be used for five years, after which it will be traded in on another truck. The new truck would be diesel-operated. resulting in a substantial reduction in annual operating costs, as shown above, The company computes depreciation on a straight-line basis. All investment projects are evaluated using a 16% discount rate. Required: 1. What is the net present value of the "keep the old truck" alternative Round to the nearest whole dollar. 2. What is the net present value of the "purchase the new truck alternative Round to the nearest whole dollar. 3. Should Bilboa Freightlines keep the old tryck or purchase the new one? U data block and cell referencing is required There is NO What IF part to this assignment. DATA BLOCK AND ANALYSES FORMATS: You can use the following format illustrated below for your data block page. Data Block Bilboa Freightlines Required Rate of Retum Present Truck New Year! TruckP eriods Purchase cost new Overhaul needed now Annual cash operating costs Salvage value now Salvage value five years from now Include the total cost approach analysis and the incremental analysis both on one Spreadsheet page you can use the format illustrated on the following page for your total cost approach analysis Biboa Freightlines, S.A. Total Cost Approach Net Present Value Calculation Amount of Year's Cash Flows Overhaul old truck Overhaul now Annual cash operating costs Salvage value in five years Net Present Value Item Purchase new truck Purchase cost new Salvage value of old truck now Annual cash operating costs Salvage value of new truck in five years Net Present Value Logo IF statement belongs her - Do not include a Factor column as is shown in the power point slides since excel calculate that for ou sm e where there would be a Non since no time D'Focus DIE Bilboa Freightlines, SA Incremental Cost Approach Net Present Value Calculation Amount of Year(s) Cash Flows Incremental investment - in favor of old truck Adu tiona annual cash operating costs Lost salvage value in five years Lost savage value for old truck now Looc statement belongs here crosoft Excel A207 Spring 2002 Excel 24 de Edt View Insert Format Tools Data Window X =PV(Data Block 183,813.. C13) Rate Datos Nper BT 91-02 12000 Returns the present ents who 12 the total amount that a series of future pas de Form When placing the cell reference into the FT area, make sure there is a negative sign before the reference, otherwise your output will have the wrong sign You will need to use the Pmt box when calculating the PV of an annuity and use the F box when calculating the PV of a single sum The box is for the time period of the inflow or outflow hodical D pro einen Instuctions l attau e sult use a Logical IF statement Instead of typing in Ner determine the correct result by Border Arrange Size A2. 30 DATA BLOCK AND ANALYSES FORMATS: You can use the following format illustrated below for your data block page. Data Block Biboa Freightlines Required Rate of Return Present Truck New Truck Year Periods Purchase cost new Overhaul needed now Annual cash operating costs Salvage value now Salvage value five years from now Include the total cost approach analysis and the incremental analysis both on one spreadsheet page You can use the format illustrated on the following page for your total cost approach analysis Size Bilboa Freightlines, S.A. Total Cost Approach Net Present Value Calculation Amount of Item Year(s) Cash Flows Overhaul old truck Overhaul now Annual cash operating costs Salvage value in five years Net Present Value Purchase new truck Purchase cost new Salvage value of old truck now Annual cash operating costs Salvage value of new truck in five years Net Present Value Logic IF statement belongs here - Do not include a "Factor column as is shown in the power point slides since excel dow % Picture Border E Position Wrap Text Bring Forward o Send Backward N E Le Group Selection Pane Rotate Crop Height 3.73 Width: 5.69 Size Shadow Effects Border Arrange Bilboa Freightlines, SA Incremental Cost Approach Net Present Value Calculation Amount of Year(s) Cash Flows Item Incremental investment - in favor of old truck Additional annual cash operating costs Lost salvage value in five years Lost salvage value for old truck now Logic IF statement belongs here GENERAL INFORMATION: Please be sure what you tum 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 stones about lost work! Back up your work on a disk award Backward Pane Rotate Width dow Effects Border CHICCO Arrange CHIES Size ! crosoft Excel A207 Spring 2002 Excel 14 de Ede Yen Insert Form Looks Reta Window Bebe EX V =PV(Data Block1B3.813.. C13) 120000 Return the present value of a vestment the total amount that a les of future payments north viste future or a cathb Le yo nan to state that payment made IZOLO 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. 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 The Nper box is for the time period of the inflow or outflow Logical in Statement Instructions 1, 5.A., of Panama, has a small truck that it uses for intracity deliveries. The truck is worn out and must be either overhauled or replaced with a new truck. The company has assembled the following information: Zaba650254%2F150X5Bdat. I Present Truck New Truck $21,000 $30,000 $ 11,500 Purchase cost (new) Remaining book value Overhaul needed now Annual cash operating costs Salvage value-now Salvage value-five years from now $7,000 $6,500 $10,000 $9.000 $1,000 $4,000 If the company keeps and overhauls its present delivery truck, then the truck will be usable for five more years. If a new truck is purchased, it will be used for five years, after which it will be traded in on another truck. The new truck would be diesel-operated. resulting in a substantial reduction in annual operating costs, as shown above, The company computes depreciation on a straight-line basis. All investment projects are evaluated using a 16% discount rate. Required: 1. What is the net present value of the "keep the old truck" alternative Round to the nearest whole dollar. 2. What is the net present value of the "purchase the new truck alternative Round to the nearest whole dollar. 3. Should Bilboa Freightlines keep the old tryck or purchase the new one
