Introduction:-
Capital Budgeting is used by organisations to evaluate a project in hand i.e. whether to proceed with it or leave it. There are various methods or techniques of capital budgeting. The most popular are
To determine:-
Here, in the given problem we are supposed to calculate NPV based on the inputs given like sales, cost of goods sold, equipment cost, investment in
Answer to Problem 1AE
Solution:-
Net Present Value is a Capital Budgeting technique which is used by management to decide as to whether proceed with a given project or not. It is calculated by discounting all the
When we solve the problem, the NPV of purchasing the equipment @14% is $31, 493 and NPV @10% is $56, 517 and as can be seen that NPV has increased when the discount rate is decreased.
Explanation of Solution
Explanation:-
Here, following key inputs given are as follows:-
Purchase of Equipment | 60000 |
Investment in working capital | 100000 |
Sales | 200000 |
Cost of goods sold | 125000 |
Out of pocket operating costs | 35000 |
Overhaul of equipment | 5000 |
Salvage value of equipment | 10000 |
Now, calculating NPV at 14% and 10% discount rate At 14 % discount rate
Years | ||||||
Now | 1 | 2 | 3 | 4 | 5 | |
Purchase of Equipment | -60000 | |||||
Investment in working capital | -100000 | |||||
Sales | 200000 | 200000 | 200000 | 200000 | 200000 | |
Cost of goods sold | 125000 | 125000 | 125000 | 125000 | 125000 | |
Out of pocket operating costs | 35000 | 35000 | 35000 | 35000 | 35000 | |
Overhaul of equipment | 5000 | |||||
Salvage value of equipment | 10000 | |||||
Working Capital Released | 100000 | |||||
Total Cash flows (a) | -160000 | 40000 | 40000 | 40000 | 35000 | 150000 |
Discount factor @14% (b) | 1 | 0.87719 | 0.76947 | 0.67497 | 0.59208 | 0.51937 |
Present value of cash flows (a x b) | -160000 | 35087.7 | 30778.7 | 26998.9 | 20722.8 | 77905.3 |
Net Present Value | 31493.4 |
At 10% discount rate
Years | ||||||
Now | 1 | 2 | 3 | 4 | 5 | |
Purchase of Equipment | 60000 | |||||
Investment in working capital | 100000 | |||||
Sales | 200000 | 200000 | 200000 | 200000 | 200000 | |
Cost of goods sold | 125000 | 125000 | 125000 | 125000 | 125000 | |
Out of pocket operating costs | 35000 | 35000 | 35000 | 35000 | 35000 | |
Overhaul of equipment | 5000 | |||||
Salvage value of equipment | 10000 | |||||
Working Capital Released | 100000 | |||||
Total Cash flows (a) | -160000 | 40000 | 40000 | 40000 | 35000 | 150000 |
Discount factor @10% (b) | 1 | 0.90909 | 0.82645 | 0.75131 | 0.68301 | 0.62092 |
Present value of cash flows (a x b) | -160000 | 36363.6 | 33057.9 | 30052.6 | 23905.5 | 93138.2 |
Net Present Value | 56517.7 |
Now, we are asked to explain as to why the NPV has increased from $31493 to $56517 when the discount rate is reduced from 14% to 10%. Actually, with the decrease in the discount rate the Present Value factor increases and when we multiply that increased factor with the net cash flows, the resultant present value of the cash flows increases and ultimately the NPV figure.
Now, in the second query, the company is considering to purchase new equipment
Years | ||||||
Now | 1 | 2 | 3 | 4 | 5 | |
Purchase of Equipment | 120000 | |||||
Investment in working capital | 80000 | |||||
Sales | 255000 | 255000 | 255000 | 255000 | 255000 | |
Cost of goods sold | 160000 | 160000 | 160000 | 160000 | 160000 | |
Out of pocket operating costs | 50000 | 50000 | 50000 | 50000 | 50000 | |
Overhaul of equipment | 40000 | |||||
Salvage value of equipment | 20000 | |||||
Working Capital Released | 80000 | |||||
Total Cash flows (a) | -200000 | 45000 | 45000 | 45000 | 5000 | 145000 |
Discount factor @14% (b) | 1 | 0.87719 | 0.76947 | 0.67497 | 0.59208 | 0.51937 |
Present value of cash flows (a x b) | -200000 | 39473.7 | 34626 | 30373.7 | 2960.4 | 75308.5 |
Net Present Value @ 14% | -17258 |
Therefore, NPV of the project is - $17, 258 Now, we will calculate the NPV at various discount rates i.e.13%, 12%, 11% and 10% and determine on which discount rate NPV turns profitable from the negative figure.
Total Cash flows (a) | -200000 | 45000 | 45000 | 45000 | 5000 | 145000 |
Discount factor @13% (b) | 1 | 0.88496 | 0.78315 | 0.69305 | 0.61332 | 0.54276 |
Present value of cash flows (a x b) | -200000 | 39823 | 35241.6 | 31187.3 | 3066.59 | 78700.2 |
Net Present Value @ 13% | -11981 | |||||
Total Cash flows (a) | -200000 | 45000 | 45000 | 45000 | 5000 | 145000 |
Discount factor @12% (b) | 1 | 0.89286 | 0.79719 | 0.71178 | 0.63552 | 0.56743 |
Present value of cash flows (a x b) | -200000 | 40178.6 | 35873.7 | 32030.1 | 3177.59 | 82276.9 |
Net Present Value @ 12% | -6463.1 | |||||
Total Cash flows (a) | -200000 | 45000 | 45000 | 45000 | 5000 | 145000 |
Discount factor @11% (b) | 1 | 0.9009 | 0.81162 | 0.73119 | 0.65873 | 0.59345 |
Present value of cash flows (a x b) | -200000 | 40540.5 | 36523 | 32903.6 | 3293.65 | 86050.4 |
Net Present Value @ 11% | -688.74 | |||||
Total Cash flows (a) | -200000 | 45000 | 45000 | 45000 | 5000 | 145000 |
Discount factor @10% (b) | 1 | 0.90909 | 0.82645 | 0.75131 | 0.68301 | 0.62092 |
Present value of cash flows (a x b) | -200000 | 40909.1 | 37190.1 | 33809.2 | 3415.07 | 90033.6 |
Net Present Value @ 10% | 5357 |
As we can see that the NPV has turned positive from 14% discount rate to 10% discount rate.
Now, we are asked to determine the IRR of the project. As we can see above that NPV has turned positive at 10% discount rate, therefore the IRR of the project should lie between 10% and 11% discount rates i.e. at a place where NPV is zero. Actually, if calculated IRR is 10.88%. Now, we are asked to determine the salvage value of the equipment so that the NPV becomes positive, assuming that the amount given in the question is not given If we see in the initial table of discount rate of 14%, the NPV is -$17258 taking into account the salvage value of $20000 in the fifth year. Now, if we increase the salvage value, then only we will get the positive NPV.
Without taking into account the salvage value, then NPV is -$27645. Now dividing it by the PVIF @ 14% which is .51937, we will get the minimum salvage value of the equipment to turn NPV into positive figure.
=27645÷.51937
=$53,229
Therefore, the minimum salvage value should be $53229 to turn NPV into positive figure.
Conclusion:-
Since, the resultant net present value is positive in problem 1, therefore it is viable to proceed with the given project.
However, NPV is negative in project 2, therefore it is not viable to purchase the equipment.
Want to see more full solutions like this?
Chapter 12 Solutions
Introduction To Managerial Accounting
- Use the template attached below to complete this activity: (Videos are available in the C4 module on using the ebook + copying worksheets). Chapter_7_Applying_Excel_template-1.xlsx Use the data found in the template + use the additional data found in the ebook section, "Applying Excel" (p. 330 print text). All calculations should be performed within the cell using cell referencing/formulas so that I can see how you arrived at your answers! Do not use your calculator and just type in numbers! You will submit four worksheets within your assignment file: sheet 1 = original sheet 2 = requirement 1 quantitative + question responses sheet 3 = requirement 2 quantitative + question responses sheet 4 = requirement 3 quantitative + question responses In scenario 1, 2, and 3.... copy the original worksheet after inputting your cell formulas and change the values as indicated for each requirement. Name each tab by the question #. Chapter 7: Applying Excel Data…arrow_forwardCan someone set up an excel sheet using the following data and show me the formula side, so I know how to replicate this type of problem later on? I don't understand where we get the values from the solution excel sheet I attached in the images. Thanks in advance!arrow_forwardCan you show me how to calculate the NPER without using Excel?arrow_forward
- Use cells A6 to C17 from the given information to complete this questions. You must use the built-in excel function to answer this question. Don't worry about the numbers that are answered, they are all correct! Just help me figure out the remaining four please!arrow_forwardSolve this question properly by making a table, calculating all the required values in detail (without using MS-Excel)arrow_forwardYou are provided with the following information. Please give me the answers in an excel format.arrow_forward
- Please show your computations through excel format. Using FIFO, compute for the cost of WIP, end.arrow_forwardRequired information (The Excel worksheet form that appears below is to be used to recreate part of the example relating to Turbo Crafters that appears earlier in the chapter.) Download the Applying Excel form and enter formulas in all cells that contain question marks. For example, in cell B13 enter the formula "= B5". After entering formulas in all of the cells that contained question marks, verify that the dollar amounts match the example in the text. Check your worksheet by changing the estimated total amount of the allocation base in the Data area to 50,000 machine-hours, keeping all of the other data the same as in the original example. If your worksheet is operating properly, the predetermined overhead rate should now be $6.00 per machine-hour. If you do not get this answer, find the errors in your worksheet and correct them. Save your completed Applying Excel form to your computer and then upload it here by clicking "Browse." Next, click "Save." You will use this…arrow_forwardPlease answer this and kindly show the solution/computation and final answers using microsoft excel. I'll give you a like. Thank you!arrow_forward
- Can you show me using excel calculations please?arrow_forwardWork the problem out by hand on a piece of paper so that you know what the answers should be. All dollar amounts (except EPS) should end in ",000" The schedule and statment in your output should compute mathematically from TOP to Bottom, so check and make sure that's the case Math Example: If A - B = C, then A - C = B and A = B + C Prepare an output section that produces the following items: 1) "COGS Schedule", 2) "Income Statement", and 3) "Retained Earning Statement". The reporting period is for the calendar year of 2023. The output items should be placed on a separate 'sheet' (the heading "... OUTPUT SECTION ..." should be centered over all columns to which it relates: [A1..G1]). Naming of the output 'sheet' should be "OUTPUT". No number (dollar amount, shares, or percentage) or company name should be typed (hard coded) directly into any cell in the output section, as this would prevent your output from being correct when the input is changed. Instead, the output section must…arrow_forwardThe question is how do I make the Excel Tables correctly before trying to plot the remaining units and get by solving those in the spreadsheet?arrow_forward
- Excel Applications for Accounting PrinciplesAccountingISBN:9781111581565Author:Gaylord N. SmithPublisher:Cengage Learning