excel S

$24$

$$Murphy BUAD

$346$

Case

$2$

$-$

$$Simulation

Instructions for Completion:

SIMULATION WORK

$1$

$.$

$$Complete a Static Forecast and Simulation Forecast in Excel

$($

not Google Spreadsheet

$)$

$$using the information

provided and the Excel spreadsheet template that accompanies this document. Do NOT create new

assumptions or a new approach

$/$

format to the spreadsheets! You must use RAND, and VLOOKUP or IF

functions in Excel to complete your work; you cannot manually enter data.

$2$

$.$

$$Save your completed Excel Spreadsheet with the file name:

LName

$1$

LName

$2$

Case

$2$

Simulation

$($

example: SkywalkerSoloCase

$2$

Simulation

$)$

CASE DOCUMENTATION

$/$

QUESTIONS

$1$

$.$

$$Edit

$/$

replace cover page

$($

attached

$)$

$$to include partner names

$2$

$.$

$$Answer questions

$($

attached

$)$

$3$

$.$

$$Save in WORD format

$($

ONLY

$)$

$$with the file name:

LName

$1$

LName

$2$

Case

$2$

Answers

$($

example: SkywalkerSoloCase

$2)$

UPLOAD VIA CANVAS

$1$

$.$

$$Upload the

$2$

$$files to Canvas no later than Weds, May

$1$

$$at

$6$

pm EST. Late submissions will have points deducted no

matter how late; maximum late days allowed is

$2$

$.$

$$Only one submission is required for partnership. You can decide

who will submit.

Grading Rubric:

$50$

$$points:

Static Forecast correctness

$5$

$$pts

Simulation Forecast Performance

$($

across required periods

$)10$

$$pts

Formula Logic and Automated Performance in Excel

$5$

$$pts

Q&A

$15$

$$pts as noted

Spelling

$/$

Grammar

$5$

$$pts

Timely Submission

$5$

$$pts

Followed Instructions re: Submission

$/$

Cover Sheet

$/$

File Names

$/$

Email Subject

$5$

$$pts

Reminder About Work Effort:

$$

$$You are expected to work on this case with your partner. However, working with other students or discussing

methodologies is not permitted.

$$

$$I will review your Simulation file for correctness and logic AND I will use the

$$

View Formulas

$$

$$function to audit the

formulas you use throughout

$($

if

$/$

then or VLOOKUP

$)$

$.$

$$If you do not use formulas and enter data manually, you will

lose

$5$

$$points

$($

see Formula Logic above

$)$

$.$

FORMAT REQUIREMENTS FOR TYPED ANSWERS:

$$

$$Font: Calibri

$11$

$$

$$Line Spacing:

$1\mathrm{.}5$

$$

$$Type answers directly below Q as directed

$$

$$Save as WORD document per instructions

BACKGROUND

Create Factory

$($

CF

$)$

$$is a maker facility that offers access to machinery and equipment for production of various items

including woodcrafts, clothing and furniture. The facility is open from

$9$

$-$

$6$

$$every day

$($

last reservation for each day is from

$5$

$-$

$6)$

$.$

CF management is considering the installation of THREE

$(3)$

$$jewelry production stations in their facility. The one

$-$

time

cost for this implementation is $

$10$

$,$

$000$

$.$

Each jewelry station would be available for rent in

$60$

$-$

minute intervals. CF earns a station reservation fee of $

$25$

$$per

$60$

$-$

minute session; clients can purchase

$1$

$$or more basic supply kits of materials for $

$20$

$$per kit

$($

optional

$)$

$.$

$$Additional costs

are noted below.

CF plans to accept advanced reservations for the jewelry stations but will also accommodate walk

$-$

in clients if any

stations are free.

CF wants to simulate

$5$

$$days of business to determine approximate revenue, costs, and net profit.

DATA

$/$

PROBABILITIES

Reservation Probabilities Revenue

$/$

Cost Information

# Reservations

Received

$/$

Hour Probability

$1\mathrm{.}45$

$2\mathrm{.}35$

$3\mathrm{.}2$

Walk In Probabilities

# Walk Ins

$/$

Hour Probability

$1\mathrm{.}64$

$2\mathrm{.}23$

$3\mathrm{.}08$

$4\mathrm{.}04$

$5\mathrm{.}01$

Materials Kits Purchased

# Kits Sold Probability

$0\mathrm{.}22$

$1\mathrm{.}38$

$2\mathrm{.}25$

$3\mathrm{.}15$

Reservation

$/$

Hour $

$25$

Materials

$($

per supply kit sold

$)$

$$$

$20$

Lost Goodwill

$($

for every walk

$-$

in

client turned away

$)$

$

$5$

Excess Capacity Cost

$($

for every

period when capacity goes

unused

$)$

$

$15$

SIMULATION

$/$

ANALYSIS REQUIREMENTS

STATIC FORECAST WORKSHEET

$1$

$.$

$$Using the S

$24$

$$BUAD

$346$

$$Case

$2$

$$Excel File, open the STATIC FORECAST tab and manually enter data to create a static

forecast for

$5$

$$BUSINESS DAYS using the assumptions stated. Determine the TOTAL REVENUE EARNED.

$2$

$.$

$$Create

$/$

complete an Excel simulation for

$1$

$$business week

$(5$

$$days

$)$

$$using the data

$/$

format in SIMULATION FORECAST

tab.

a

$.$

$$You must generate your own random variables for each element using the RAND function. Note: Once you

have generated your random #s

$,$

$$you will need to

$$

set

$$

$$them$,$ so they don

$$

t regenerate with every

spreadsheet entry you make. You can do this by selecting

$/$

copying your random number data and special

paste using the VALUES paste option.

b

$.$

$$You must use IF

$/$

THEN or VLOOKUP functions to perform this work.

$3$

$.$

$$Answer Qs as found in within this document.

$4$

$.$

$$Submit all files via Canvas upload by the due date

$/$

time

$.$

$$Failure to do so will result in points lost.

S

$24$

$$BUAD

$346$

$-$

Murphy

Case

$2$

$$Submission

Due: Weds, May

$1$

$$at

$6$

pm EST

Submitted by:

S

$24$

$$BUAD

$346$

Case

$2$

$$Questions

You must include the question with your answer. Please type the answer in the space provided.

$1$

$.$

$$STATIC FORECAST

$(2$

$$pts

$)$

:

$$

$$How much net revenue does CF earn via?

$$

$$On average, how many materials kits are sold?

$$

$$On