Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

how to do on excel Windsor Metal Company (WMC), a small manufacturer of fabricated metal parts, must decide whether to enter the competition to become

image text in transcribed

how to do on excel

Windsor Metal Company (WMC), a small manufacturer of fabricated metal parts, must decide whether to enter the competition to become the supplier of transmission housings for Gulf Electric, a company that produces the housings in its own in-house manufacturing facility but has almost reached its maximum production capacity. Therefore, Gulf is looking for an outside supplier. To compete, WMC must design a new fixture for the production process and purchase a new forge. The available details for this purchase are on the following slide. Financial Facts : Known with Great Confidence Required investment = $100,000 Fixed cost = $15,000/Xc Project Life = 5 years Income tax rate = 40% MARR = 10% Unknown but Predictable (Most Likely Values) Q: Number of units = 1,900 units P: Unit Price = $48 per unit VC: Unit variable cost = $20 per unit S: Salvage value = $30,000 (a) Conduct a sensitivity analysis for each of the four key uncertain variables Q, P, VC and Sin the range of -20% to 20% around the most likely value (the incremental is 2%). Present your result in both tables and scattered graphs. (40 marks) (b) Conduct a break-even analysis on Q. Use the goal-seeking function to find the break-even value of Q. (20marks) (c) The four unknown variables are normally distributed. The means and standard deviations are as follows: (40 marks) Q: mean = 1,900 units; o=200 P: mean = $48 per unit; o=3 VC: mean = $20 per unit; c=4 S: mean = $30,000; O=5.000 Develop a simulation model on the project NPW with 300 iteration. Present your result in a table and histogram. Windsor Metal Company (WMC), a small manufacturer of fabricated metal parts, must decide whether to enter the competition to become the supplier of transmission housings for Gulf Electric, a company that produces the housings in its own in-house manufacturing facility but has almost reached its maximum production capacity. Therefore, Gulf is looking for an outside supplier. To compete, WMC must design a new fixture for the production process and purchase a new forge. The available details for this purchase are on the following slide. Financial Facts : Known with Great Confidence Required investment = $100,000 Fixed cost = $15,000/Xc Project Life = 5 years Income tax rate = 40% MARR = 10% Unknown but Predictable (Most Likely Values) Q: Number of units = 1,900 units P: Unit Price = $48 per unit VC: Unit variable cost = $20 per unit S: Salvage value = $30,000 (a) Conduct a sensitivity analysis for each of the four key uncertain variables Q, P, VC and Sin the range of -20% to 20% around the most likely value (the incremental is 2%). Present your result in both tables and scattered graphs. (40 marks) (b) Conduct a break-even analysis on Q. Use the goal-seeking function to find the break-even value of Q. (20marks) (c) The four unknown variables are normally distributed. The means and standard deviations are as follows: (40 marks) Q: mean = 1,900 units; o=200 P: mean = $48 per unit; o=3 VC: mean = $20 per unit; c=4 S: mean = $30,000; O=5.000 Develop a simulation model on the project NPW with 300 iteration. Present your result in a table and histogram

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Production And Operations Analysis

Authors: Steven Nahmias, Tava Lennon Olsen

7th Edition

1478623063, 9781478623069

More Books

Students also viewed these Finance questions

Question

Explain the concept of employment at will.

Answered: 1 week ago

Question

Discuss compensation for sales representatives.

Answered: 1 week ago

Question

Explain termination of employment.

Answered: 1 week ago