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.7 unit of cacao seeds, 0.2 unit of sugar, and 0.1 unit of milk. To produce 1 unit of instant coffee, we will use 0.8 unit of coffee beans and 0.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.5 hour. To produce 1 unit of instant coffee, we will use our grinder for 0.75 hour and our mixer for 0.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.5 Question: To maximize our profit, how many units of hot chocolate mix and instant coffee should we produce every day? 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.7 unit of cacao seeds, 0.2 unit of sugar, and 0.1 unit of milk. To produce 1 unit of instant coffee, we will use 0.8 unit of coffee beans and 0.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.5 hour. To produce 1 unit of instant coffee, we will use our grinder for 0.75 hour and our mixer for 0.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.5 Question: To maximize our profit, how many units of hot chocolate mix and instant coffee should we produce every day
