NSTRUCTIONS

Revisit the class work and practice the problem we solved in class.

Build an Excel spreadsheet model for the following problem and solve for maximum profit:

A pipe manufacturer can make four different types of pipes X$1,$ X$2,$ X$3,$ and X$4.$ Following Table summarizes key resources used for manufacturing:

X$1$ X$2$ X$3$ X$4$ Max Available

Extrusion $4$hr $3$hr $6$hr $7$hr $1200$hr

Polishing $1$hr $1\mathrm{.}5$hr $1$hr $3$hr $800$hr

Packaging $2\mathrm{.}5$hr $4\mathrm{.}2$hr $1\mathrm{.}7$hr $4\mathrm{.}5$hr $800$hr

Additive $2$lbs $3$lbs $1$lbs $5$lbs $100$lbs

Profit margin for the X$1,$X$2,$X$3,$ and X$4$ pipes are $$11,$ $$12\mathrm{.}6,$ $$13\mathrm{.}7,$ and $$16\mathrm{.}8$ per unit of production.

A$)$ Determine the product mix $($how much of each pipe$)$ should the firm make to maximize its profit. Assume all quantity manufactured can be sold.

B$)$ Assume that the marketing department wants at least $100$ units of each pipe to be made. What will be the profit in this case?

First solve for part A$.$ Copy your spreadsheet into a new worksheet, add the new constraint in this sheet and solve.