Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please show excel functions/formulas and step-by-step process using excel . I am struggling using excel.. :( Mid-Michigan Manufacturing Inc. (MMMI) wishes to determine
Please show "excel functions/formulas" and step-by-step process using excel. I am struggling using excel.. :(
Mid-Michigan Manufacturing Inc. (MMMI) wishes to determine whether it would be advisable to replace an existing production machine with a new one. The have hired your firm as a consultant to determine whether the new machine should be purchased. The data you will need to make this determination is as follows: MMMI has decided to set a project timeline of 4 years. The new machine will cost $1,100,000. It will be depreciated (straight line) over a five-year period (its estimated useful life), assuming a salvage value of $100,000. > The old machine, which has been fully depreciated, could be sold today for $253,165. The company has received a firm offer for the machine from Williamston Widgets, and will sell it only if they purchase the new machine. > Additional Sales generated by the superior products made by the new machine would be $665,000 in Year 1. In Years 2 & 3 sales are projected to grow by 8.5% per year. However, in Year 4, sales are expected to decline by 5% as the market starts to become saturated. Total expenses have been estimated at 60.75% of Sales. > The firm is in the 21% marginal tax bracket and requires a minimum return on the replacement decision of 9%. > A representative from Stockbridge Sprockets has told MMMI that they will buy the machine from them at the end of the project (the end of Year 4) for $100,000. MMMI has decided to include this in the terminal value of the project. The project will require $100,000 in Net Working Capital, 75% of which will be recovered at the end of the project. Part A: Base Case project decision (20 points) On the Assignment spreadsheet, on the part A tab, fill in the value drivers. Build the DCF Model, and calculate NPV and IRR Somewhere on this tab, state whether or not the company should purchase the new machine just based on the Base Case Note: your company has estimated that the Base Case has a 50% chance of being correct. Sensitivity/Scenario Analysis (5 points for each part) Copy the Part A tab to the tabs for Parts B through D. Analyze the following scenarios, making the relevant changes to each model. On each tab, state whether or not you would recommend purchasing the machine under that scenario: 8.5% -5.0% 60.75% VALUE DRIVERS Sales Growth yrs 2-3 Sales Growth yr 4 Expenses as % of sales Cost of new machine Salvage value Old machine resale New machine resale Tax rate Required Rate of Return Project Time Period Years 2 Initial Investment (1) $665,000 $403,988 $721,525 $438,326 $782,855 $475,584 $743,712 $451,805 OCF: Revenues Expenses Depreciation EBIT Taxes Net Income Depreciation OPERATING CASH FLOW Net Working Capital Total Cash Flow NPV IRR Mid-Michigan Manufacturing Inc. (MMMI) wishes to determine whether it would be advisable to replace an existing production machine with a new one. The have hired your firm as a consultant to determine whether the new machine should be purchased. The data you will need to make this determination is as follows: MMMI has decided to set a project timeline of 4 years. The new machine will cost $1,100,000. It will be depreciated (straight line) over a five-year period (its estimated useful life), assuming a salvage value of $100,000. > The old machine, which has been fully depreciated, could be sold today for $253,165. The company has received a firm offer for the machine from Williamston Widgets, and will sell it only if they purchase the new machine. > Additional Sales generated by the superior products made by the new machine would be $665,000 in Year 1. In Years 2 & 3 sales are projected to grow by 8.5% per year. However, in Year 4, sales are expected to decline by 5% as the market starts to become saturated. Total expenses have been estimated at 60.75% of Sales. > The firm is in the 21% marginal tax bracket and requires a minimum return on the replacement decision of 9%. > A representative from Stockbridge Sprockets has told MMMI that they will buy the machine from them at the end of the project (the end of Year 4) for $100,000. MMMI has decided to include this in the terminal value of the project. The project will require $100,000 in Net Working Capital, 75% of which will be recovered at the end of the project. Part A: Base Case project decision (20 points) On the Assignment spreadsheet, on the part A tab, fill in the value drivers. Build the DCF Model, and calculate NPV and IRR Somewhere on this tab, state whether or not the company should purchase the new machine just based on the Base Case Note: your company has estimated that the Base Case has a 50% chance of being correct. Sensitivity/Scenario Analysis (5 points for each part) Copy the Part A tab to the tabs for Parts B through D. Analyze the following scenarios, making the relevant changes to each model. On each tab, state whether or not you would recommend purchasing the machine under that scenario: 8.5% -5.0% 60.75% VALUE DRIVERS Sales Growth yrs 2-3 Sales Growth yr 4 Expenses as % of sales Cost of new machine Salvage value Old machine resale New machine resale Tax rate Required Rate of Return Project Time Period Years 2 Initial Investment (1) $665,000 $403,988 $721,525 $438,326 $782,855 $475,584 $743,712 $451,805 OCF: Revenues Expenses Depreciation EBIT Taxes Net Income Depreciation OPERATING CASH FLOW Net Working Capital Total Cash Flow NPV IRRStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started