You are thinking of opening a small copy shop. It costs $5000 to rent a copier for a year, and it costs $0.03 per copy to operate the copier. Other fixed costs of running the store will amount to $4800 per year. You plan to charge a price of $0.10 per copy, and the store will be open 365 days per year. Each copier has a maximum capacity of 100,000 copies per year.

Objective: Simulation modeling allows businesses to collect information to make decisions in the face of uncertainty. Using a simulation model, a business can review the results of thousands of possible outcomes to evaluate the expected return and risk, along with best case and worst case scenarios.

Part A: Review the Spreadsheet Model

• I have created spreadsheet model for this problem. Review the key parts (additional calculations and profit calculations) and make sure you understand what each of the Excel formulas are doing in this problem, especially if the files you submitted for Week 2 were not correct!
• The best way to make sure you understand how the spreadsheet model works is to change the number of copiers from 3 to a few different values to see how the amount sold cell works.
• Notice that demand I have demand listed as 1500 like Part 1 of Week 2. Since demand is the uncertain variable, we will adapt this to allow it to follow some probability distribution.
  • Part A work

Given Information:

Prob Distribution for parts B and C

Copier Rental

$5,000.00

per year

Demand

Probability

Store Cost

$4,800.00

per year

500

0.05

Price

$0.10

per copy

1000

0.15

Variable Cost

$0.03

per copy

1500

0.25

Max Production per Copier

100000

copies

2000

0.4

Days Business is Open

365

days

2500

0.15

Decision Variable:

# of Copiers to Rent

3

copiers

Uncertain Variable:

Daily Demand

1500

copies per day

Workspace: Calculate Annual Copies Sold

Maximum Annual Production

300000

Estimated Annual Demand

547500

Actual Amount of Copies Sold

300000

Objective: Calculate Annual Profits

Revenue

$30,000.00

Store Costs

$4,800.00

Total Copier Rental Cost

$15,000.00

Total Variable Cost

$9,000.00

Profit

$1,200.00

Part B: Create Basic Simulation Model

• In reality, daily demand will not be 1500. It is an uncertain value that could range from 500 to 2500 based on the probability distribution provided. Assume that the company has selected the number of copiers to be 3.
• Create simulation model with 500 replications. In each replication, demand will be randomly drawn from the discrete probability distribution provided and profit must be calculated.
• HINT: You should be able to follow along on Tech Demo Videos from this week to create these simulations.
• I recommend using the data tables as it will make the rest of the assignment easier in my opinion!
• Report the mean, median, standard deviation, max, and min of the values of profit for your simulation. The videos I mentioned above will help you with this!

Part C: Use a Two-Way Table with a Simulation to Evaluate Possible Production Levels

Tasks

• The easiest thing to do is start with the simulation you created in Part B. You can copy and paste most of that to a new sheet to perform the calculations here.
• Using the probability distribution provided in the problem, create simulation model with 500 replications. The simulation model should use a two-way data table to evaluate different amounts of copiers. Test 2, 3, 4, 5, and 6.
• Calculate the mean, standard deviation, maximum, and minimum values of profit for each of the copier levels based on the simulation.

Part D: Create Simulation Model with a Normal Distribution

• In this part you will reproduce either work you did in parts B or in Part C
• Create simulation model with 500 replications. In each replication, demand will be randomly drawn from a normal distribution with mean 5,500 and standard deviation 2,300 and profit must be calculated.
• You can either produce a simulation model for a single level of copiers like 3, like you did in Part B, or produce a simulation using a two-way data table for various levels of copiers like you produced in Part C of this assignment.
• Calculate the mean, standard deviation, maximum, and minimum values of profit based on the simulation.