Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

A company produces four products each of which must undergo assembly, inspection, and packaging. The information below summarizes the hours required for each operation

image text in transcribed

A company produces four products each of which must undergo assembly, inspection, and packaging. The information below summarizes the hours required for each operation by each product, the total hourly capacity for each operation, and the fixed cost incurred by each product if it is produced. Hours Required Available Operation Product 1 Product 2 Product 3 Product 4 Hours Assembly 4 4 500 Inspection 2 1 3 2 300 Packaging 1 1 2 2 200 Fixed Cost $1100 $1000 $900 $850 Each unit of product I sold will contribute $60 to profit, and each unit of products 2, 3, and 4 contributes $65, $70, and $55, respectively. What is the optimal product mix? Set this problem up in an Excel spreadsheet in a well-organized manner. Use formulae that rely on cell references. Clearly label your input parameters, decision variable (or changing cells), constraints (both left- and right-hand sides), and objective function. Use Solver within Excel to solve the problem. Be sure to clearly state both optimal product mix and the objective function value obtained by producing that optimal product mix. (NOTE: While this problem is small enough that you may be able to determine a reasonable solution without using Excel or Solver, for purposes of this Final Exam you must properly set it up in Excel and solve it using Solver. It is recommended that you use a reasonable solution you can readily identify to verify that your Excel model works and that you have set everything up correctly in Solver.)

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

Intermediate Accounting

Authors: Earl K. Stice, James D. Stice

18th edition

538479736, 978-1111534783, 1111534780, 978-0538479738

More Books

Students also viewed these Accounting questions