Question: please solve this using excel Candoit Inc., an aerospace parts manufacturing company, investigates an upgrade plan according to which some of its old machines will

please solve this using excel

please solve this using excel Candoit Inc., an aerospace parts manufacturing company,

Candoit Inc., an aerospace parts manufacturing company, investigates an upgrade plan according to which some of its old machines will be replaced with advanced automatic machines that require fewer operators. Currently the company operates 49 weeks per year, 7 days per week, and 1 shift per day. The company has employed 3 utility workers and utilizes 14 similar machines, each being operated by 4 direct workers. Each worker's hourly wage is $13. Candoit can decrease its manning level by reducing the number of its direct workers, which is possible thanks to the higher automation levels of the new advanced machines. Currently, there are two models of automatic machines available in the market with the following specifications: Each new machine can be purchased by financing over its stated service life and rate of return (interest rate). On the other hand, each old machine can be sold at $9,000 and replaced by a new Base or Premium model machine. Note that the total number of machines remains constant. Replacement has the following consequences: - Revenues: (1) operations costs of the reduced direct workers will be saved, and (2) the old machine can be sold at the aftermarket. - Costs: (1) annual amortization of the initial investment, (2) new machine's operation costs, and (3) new machine's annual maintenance costs. If the company wants to decrease its current manning level by at least 34\%, calculate the minimum total annual cost (in dollars) to achieve this goal (round to the nearest integer). Hint: Formulate the problem as an Integer Linear Programming model and solve using Lindo or Excel (recommended). Watch videos on how to formulate and solve such models in Excel. The decision variables can be m1 and m2, which are integer numbers respectively representing the number of Base and Premium machines to be purchased and installed. The objective function is to minimize all annual costs, expressed as a linear function of the decision variables. The constraints are (1) keeping the reduction percentage in the manning level greater than or equal to the given target level, and (2) the total number of new machine purchases cannot exceed the current number of machines

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!