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.

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 average, how many walk$-$in clients are turned away?

$$ On average, how much excess capacity does CF expect to have?

Answer:

$2.$ SIMULATION FORECAST $(4$ pts$)$:

$$ How much net revenue does CF earn via?

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

$$ On average, how many walk$-$in clients are turned away?

$$ On average, ho