Excel In$-$Class Exercise $2$

In this exercise, we will try to design a simple AIS using Excel Solver to facilitate production.

Alpha Inc. is a company that produces hot chocolate mix and instant coffee. The production

process is described below.

Direct material:

To produce $1$ unit of hot chocolate mix, we will use $0\mathrm{.}7$ unit of cacao seeds, $0\mathrm{.}2$ unit of sugar,

and $0\mathrm{.}1$ unit of milk.

To produce $1$ unit of instant coffee, we will use $0\mathrm{.}8$ unit of coffee beans and $0\mathrm{.}2$ unit of sugar.

Our warehouse isn$$t big, so the total units of cacao seeds and coffee beans cannot exceed $15.$

Material cost:

Cacao seeds cost $$10/$unit$.$ Sugar costs $$3/$unit$.$ Milk costs $$5/$unit$.$ Coffee beans cost $$12/$unit$.$

Machine hours:

To produce $1$ unit of hot chocolate mix, we will use our grinder for $1$ hour and our mixer for $0\mathrm{.}5$

hour.

To produce $1$ unit of instant coffee, we will use our grinder for $0\mathrm{.}75$ hour and our mixer for $0\mathrm{.}75$

hour.

Direct labor:

Our employee operating the grinder earns $$20/$hour$.$

Our employee operating the mixer earns $$15/$hour$.$

Note that each employee can work for no more than $8$ hours$/$day for the pay above. If we want

them to work more than $8$ hours, we need to triple their pay per hour. Also, either employee

shouldn$$t work more than $12$ hours each day.

Price and demand:

For hot chocolate mix, the relation between price and number of units produced is:

Price $=35$ # of Units Produced

For instant coffee, the relation is:

Price $=(70$ # of Units Produced$)/1\mathrm{.}5$

Question:

To maximize our profit, how many units of hot chocolate mix and instant coffee should we

produce every day? What if the relation for chocolate mix is Demand $=120$ Price?