Description
Unformatted Attachment Preview
Module/Week 2 Homework Rubric
BUSA 3315: Spreadsheet Modeling with Business Applications
Problem
3.36
Items for Grading
Maximum Points
Data Input
2
Changing Cells
4
Three Formulas for Valves Obtained
6
Formula for Total Cost
4
Two Constraints
2
Objective, Min, Non-negativity
2
Data Input
1
Formula for Space Used
3
Formula for Min Required Ice Cream
6
Tom’s
Formula for Money Spent
Restaurant
Formula for Profit
4
3
Three Constraints
2
Objective, Min, Non-negativity
1
Total Points for This Assignment
40
Extra Credit Bonus Question: 3.26(a)
7 (if fully correct)
BUSA 3315 | Spreadsheet Modeling with Business Applications
Page 1 of 1
Module/Week 2 Homework: Introduction to Optimization Modeling
BUSA 3315: Spreadsheet Modeling with Business Applications
Please create a single Excel file with each problem on a separate worksheet tab.
The problem description for Problem 1 can be found on page 127 of your Practical
Management Science textbook, and the problem description for the optional extra-credit
question can be found on page 125.
Problem 1
Problem 3.36(a) -Manufacturing Mechanical Heart Valves (p. 127)
Inputs
Supplier 1
Supplier 2
Supplier 3
Cost/valve
$75
$65
$50
% small
40%
30%
20%
Purchases
% medium % large
40%
20%
35%
35%
20%
60%
=
Supplier 1
Supplier 2
Supplier 3
Valves obtained
Required
Total cost
BUSA 3315 | Spreadsheet Modeling with Business Applications
Page 1 of 2
Problem 2
Tom’s Restaurant has an ice cream counter where it sells two main products, ice
cream and frozen yogurt, each in variety of flavors. The restaurant makes one order
for ice cream and yogurt each week, and the store has enough freezer space for 115
gallons of both products. A gallon of ice cream costs $0.93 and a gallon of frozen
yogurt costs $0.75, and the restaurant budgets $90 each week for these products. The
manager estimates that each week the restaurant sells at least twice as much ice
cream as frozen yogurt. Profit per gallon of ice cream is $4.15 and profit per gallon of
yogurt is $3.60.
How many gallons of ice cream and frozen yogurt should restaurant order to
maximize its weekly profit?
Ice-cream
Frozen yogurt
Ice-cream
Frozen yogurt
Unit cost
Unit profit
Space used
Freezer space
Order per week (gal)
>=
Min required ice-cream (gal)
Budget
Money spent
Purchase answer to see full
attachment
