Programming Question with Excel and Solver

Nov 2008
152
1
A cabinet shop produces and installs cabinets. Business is good, and the shop has an unlimited number of customers willing to pay $100 for each cabinet installed. However, for the next month, the shop has only 1750 hrs. of labor and 1032 units of wood that it can commit for cabinet production. Each installed cabinet requires 5 hrs. of labor, 3 units of wood, and one frame. The frames can be prepared in the shop before installation, with each frame requiring 2 hr. of the shop's labor and 1 unit of its wood, or they can be bought ready for installation from the local mill for $27 each. The shop pays $6/hr. for labor, $5/unit for wood, and only pays for the labor and wood used. For the next month, how many cabinets should the shop install, and how should the necessary frames be generated so that net income is maximized?

I need help figuring out how to set up a table that sets up the problem. I can figure out the Excel stuff, I'm just confused as to how to set up the problem.
 

CaptainBlack

MHF Hall of Fame
Nov 2005
14,972
5,271
someplace
A cabinet shop produces and installs cabinets. Business is good, and the shop has an unlimited number of customers willing to pay $100 for each cabinet installed. However, for the next month, the shop has only 1750 hrs. of labor and 1032 units of wood that it can commit for cabinet production. Each installed cabinet requires 5 hrs. of labor, 3 units of wood, and one frame. The frames can be prepared in the shop before installation, with each frame requiring 2 hr. of the shop's labor and 1 unit of its wood, or they can be bought ready for installation from the local mill for $27 each. The shop pays $6/hr. for labor, $5/unit for wood, and only pays for the labor and wood used. For the next month, how many cabinets should the shop install, and how should the necessary frames be generated so that net income is maximized?

I need help figuring out how to set up a table that sets up the problem. I can figure out the Excel stuff, I'm just confused as to how to set up the problem.
1. Identify a suitable set of decision variables, say the number of cabinets to produce and the number of frames to be bought in.

2. Express the income in terms of these decision variables

3. Identify the constraints

CB