Excel Question

Create a single Excel workbook (.xls or .xslx) containing two
worksheets, one for each of the two questions below. When you are
finished modeling Q1 and Q2 below, upload your Excel workbook to
Canvas. Clearly organize your spreadsheet work and highlight your
final results. After you submit your HW, as the final task of the
assignment, include a comment using Canvas’s commenting system that
states, for Question 1, which supplier you choose, and for Question
2, what the number of people is.

Question 1 In operations management a classic result is the
“Economic Lot Size” formula, also called the “EOQ formula,” which
tells you the best order size of an item. The inputs to the formula
are: U : the annual usage amount of the item A : the acquisition
cost per order (e.g., the shipping cost per order) h : the item
warehousing cost (per year per unit) It has been proven many times
that the cost-minimizing order quantity is given by Q = SQRT( 2*U*A
/ h ) , and that if you use that best quantity Q then the
acquisition and holding costs yield a corresponding TOTAL COST =
SQRT( 2*U*A *h ). Define the above two formulas once each in an
Excel spreadsheet, without worrying about using Excel’s
“relative/absolute dollar signs.” To complete the rest of this
question, use a Data Table rather than copying-and-pasting your
formulas.

Assuming an annual usage rate U = 3000, create a two-way Excel
Data Table that shows TOTAL COST for all combinations of the values
A = {$250, $400, $800, $1000} and h = {$3, $4, $5, $6, $7, $8, $9}.
Using your table, if you had to pick between Supplier 1 (with A =
$250 and h = $7) and Supplier 2 (with A = $400 and h = $6) and
Supplier 3 (with A = $800 and h = $3), which supplier would give
you the lowest TOTAL COST value? HIGHLIGHT in yellow the three
corresponding cells in your two-way Data Table.

Question 2 (You will be able to do this problem after our
session on Monday 11/28) There are 59 students in our class, each
having a birthday, let’s say, between 1 and 365. I guess you don’t
know most of your classmates’ birthdays, so these numbers look
random to you! Create an Excel-based simulation model and use a
Data Table to run 1000 trials of your model, in order to answer the
following question:

“What is the probability that each person’s birthday in our
classroom is unique?” Use the “Hide Rows” feature in Excel to hide
trial #’s 11-995 your Data Tables for the trials, so you end up
showing only about 15 of the 1000 trials for each question.