Capital Project Justification It is now time to prepare a capital budget. The idea is to prepare a list of acceptable projects in which to spend in the first quarter in order to increase the value of operations. Recall that the cash budget included funds for plant expansion. The estimated future cash flows arising from sales, net of operating costs are given. The hurdle rate represents the interest rate to borrow these funds plus a 2% risk factor. Use this percent in order to discount the cash flows. The project cost, and the salvage value as well, have already been input into the spreadsheet. 1. Input the positive cash flows due to production cost savings. Sales revenues: Years 1-5 $135,000 per year; Years 610 $200,000 per year Operating costs: Years 1-5 $95,000 per year; Years 610 $135,000 per year 2. Decide on the best year (57) to spend the $85,000 of major maintenance. Complete the five measures at the bottom of the spreadsheet. 3. Using Excel functions, given a hurdle rate calculate the project's Net Present Value (=NPV) and the Internal Rate of Return (=IRR). 4. Calculate the payback period and profitability index. Provide a recommendation as to whether this project is acceptable. . Performance Evaluation The data for the February master budget columns should come over from the operating budget worksheet. Verify that the master budge Net Income is the same as that reported in Sheet #1. Complete a flexible budget for February, showing what net income should have been using the operating budget revenue rate, variable expenses' rates, and fixed costs. 1. Given actual results and the operating budgeted rates, prepare a flexible budget for 1 month. Actual operating results for the month are provided. DO NOT CHANGE THIS DATA. 2. Calculate the variances between the flexible budget and actual results, as being either F for favorable or U for unfavorable. Determine how much of the Net Income variance was due to volume and how much was rate-related. Explain deviations from plan. How would you evaluate the actual results? What steps would you take to further investigate and possibly adjust your budget for the rest of the year? Incremental Analysis: Do We Outsource? 1. Given the relevant costs from the operating budget, prepare a worksheet comparing the relevant data to a vendor's price quote for doing the production currently done in-house. 2. Given avoidable costs, calculate whether the outsourcing decision will save costs in total. Provide an opinion as to whether this business deal is acceptable. Are there any nonfinancial considerations? 3 4 Jan 40000 40,000 9.50 $ 380,000 $ Feb 42500 42,500 9.50 $ 403,750 $ April 45000 45,000 9.50 427,500 $ Total Qtr 127500 127,500 Average Sales Price/unit (A) wgtd. % Sales average 10.50 50% $ 5.25 8.50 50% $ 4.25 100% $ 9.50 Sales Price (all 2 topping): Large $ Medium $ 1,211,250 (A) Input the planned product mix (B) Input the forecast sale volume (c) Input the number of pizza made per hour (D) Input the sales commission as a % of reven (E) Input the sales manager's salary (F) Input the office manager's salary (G) Calculate the breakeven point in sales units (H) Calculate the sales units needed to reach a $200,000 net income target, 5.55 $ 15 12.00 5.55 $ 0.10 5.65 $ 154,200 $ 3.86 S 40.6% 0.10 5.65 $ 163,838 $ 3.86 $ 40.6% 5.55 0.10 5.65 173,475 $ 3.86 40.6% Direct Labor: (c) efficiency rate as minutes per pizza hourly labor cost plus fringe benefits $ Total direct labor per pizza $ 719,738 491,513 5 Sales : 6 Units (B) 7 Price/unit $ 8 Total Sales Revenue $ 9 10 Variable Costs (stated as per unit) 11 Production $ 12 Selling 13 Total Variable Costs per unit $ 14 Contribution Margin $ 15 CM per unit $ 16 CM Ratio 17 Fixed Costs 18 Selling (E) $ 19 Administration (F) 20 Total Fixed Costs $ 21 22 Net Income $ 23 24 Breakeven Point in sales units 25 26 Sales units for a Target Profit of $200,000 27 20 3.00 0.25 50,000 $ 60,000 110,000 $ 52,500 $ 63,000 115,500 $ 55,125 $ 66,150 121,275 $ 157,625 189,150 346,775 Direct Materials: Dough $ cheese toppings caffinated tomato sauce Boxes Total direct material per pizza $ 0.50 0.75 1.00 0.05 2.55 44,200 $ 48,338 $ 52,200 $ 144,738 28,534 (G) Sales comissions (D) Total Variable Selling Expenses per pizza $ 1% 0.10 80,415 (H) 380,000 $ 403,750 $ 427,500 $ 1,211,250 45,000 $ 120,000 95,000 260,000 $ 30,000 $ 228,000 100,938 358,938 $ 57,000 242,250 106,875 406,125 $ 132,000 590,250 302,813 1,025,063 (A) Calculate, based on the collection history, the cash receipts from customers (B) Calculate the cash disbursements for raw material purchases, assuming 1/2 of the previous month's purchases are paid in the current month Total Purchases December January February March $ 53,684 $ 102,000 $ 108,375 $ 114,750 77,842 53,800 60,000 191,642 $ 68,358 $ 3 Cash Receipts 4 Sales Revenues $ 300,000 $ 200,000 $ 5 Cash Receipts from: 6 2 months ago (10%) $ 7 1 month ago (60% (A) 8 current month (25%) 9 total cash receipts $ 10 Cash Disbursements 11 Raw Materials (B) 12 Selling Expenses 13 Administrative Expenses 14 total disbursements $ 15 Net Operating Cash $ 16 17 Investments: 18 Expand Business (C) 19 20 Financing: 21 New Debt (D) 22 Repay debt 23 24 Net Cash Flow $ 25 Beginning Cash Balance 26 Ending Cash Balance $ 27 28 THE MINIMUM CASH BALANCE IS $ 75.000 105,188 56,538 63,000 224,725 $ 134,213 $ 111,563 59,400 66,150 237,113 $ 169,013 $ 294,592 169,738 189,150 653,480 (c) Decide in which month you will make a capital investment of $300,000 (D) Determine the appropriate financing activities so as to keep at least the required minimum cash balance of $ 75,000 and payoff any amount borrowed. 371,583 (300,000) (300,000) 222,430 222,430 (222,430) (222,430) 68,358 $ 75,000 143,358 $ 56,643 $ 143,358 200,000 $ (53,418) $ 200,000 146,583 $ 71,583 75,000 146,583 Flex Budget February Variances (flex. vs. actual) Total per Unit per Unit per Unit (B) (F) $ (A) Verify that the net income is the same as the Op Bud (B) Input the flex budget sales units and the per unit pr (C) Input the flex budget production cost per unit and ca the total flex production costs. (D) Input the flex budget variable selling costs per unit (E) Input the flex budget fixed selling and administratior (F) Calculate the variances between the actual and flex! (6) Input the spending variance (H) Calculate the volume variance using the budgetted a $ (C) (D) (F) (F) $ $ $ $ 3 Master Budget Actual 4. Sales : February February per Unit 5 6 Units 42,500 $ 9.50 35,000 9.85 Total Sales Revenue $ 403,750 $ 344,750 8 9 Variable Costs: 10 Production $ 235,875 $ 5.55 190,750 $ 5.45 11 Selling 4,038 0.10 3,850 0.11 12 Total Variable Costs $ 239,913 $ 5.65 $ 194,600 $ 5.56 13 14 Contribution Margin $ 163,838 $ 3.86 $ 150,150 $ 4.29 15 16 Fixed Costs: 17 Selling 52,500 60,000 18 Administration 63,000 58,500 19 Total Fixed Costs 115,500 118,500 20 21 Net Income $ 48,338 (A) $ 31,650 22 23 Master Budget vs. Actual Net Income Variance $ (16,688) 24 What is the volume variance (G) 25 What is the spending rate variance $ (H) $ $ $ $ 52,500 (E) 63,000 (E) 115,500 (F) (F) $ (115,500) $ (F) 2 3 Master Budget Relevant Costs 127,500 (A) Outsource Relevant Costs (A) Input the number of units to be produced (same as the sales for the quarter) (B) From the master budget, input the variable costs per unit for production. (C) From the master budget, input the variable costs per unit for selling. (D) Input the total fixed costs from the master budget (E) Input the vendor's price $ 7.50 (F) Input a $.05 incremental variable selling costs due to the outsourcing decision (G) Enter the total fixed costs that will NOT be avoided due to oursourcing Selling: $50,000 Admin $40,000 4 5 Units to Produce 6 Variable Costs (stated as per unit) 7 Production 8 Selling 9 Total Variable Costs per unit 10 11 Fixed Costs 12 Selling 13 Administration 14 Total Fixed Costs (B) 0.10 (c) 0.10 (E) (F) $ $ 157,625 (D) 189,150 (D) 346,775 (G) (G) 15 16 Total Costs $ 358,888 $ 17 18 Cost per unit produced $ 2.81 $ 19 20 21 22 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Year 0 (300,000) Totals (300,000) 135,000 135,000 135,000 135,000 135,000 200,000 200,000 200,000 200,000 200,000 1,675,000 (50,000) (50,000) (135,000) (1,150,000) (95,000) (95,000) (95,000) (95,000) (135,000) (135,000) (135,000) (95,000) (135,000) (E) 40,000 65,000 (300,000) 40,000 40,000 40,000 40,000 65,000 65,000 65,000 15,000 175,000 (300,000) (260,000) (220,000) (180,000) (140,000) (100,000) (35,000) 30,000 95,000 160,000 175,000 2 3 Investment 4 5 Cash Flows 6 Sales 7 Salvage value 8 Operating Costs 9 Major Maintenance 10 Net Cash Flow 11 12 Cummulative CF 13 14 Hurdle Rate 15 16 Net Present Value (=NPV) 17 18 Internal Rate of Return (=IRR) 19 20 Payback Period 21 22 Profitability Index 23 24 Major Maintenance $ 5% (A) Use the Excel function =NPV in order to determine the value of the future cash flows, net of the initial investment (B) Use the IRR function in order to determine the "interest" earned on the cash flows from year 0 to year 10 (C) Look at the cummulative cash flow. How long before the initial investment is paid off? (D) Calculate the ratio of the project's PV of cashflows from years 1-10/ initial investment (85,000) (E) Decide in which year (5-7) this expenditure should be made. Add $5,000 for every year after year 5 Capital Project Justification It is now time to prepare a capital budget. The idea is to prepare a list of acceptable projects in which to spend in the first quarter in order to increase the value of operations. Recall that the cash budget included funds for plant expansion. The estimated future cash flows arising from sales, net of operating costs are given. The hurdle rate represents the interest rate to borrow these funds plus a 2% risk factor. Use this percent in order to discount the cash flows. The project cost, and the salvage value as well, have already been input into the spreadsheet. 1. Input the positive cash flows due to production cost savings. Sales revenues: Years 1-5 $135,000 per year; Years 610 $200,000 per year Operating costs: Years 1-5 $95,000 per year; Years 610 $135,000 per year 2. Decide on the best year (57) to spend the $85,000 of major maintenance. Complete the five measures at the bottom of the spreadsheet. 3. Using Excel functions, given a hurdle rate calculate the project's Net Present Value (=NPV) and the Internal Rate of Return (=IRR). 4. Calculate the payback period and profitability index. Provide a recommendation as to whether this project is acceptable. . Performance Evaluation The data for the February master budget columns should come over from the operating budget worksheet. Verify that the master budge Net Income is the same as that reported in Sheet #1. Complete a flexible budget for February, showing what net income should have been using the operating budget revenue rate, variable expenses' rates, and fixed costs. 1. Given actual results and the operating budgeted rates, prepare a flexible budget for 1 month. Actual operating results for the month are provided. DO NOT CHANGE THIS DATA. 2. Calculate the variances between the flexible budget and actual results, as being either F for favorable or U for unfavorable. Determine how much of the Net Income variance was due to volume and how much was rate-related. Explain deviations from plan. How would you evaluate the actual results? What steps would you take to further investigate and possibly adjust your budget for the rest of the year? Incremental Analysis: Do We Outsource? 1. Given the relevant costs from the operating budget, prepare a worksheet comparing the relevant data to a vendor's price quote for doing the production currently done in-house. 2. Given avoidable costs, calculate whether the outsourcing decision will save costs in total. Provide an opinion as to whether this business deal is acceptable. Are there any nonfinancial considerations? 3 4 Jan 40000 40,000 9.50 $ 380,000 $ Feb 42500 42,500 9.50 $ 403,750 $ April 45000 45,000 9.50 427,500 $ Total Qtr 127500 127,500 Average Sales Price/unit (A) wgtd. % Sales average 10.50 50% $ 5.25 8.50 50% $ 4.25 100% $ 9.50 Sales Price (all 2 topping): Large $ Medium $ 1,211,250 (A) Input the planned product mix (B) Input the forecast sale volume (c) Input the number of pizza made per hour (D) Input the sales commission as a % of reven (E) Input the sales manager's salary (F) Input the office manager's salary (G) Calculate the breakeven point in sales units (H) Calculate the sales units needed to reach a $200,000 net income target, 5.55 $ 15 12.00 5.55 $ 0.10 5.65 $ 154,200 $ 3.86 S 40.6% 0.10 5.65 $ 163,838 $ 3.86 $ 40.6% 5.55 0.10 5.65 173,475 $ 3.86 40.6% Direct Labor: (c) efficiency rate as minutes per pizza hourly labor cost plus fringe benefits $ Total direct labor per pizza $ 719,738 491,513 5 Sales : 6 Units (B) 7 Price/unit $ 8 Total Sales Revenue $ 9 10 Variable Costs (stated as per unit) 11 Production $ 12 Selling 13 Total Variable Costs per unit $ 14 Contribution Margin $ 15 CM per unit $ 16 CM Ratio 17 Fixed Costs 18 Selling (E) $ 19 Administration (F) 20 Total Fixed Costs $ 21 22 Net Income $ 23 24 Breakeven Point in sales units 25 26 Sales units for a Target Profit of $200,000 27 20 3.00 0.25 50,000 $ 60,000 110,000 $ 52,500 $ 63,000 115,500 $ 55,125 $ 66,150 121,275 $ 157,625 189,150 346,775 Direct Materials: Dough $ cheese toppings caffinated tomato sauce Boxes Total direct material per pizza $ 0.50 0.75 1.00 0.05 2.55 44,200 $ 48,338 $ 52,200 $ 144,738 28,534 (G) Sales comissions (D) Total Variable Selling Expenses per pizza $ 1% 0.10 80,415 (H) 380,000 $ 403,750 $ 427,500 $ 1,211,250 45,000 $ 120,000 95,000 260,000 $ 30,000 $ 228,000 100,938 358,938 $ 57,000 242,250 106,875 406,125 $ 132,000 590,250 302,813 1,025,063 (A) Calculate, based on the collection history, the cash receipts from customers (B) Calculate the cash disbursements for raw material purchases, assuming 1/2 of the previous month's purchases are paid in the current month Total Purchases December January February March $ 53,684 $ 102,000 $ 108,375 $ 114,750 77,842 53,800 60,000 191,642 $ 68,358 $ 3 Cash Receipts 4 Sales Revenues $ 300,000 $ 200,000 $ 5 Cash Receipts from: 6 2 months ago (10%) $ 7 1 month ago (60% (A) 8 current month (25%) 9 total cash receipts $ 10 Cash Disbursements 11 Raw Materials (B) 12 Selling Expenses 13 Administrative Expenses 14 total disbursements $ 15 Net Operating Cash $ 16 17 Investments: 18 Expand Business (C) 19 20 Financing: 21 New Debt (D) 22 Repay debt 23 24 Net Cash Flow $ 25 Beginning Cash Balance 26 Ending Cash Balance $ 27 28 THE MINIMUM CASH BALANCE IS $ 75.000 105,188 56,538 63,000 224,725 $ 134,213 $ 111,563 59,400 66,150 237,113 $ 169,013 $ 294,592 169,738 189,150 653,480 (c) Decide in which month you will make a capital investment of $300,000 (D) Determine the appropriate financing activities so as to keep at least the required minimum cash balance of $ 75,000 and payoff any amount borrowed. 371,583 (300,000) (300,000) 222,430 222,430 (222,430) (222,430) 68,358 $ 75,000 143,358 $ 56,643 $ 143,358 200,000 $ (53,418) $ 200,000 146,583 $ 71,583 75,000 146,583 Flex Budget February Variances (flex. vs. actual) Total per Unit per Unit per Unit (B) (F) $ (A) Verify that the net income is the same as the Op Bud (B) Input the flex budget sales units and the per unit pr (C) Input the flex budget production cost per unit and ca the total flex production costs. (D) Input the flex budget variable selling costs per unit (E) Input the flex budget fixed selling and administratior (F) Calculate the variances between the actual and flex! (6) Input the spending variance (H) Calculate the volume variance using the budgetted a $ (C) (D) (F) (F) $ $ $ $ 3 Master Budget Actual 4. Sales : February February per Unit 5 6 Units 42,500 $ 9.50 35,000 9.85 Total Sales Revenue $ 403,750 $ 344,750 8 9 Variable Costs: 10 Production $ 235,875 $ 5.55 190,750 $ 5.45 11 Selling 4,038 0.10 3,850 0.11 12 Total Variable Costs $ 239,913 $ 5.65 $ 194,600 $ 5.56 13 14 Contribution Margin $ 163,838 $ 3.86 $ 150,150 $ 4.29 15 16 Fixed Costs: 17 Selling 52,500 60,000 18 Administration 63,000 58,500 19 Total Fixed Costs 115,500 118,500 20 21 Net Income $ 48,338 (A) $ 31,650 22 23 Master Budget vs. Actual Net Income Variance $ (16,688) 24 What is the volume variance (G) 25 What is the spending rate variance $ (H) $ $ $ $ 52,500 (E) 63,000 (E) 115,500 (F) (F) $ (115,500) $ (F) 2 3 Master Budget Relevant Costs 127,500 (A) Outsource Relevant Costs (A) Input the number of units to be produced (same as the sales for the quarter) (B) From the master budget, input the variable costs per unit for production. (C) From the master budget, input the variable costs per unit for selling. (D) Input the total fixed costs from the master budget (E) Input the vendor's price $ 7.50 (F) Input a $.05 incremental variable selling costs due to the outsourcing decision (G) Enter the total fixed costs that will NOT be avoided due to oursourcing Selling: $50,000 Admin $40,000 4 5 Units to Produce 6 Variable Costs (stated as per unit) 7 Production 8 Selling 9 Total Variable Costs per unit 10 11 Fixed Costs 12 Selling 13 Administration 14 Total Fixed Costs (B) 0.10 (c) 0.10 (E) (F) $ $ 157,625 (D) 189,150 (D) 346,775 (G) (G) 15 16 Total Costs $ 358,888 $ 17 18 Cost per unit produced $ 2.81 $ 19 20 21 22 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Year 0 (300,000) Totals (300,000) 135,000 135,000 135,000 135,000 135,000 200,000 200,000 200,000 200,000 200,000 1,675,000 (50,000) (50,000) (135,000) (1,150,000) (95,000) (95,000) (95,000) (95,000) (135,000) (135,000) (135,000) (95,000) (135,000) (E) 40,000 65,000 (300,000) 40,000 40,000 40,000 40,000 65,000 65,000 65,000 15,000 175,000 (300,000) (260,000) (220,000) (180,000) (140,000) (100,000) (35,000) 30,000 95,000 160,000 175,000 2 3 Investment 4 5 Cash Flows 6 Sales 7 Salvage value 8 Operating Costs 9 Major Maintenance 10 Net Cash Flow 11 12 Cummulative CF 13 14 Hurdle Rate 15 16 Net Present Value (=NPV) 17 18 Internal Rate of Return (=IRR) 19 20 Payback Period 21 22 Profitability Index 23 24 Major Maintenance $ 5% (A) Use the Excel function =NPV in order to determine the value of the future cash flows, net of the initial investment (B) Use the IRR function in order to determine the "interest" earned on the cash flows from year 0 to year 10 (C) Look at the cummulative cash flow. How long before the initial investment is paid off? (D) Calculate the ratio of the project's PV of cashflows from years 1-10/ initial investment (85,000) (E) Decide in which year (5-7) this expenditure should be made. Add $5,000 for every year after year 5
