BUS310, Homework #1, Spring 2018                                                                                        Instructor:Betsy McCall

Instructions: Complete each problem on a separate worksheet in a single Excel file.  Rename the separate worksheets with the respective problem number.  You may have to copy and paste the datasets into your homework file first.  Name the file with your last name, first initial, and HW #1.  Label each part of the question. When calculating statistics, label your outputs.Submit your completed file in Blackboard.Use the Solver add-in for these problems.

1. A chemical company manufacturers three chemicals: A, B, C.  These chemicals are produced via two production processes: 1 and 2.  Running process 1 for an hour costs $400 and yields 300 units of A, 100 units of B and 100 units of C.  Running process 2 for an hour costs $100 and yields 100 units of A, and 100 units of B.  To meet customer demands, at least 1000 units of A, 500 units of B and 300 units of C must be produced daily.
2. Use Solver to determine a daily production plan that minimizes the cost of meeting the company’s daily demands.
3. Confirm graphically that the daily production plan from part a minimizes the cost of meeting the company’s daily demands.
4. Use SolverTable to see what happens to the decision variables and the total cost when the hourly processing cost for process 2 increases in increments of $0.50.  How large must this cost increase be before the decision variables change?  What happens when it continues to increase beyond this point?

• A furniture company manufactures desks and chairs.  Each desk uses four units of wood, and each chair uses three units of wood.  A desk contributes $400 to profit, and a chair contributes $250.  Marketing restrictions require that the number of chairs produced be at least twice the number of desks produced.  There are 2000 units of wood available.
• Use Solver to maximize the company’s profit.
• Confirm graphically that the solution in part a maximizes the company’s profit.
• Use SolverTable to see what happens to the decision variables and the total profit when the availability of wood varies from 1000 to 3000 in 100-unit increments.  Based on your findings, how much would the company be willing to pay for each extra unit of wood over its current 2000 units?  How much profit would the company lose if it lost any of its current 2000 units?

• A farmer in Iowa owns 450 acres of land.  He is going to plant each acre with wheat or corn.  Each acre planted with wheat yields $2000 profit, requires three workers, and requires two tons of fertilizer.  Each acre planted with corn yields $3000 profit, requires two workers, and requires four tons of fertilizer.  There are currently 1000 workers and 1200 tons of fertilizer available.
• Use Solver to help the farmer maximize the profit from this land.
• Confirm graphically that the solution from part a maximizes the farmer’s profit from his land.
• Use SolverTable to see what happens to the decision variables and the total profit when the availability of fertilizer varies from 200 tons to 2200 tons in 100-ton increments.  When does the farmer discontinue producing wheat?  When does he discontinue producing corn?  How does the profit change for each 10-ton increment?

 

Looking for a Similar Assignment? Order now and Get 10% Discount! Use Coupon Code "Newclient"

Microsoft Excel 16.0 Sensitivity Report
Worksheet: [Book1]Sheet5
Report Created: 6/24/2019 9:22:46 PM
Variable Cells
			Final	Reduced	Objective	Allowable	Allowable
	Cell	Name	Value	Cost	Coefficient	Increase	Decrease
	$B$3	Variables Doubles	150	0	65	8.333333333	65
	$C$3	Variables Singles	200	0	55	1E+30	6.25
Constraints
			Final	Shadow	Constraint	Allowable	Allowable
	Cell	Name	Value	Price	R.H. Side	Increase	Decrease
	$D$4	Labor Total	960	20.3125	960	160	480
	$D$5	Single Total	200	6.25	200	200	200
	$D$6	Combination Total	350	0	400	1E+30	50

 

Looking for a Similar Assignment? Order now and Get 10% Discount! Use Coupon Code "Newclient"