Organizational Finance
Fact Situation: You are the new CFO of ABC Company and you have been asked by the President to analyze the companys performance regarding the success or failure of capital projects. Your senior accountant gives you a spreadsheet entitled ABC company summary of capital projects (see worksheet for details) based on post completion reviews of the various projects over a five (5) year period. Your preliminary analysis is that the company has had mixed success in implementing capital projects, but you wish to perform more analysis. You decide that analyzing the projects by major category might yield insights into how the projects performed. You therefore analyze the projects by major category to determine project performance.
Required: Perform a preliminary analysis on the capital projects which indicates how the actual project results performed relative to the expected returns and compute the mean and standard deviation for the projects, along with a graphical representation of the results (this could be a line graph, bar graph or bell curve). Next, sort the projects by major category and determine actual versus expected results for each category. Finally, make a recommendation to the President about whether the projects should be examined in total or broken down by category, giving reasons for your decision. Also, provide the President with an assessment about whether or not the capital program is working, as well as any changes which might improve capital project performance. The following items should be addressed in the assessment: 1. Why is it necessary to rank projects by type? 2. What type of risks are associated with capital budgeting? 3. Why is it necessary to perform post completion reviews for each project? 4. The development of NPV, IRR and payback for projects that will be included in the budget is a great deal of work. Why should this work be done?
So that,
B D E F G H 1 ABC Company 2 Summary of Capital Projects for Five (5) Year Period 3 4 Year Project Category Project Useful Original Cost Life Discounted Payback Years Discounted Payback CER Discounted Actual Payback Discounted Actual Return Change from Expected Return 4.00 999 3.60 3.57 4.29 10 2006 RE 2006 RE 2006 RE 2007 NE 2007 NP 2008 NP 2008 NE 2008 NP 2008 NE 2008 NE 5 5 5 Description 6 7 8 9 Replacement Printing Press 10 Replacement Die Cutting Machine 11 Replacement Drill Press 12 Automation Equipment 13 New Product Launch 1 14 New Product Launch 2 15 New Sortation Equipment 16 New Product Launch 3 17 New Cutting Machine 18 New Die Stamping Machine 19 20 Grand Total 21 22 Standard Deviation Entire Population 23 24 Project Key: 25 NE = New Equipment 26 NP = New Product 27 RE = Replacement Equipment 28 5,000,000 450,000 1,250,000 7,500,000 15,000,000 5,500,000 1,750,000 10,000,000 1,500,000 6,500,000 5.00 3.14 1,250,000 125,000 350,000 1,750,000 3,000,000 1,750,000 750,000 2,500,000 550,000 1,350,000 1,200,000 125,000 400,000 1,650,000 2,975,000 1,800,000 825,000 2,400,000 575,000 1,300,000 24.00% 27.78% 32.00% 22.00% 19.83% 32.73% 47.14% 24.00% 38.33% 20.00% 2.33 4.00 299 2.73 4.81 54,450,000 4.07 13,375,000 13,250,000 24.33% C D E F . J K L M 1 2 3 4 4 5 6 7 8 9 9 10 Total 0.00 100,000.00 110,000.00 210,000.00 35.00% 35,000.00 0.00 100,000.00 110,000.00 210,000.00 35.00% 35,000.00 0.00 100,000.00 110,000.00 210,000.00 35.00% 35,000.00 0.00 100,000.00 110,000.00 210,000.00 35.00% 35,000.00 0.00 100,000.00 110,000.00 210,000.00 35.00% 35,000.00 0.00 0.00 0.00 0.00 35.00% 0.00 0.00 0.00 0.00 0.00 35.00% 0.00 0.00 0.00 0.00 0.00 35.00% 0.00 0.00 0.00 0.00 0.00 35.00% 0.00 0.00 (550,000.00) 0.00 600,000.00 0.00 550,000.00 0.00 1,050,000.00 35.00% 0.03 0.00 175,000.00 175,000.00 175,000.00 175,000.00 175,000.00 175,000.00 0.00 0.00 0.00 0.00 0.00 325,000.00 B 1 Capital Project Evaluation 2 || 3 ABC Manufacturing Company 4 5 Year 0 0 6 7 Description 8 9 Cost of Machine (550,000.00) 10 Incremental Operating Income/Expens 100,000.00 11 Straight Line Depreciation 5yrs 0.00 12 Total Cash Flow Before Tax 0.00 13 Tax Rate 35% 0.35% 14 Taxes 0.00 15 16 Total Incremental Cash Flow (550,000.00) 17 18 Weighted Average cost of Capital 10.50% 19 20 Discounted Cash Flow (550,000.00) 21 22 Net Present Value 105,000.19 23 24 25 Check: NPV Excel : 105,000.19 26 27 Simple Payback Years 3.1429 28 29 Discounted Payback Years 4.1985 30 31 Internal Rate of Return 17.78% 32 33 Discounted Benefit/Cost Ratio 19.09% 34 35 Present Value Weighted Average Cost 36 of Capital 37 158,371.04 143,322.21 129,703.36 117,378.60 106,224.98 0.00 0.00 0.00 0.00 0.00 105,000.19 B D E F G H 1 ABC Company 2 Summary of Capital Projects for Five (5) Year Period 3 4 Year Project Category Project Useful Original Cost Life Discounted Payback Years Discounted Payback CER Discounted Actual Payback Discounted Actual Return Change from Expected Return 4.00 999 3.60 3.57 4.29 10 2006 RE 2006 RE 2006 RE 2007 NE 2007 NP 2008 NP 2008 NE 2008 NP 2008 NE 2008 NE 5 5 5 Description 6 7 8 9 Replacement Printing Press 10 Replacement Die Cutting Machine 11 Replacement Drill Press 12 Automation Equipment 13 New Product Launch 1 14 New Product Launch 2 15 New Sortation Equipment 16 New Product Launch 3 17 New Cutting Machine 18 New Die Stamping Machine 19 20 Grand Total 21 22 Standard Deviation Entire Population 23 24 Project Key: 25 NE = New Equipment 26 NP = New Product 27 RE = Replacement Equipment 28 5,000,000 450,000 1,250,000 7,500,000 15,000,000 5,500,000 1,750,000 10,000,000 1,500,000 6,500,000 5.00 3.14 1,250,000 125,000 350,000 1,750,000 3,000,000 1,750,000 750,000 2,500,000 550,000 1,350,000 1,200,000 125,000 400,000 1,650,000 2,975,000 1,800,000 825,000 2,400,000 575,000 1,300,000 24.00% 27.78% 32.00% 22.00% 19.83% 32.73% 47.14% 24.00% 38.33% 20.00% 2.33 4.00 299 2.73 4.81 54,450,000 4.07 13,375,000 13,250,000 24.33% C D E F . J K L M 1 2 3 4 4 5 6 7 8 9 9 10 Total 0.00 100,000.00 110,000.00 210,000.00 35.00% 35,000.00 0.00 100,000.00 110,000.00 210,000.00 35.00% 35,000.00 0.00 100,000.00 110,000.00 210,000.00 35.00% 35,000.00 0.00 100,000.00 110,000.00 210,000.00 35.00% 35,000.00 0.00 100,000.00 110,000.00 210,000.00 35.00% 35,000.00 0.00 0.00 0.00 0.00 35.00% 0.00 0.00 0.00 0.00 0.00 35.00% 0.00 0.00 0.00 0.00 0.00 35.00% 0.00 0.00 0.00 0.00 0.00 35.00% 0.00 0.00 (550,000.00) 0.00 600,000.00 0.00 550,000.00 0.00 1,050,000.00 35.00% 0.03 0.00 175,000.00 175,000.00 175,000.00 175,000.00 175,000.00 175,000.00 0.00 0.00 0.00 0.00 0.00 325,000.00 B 1 Capital Project Evaluation 2 || 3 ABC Manufacturing Company 4 5 Year 0 0 6 7 Description 8 9 Cost of Machine (550,000.00) 10 Incremental Operating Income/Expens 100,000.00 11 Straight Line Depreciation 5yrs 0.00 12 Total Cash Flow Before Tax 0.00 13 Tax Rate 35% 0.35% 14 Taxes 0.00 15 16 Total Incremental Cash Flow (550,000.00) 17 18 Weighted Average cost of Capital 10.50% 19 20 Discounted Cash Flow (550,000.00) 21 22 Net Present Value 105,000.19 23 24 25 Check: NPV Excel : 105,000.19 26 27 Simple Payback Years 3.1429 28 29 Discounted Payback Years 4.1985 30 31 Internal Rate of Return 17.78% 32 33 Discounted Benefit/Cost Ratio 19.09% 34 35 Present Value Weighted Average Cost 36 of Capital 37 158,371.04 143,322.21 129,703.36 117,378.60 106,224.98 0.00 0.00 0.00 0.00 0.00 105,000.19
