9 Decision To Be Made 10 11 12 13 14 Investment size, valuation, sensitivity analysis, what-if...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
9 Decision To Be Made 10 11 12 13 14 Investment size, valuation, sensitivity analysis, what-if capabilities, Excel Data Table, cost structure, capital structure 15 16 You are Executive Vice President of Operations of a company that produces and distributes household goods. 17 Your company manufactures some of its products and purchases and resells other products. 18 Your company has been distributing brushes purchased from a third-party and you are analyzing the economics of insourcing (manufacturing internally) the supply. 19 These are the facts that you need to incorporate into a model: 20 Your company has been spending $200,000 annually to purchase brushes. This expense will cease if supply of brushes is insourced. 21 You estimate that manufacturing inhouse will cost $100,000 in labor and $10,000 in overhead. 22 23 24 25 26 27 28 29 30 31 32 Whether to continue purchase brushes to be distributed or to begin manufacturing them internally? How big an investment can you make for insourcing to be attractive? How should you finance the investment. Strategic Considerations & KPIs A significant investment in equipment will be required and the investment will be straight-line depreciated over a 5 year useful life (no residual value). Additional work needs to be done to firm up an estimate but you believe the equipment will cost between $200,000 and $350,000. The Treasurer of your company needs to decide how the investment would be financed and has asked you to analyze the sensitivity of the return to financing options. At the end of the previous year, 2022, your company had $200,000 in Cash, zero debt, and $100,000 in Equity on its Balance Sheet. For the purpose of developing the model, you have selected some initial values for the two independent variables. Investment Required Percent Debt Financed 300,000 50% sponsible for developing a model that analyzes the potential investment, providing valuations of alternate strategies and a sensitivity analysis of the results. 32 33 34 35 36 37 38 Income Statement 39 40 41 42 43 44 C B D A F G H I J You are responsible for developing a model that analyzes the potential investment, providing valuations of alternate strategies and a sensitivity analysis of the results. The following model should include only the incremental impact on the Income Statement, Balance Sheet and Cash Flow Statement. Please enter formula in the yellow boxes below to develop the model. 53 54 55 56 57 58 59 60 61 62 63 Finished Goods Expense Depreciation Labor Overhead EBIT Interest Income (5% interest rate) Pretax Tax (30%tax rate) Net Income 45 46 47 48 49 Balance Sheet (change in account) 50 Cash 51 PP&E 52 Debt Equity Cash Flow Sta ement Net Income Depreciation Cash Flow from Operations CapEx Cash Flow from Investing Issues (Retirement) Debt Cash Flow from Financing || Generated 2022 200,000 0 0 100,000 EXCEL Financial Functions 0 (Current) 2023 50,000 300,000 150,000 100,000 (300,000) (300,000) 150,000 150,000 (150,000) E 1 2024 420,000 (60,000) (210,000) (10,000) 140,000 140,000 42,000 98,000 0 DATA TABLES 2 2025 420,000 (60,000) (210,000) (10,000) 140,000 140,000 42,000 98,000 0 3 2026 420,000 (60,000) (210,000) (10,000) 140,000 140,000 42,000 98,000 0 4 2027 420,000 (60,000) (210,000) (10,000) 140,000 140,000 42,000 98,000 0 MODEL Simple Company 5 2028 420,000 (60,000) (210,000) (10,000) 140,000 140,000 42,000 98,000 0 Hints K L M Shown as positive because it is an expense that will cease to exist N Net Income from the Income Statement Investment Required spread of the useful life (5 years) MODEL Marketing Stratagy O Interest Income is based on a 5% interest rate applied to prior year Net Cash (Cash minus Debt) P Cash is equal to that of the previous year plus Cash Generated PP&E is equal to that of the previous year minus Depreciation plus CapEx. (However, CapEx is shown as a negative on the Debt is equal to that of the previous year plus Issues (Retirement) of Debt Equity is equal to that of the previous year plus Net Income (there are no Dividends) Q MODEL Make-Buy Decision || Debt based on Percent Debt Financed with 1/5th retired each year. Must be a formula based on D52 enabling changed a 57 58 59 60 A Depreciation 87 88 Cash Flow from Operations CapEx B Cash Flow from Investing Issues (Retirement) Debt Cash Flow from Financing Cash Generated C 82 83 84 85 86 Which would generate the higher NPV? Investment Required D 61 62 63 64 65 Write a fomula that calculates the Net Present Value (NPV) of the cash flows in Row 63 using a 10% discount factor. 66 67 68 (300,000) (300,000) 150,000 150,000 (150,000) 200,000 250,000 300,000 350,000 EXCEL Financial Functions E 0 0% F 0 Change the 'Investment Required', cell D28, to $200,000 and enter the resulting NPV value (not the formula) in the yellow box below. G DATA TABLES 0 Percent Debt Financed 25% 50% 69 70 71 Keeping the $200,000 'Investment Required' change the 'Percent Debt Financed' to 0% and enter the resulting NPV value (not the formula) in the yellow box below. 72 73 74 Below we present a Excel Data Table. 75 The Excel Data Table functionality permits users to identify a set of potential value for two independent variables. 76 The functionality automatically analyzes a formula that is dependent on these two variables and presents the results of all combinations of the two independent variables. The 'EXCEL Financial Functions' Sheet presents how to create a Data Table, but, the output is presented here for those that do not want to dive any deeper. 77 78 79 80 81 H 0 75% 100% I MODEL Simple Company 0 J K L M Investment Required spread of the useful life (5 years) N MODEL Marketing Stratagy O Debt based on Percent Debt Financed with 1/5th retired each year. Must be a formula based on D52 enabling changed a P MODEL Make-Buy Decision 9 Decision To Be Made 10 11 12 13 14 Investment size, valuation, sensitivity analysis, what-if capabilities, Excel Data Table, cost structure, capital structure 15 16 You are Executive Vice President of Operations of a company that produces and distributes household goods. 17 Your company manufactures some of its products and purchases and resells other products. 18 Your company has been distributing brushes purchased from a third-party and you are analyzing the economics of insourcing (manufacturing internally) the supply. 19 These are the facts that you need to incorporate into a model: 20 Your company has been spending $200,000 annually to purchase brushes. This expense will cease if supply of brushes is insourced. 21 You estimate that manufacturing inhouse will cost $100,000 in labor and $10,000 in overhead. 22 23 24 25 26 27 28 29 30 31 32 Whether to continue purchase brushes to be distributed or to begin manufacturing them internally? How big an investment can you make for insourcing to be attractive? How should you finance the investment. Strategic Considerations & KPIs A significant investment in equipment will be required and the investment will be straight-line depreciated over a 5 year useful life (no residual value). Additional work needs to be done to firm up an estimate but you believe the equipment will cost between $200,000 and $350,000. The Treasurer of your company needs to decide how the investment would be financed and has asked you to analyze the sensitivity of the return to financing options. At the end of the previous year, 2022, your company had $200,000 in Cash, zero debt, and $100,000 in Equity on its Balance Sheet. For the purpose of developing the model, you have selected some initial values for the two independent variables. Investment Required Percent Debt Financed 300,000 50% sponsible for developing a model that analyzes the potential investment, providing valuations of alternate strategies and a sensitivity analysis of the results. 32 33 34 35 36 37 38 Income Statement 39 40 41 42 43 44 C B D A F G H I J You are responsible for developing a model that analyzes the potential investment, providing valuations of alternate strategies and a sensitivity analysis of the results. The following model should include only the incremental impact on the Income Statement, Balance Sheet and Cash Flow Statement. Please enter formula in the yellow boxes below to develop the model. 53 54 55 56 57 58 59 60 61 62 63 Finished Goods Expense Depreciation Labor Overhead EBIT Interest Income (5% interest rate) Pretax Tax (30%tax rate) Net Income 45 46 47 48 49 Balance Sheet (change in account) 50 Cash 51 PP&E 52 Debt Equity Cash Flow Sta ement Net Income Depreciation Cash Flow from Operations CapEx Cash Flow from Investing Issues (Retirement) Debt Cash Flow from Financing || Generated 2022 200,000 0 0 100,000 EXCEL Financial Functions 0 (Current) 2023 50,000 300,000 150,000 100,000 (300,000) (300,000) 150,000 150,000 (150,000) E 1 2024 420,000 (60,000) (210,000) (10,000) 140,000 140,000 42,000 98,000 0 DATA TABLES 2 2025 420,000 (60,000) (210,000) (10,000) 140,000 140,000 42,000 98,000 0 3 2026 420,000 (60,000) (210,000) (10,000) 140,000 140,000 42,000 98,000 0 4 2027 420,000 (60,000) (210,000) (10,000) 140,000 140,000 42,000 98,000 0 MODEL Simple Company 5 2028 420,000 (60,000) (210,000) (10,000) 140,000 140,000 42,000 98,000 0 Hints K L M Shown as positive because it is an expense that will cease to exist N Net Income from the Income Statement Investment Required spread of the useful life (5 years) MODEL Marketing Stratagy O Interest Income is based on a 5% interest rate applied to prior year Net Cash (Cash minus Debt) P Cash is equal to that of the previous year plus Cash Generated PP&E is equal to that of the previous year minus Depreciation plus CapEx. (However, CapEx is shown as a negative on the Debt is equal to that of the previous year plus Issues (Retirement) of Debt Equity is equal to that of the previous year plus Net Income (there are no Dividends) Q MODEL Make-Buy Decision || Debt based on Percent Debt Financed with 1/5th retired each year. Must be a formula based on D52 enabling changed a 57 58 59 60 A Depreciation 87 88 Cash Flow from Operations CapEx B Cash Flow from Investing Issues (Retirement) Debt Cash Flow from Financing Cash Generated C 82 83 84 85 86 Which would generate the higher NPV? Investment Required D 61 62 63 64 65 Write a fomula that calculates the Net Present Value (NPV) of the cash flows in Row 63 using a 10% discount factor. 66 67 68 (300,000) (300,000) 150,000 150,000 (150,000) 200,000 250,000 300,000 350,000 EXCEL Financial Functions E 0 0% F 0 Change the 'Investment Required', cell D28, to $200,000 and enter the resulting NPV value (not the formula) in the yellow box below. G DATA TABLES 0 Percent Debt Financed 25% 50% 69 70 71 Keeping the $200,000 'Investment Required' change the 'Percent Debt Financed' to 0% and enter the resulting NPV value (not the formula) in the yellow box below. 72 73 74 Below we present a Excel Data Table. 75 The Excel Data Table functionality permits users to identify a set of potential value for two independent variables. 76 The functionality automatically analyzes a formula that is dependent on these two variables and presents the results of all combinations of the two independent variables. The 'EXCEL Financial Functions' Sheet presents how to create a Data Table, but, the output is presented here for those that do not want to dive any deeper. 77 78 79 80 81 H 0 75% 100% I MODEL Simple Company 0 J K L M Investment Required spread of the useful life (5 years) N MODEL Marketing Stratagy O Debt based on Percent Debt Financed with 1/5th retired each year. Must be a formula based on D52 enabling changed a P MODEL Make-Buy Decision
Expert Answer:
Related Book For
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
Posted Date:
Students also viewed these accounting questions
-
Describe the basic elements of an internal evaluation program ways that it may benefit an aviation organization.
-
You are considering a maintenance contract from two vendors. Vendor Y charges $80,000 upfront and then $13,000 per year for the three-year life of the contract. Vendor Z charges $100,000 upfront and...
-
Explain how to apply the rules of Section (61)a to determine taxable items.
-
17. [1.1/2 Points] DETAILS PREVIOUS ANSWERS MY NOTES ASK YOUR TEACHER Write the composite function in the form f(g(x)). [Identify the inner function u-g(x) and the outer function y=f(u).) (Use...
-
The prices and dividends of a stock are given in the table below. (a) What is R2? (b) What is R4(3)? (c) What is r3? 152 0.2 254 0.2 353 0.2 4590.25
-
Explanation: Step 1: Certainly, I can give two unique physical science based game thoughts: Game 1: "The Roundabout Experience" Objective: The goal of this game is to direct a little ball around a...
-
Solve Problem 14.E2, part b, but do not assume \(R_{t}\) is constant. Derive an alternative for Eq. (13-29a) for nonconstant \(\mathrm{R}_{t}\) but with a constant amount of insoluble solids. Then...
-
Aunt Anns Kitchen offers a line of specialty food products to institutional customers and restaurant chains. The firm prides itself on using only the finest ingredients and preparation methods. The...
-
In early January 2023, Oriole Corporation applied for and received approval for a trade name, incurring legal costs of $44,800. In early January 2024, Oriole incurred $25,500 in legal fees in a...
-
Emily Jackson (Social Security number 765-12-4326) and James Stewart (Social Security number 466-74-9932) are partners in a partnership that owns and operates a barber shop. The partnerships first...
-
Cotton, Clothing Consumption, Culture: From Small Beginnings to a Global Cultural System Overview: Cotton has been a very popularly traded commodity throughout history, and its popularity continues...
-
1). An audio speaker producing a steady sound at an outdoor concert is 28 ft away from you. If you move to a position where the speaker is 64 ft distant, by what factor will the amplitude of the...
-
Builtrite is considering the purchase of a machine with the following cash flow diagram: year 0 spend $80,000 year 1 $40,000 year 2 $20,000 year 3 $10,000 year 4 $15,000 What is the payback period?
-
P was hit by an automobile driven by D. P brought a federal diversity action against D alleging negligence. P claimed that he suffered serious whiplash in the accident and that he has been physically...
-
FB Company is preparing its December 3 1 , 2 0 2 3 statement of financial position. The following items may be reported as either current or non - current liability: On December 1 5 , 2 0 2 3 , FB...
-
You are thinking about investing in one of the two mutual funds, MassMutual and Vanguard. You have obtained the following information based on their performance over the last five years. Of course,...
-
(b) For the given parameters of building with the initial selection of damper is given to be: m=2000 kg, = 0.15, k = 0.1x106 N/m and (ii) Fr(s) Y(s) against the frequency, Plot the absolute values...
-
1. Which of the four major types of information systems do you think is the most valuable to an organization? 2. How do you critically associate the ideas of business agility and business efficiency...
-
Teresa is a civil engineer who uses her automobile for business. Teresa drove her automobile a total of 21,732 miles during 2012, of which 95 percent was business mileage. The actual cost of...
-
Harold Conners (Social Security number 785-23-9873) lives at 13234 DeMilo Drive, Houston, TX 77052, and is self-employed for 2012. He estimates his required annual estimated tax payment for 2012 to...
-
Olive Corporation was formed and began operations on January 1, 2012. The corporation's income statement for the year and the balance sheet at year-end are presented below. The corporation made...
-
The following are selected transactions for Tyler, Inc., for 2017 and 2018. The firm closes its books on December 31. 2017 Dec. 31 Issued \($500,000\) of 12 percent, ten-year bonds for...
-
Magee Company reports the following in its current year financial statements: Compute the following ratios for Magee Company: a. Current ratio b. Quick ratio c. Times-interest-earned ratio Current...
-
For each of the following scenarios determine if the firm should (a) record as a liability; (b) disclose The solution is on as a contingent liability; or (c) neither: 1. The Seco Co. has been sued by...
Study smarter with the SolutionInn App