Part 1: Model Set-Up Yearly Output 1

Part 1: Model Set-Up
Yearly Output
1.
Вопрос 1
As part of setting up the model, you need to figure out how many copies your shop will make per year. The screenshot below shows part of a possible model.

• 
  Cell B3 contains the number of copiers rented (one of the variables you want to experiment with).
  
• 
  Cell B4 contains the maximum number of copies that the shop could make in a year. This is given by 100000*B3.
  
• 
  Cell B6 contains the number of copies demanded daily (another variable that you want to experiment with).
  
• 
  Cell B7 contains the annual demand, given by 365*B6.
  

There are two possible formulas that you could use in Cell B9 to get the number of copies that the shop will actually make per year.
Select both formulas.

1 балл
=MAX(B7,B4)
=IF(B7>B4,B4,B7)
=IF(B7>B4,B7,B4)
=MIN(B7,B4)
Costs and Revenue

2.Consider the sample model shown in the spreadsheet.

In the spreadsheet:

• 
  Cell B4 contains the maximum number of copies that the shop can make in a year
  
• 
  Cell B7 contains the yearly demand
  
• 
  Cell B9 contains the number of copies made per year
  
• 
  Cell B12 contains the fixed monthly cost of running the copy shop ($400)
  
• 
  Cell B14 contains the yearly cost of renting one copier ($5000)
  
• 
  Cell B15 contains the operating cost per copy ($0.03)
  

What formula could you enter in cell B17 to find the total annual cost of running the copy shop?
1 балл
=12*B12+B3*B14+B4*B15
=12*B12+B3*B14+B9*B15
=12*B12+B14+B9*B15
=12*B12+B3*B14+B7*B15

Вопрос 3
Consider the sample model shown in the spreadsheet.

In the spreadsheet:

• 
  Cell B4 contains the maximum number of copies that the shop can make in a year
  
• 
  Cell B7 contains the yearly demand
  
• 
  Cell B9 contains the number of copies made per year
  
• 
  Cell B20 contains the selling price per copy ($0.10)
  

What formula could you enter in cell B22 to find the total annual revenue of the copy shop?
1 балл
=B7*B20
=B9*B20
=B4*B20
Data Table
To investigate how the annual profit is affected by the number of copiers you rent and the daily demand, you want to create a two-way data table. Specifically, you want to determine the annual profit for one to five copiers rented and daily demands of 500,1000,1500, and 2000 copies per day.
In the spreadsheet shown, how would you fill in the blanks in the Data Table window?

In this sample spreadsheet,

• 
  Cell B3, color-coded in green, contains the number of copiers rented.
  
• 
  Cell B6, color-coded in green, contains the daily demand.
  
• 
  Cell B24 contains the total annual profit.
  

4.What would be the row input cell for the two-way data table in the spreadsheet shown above?
1 балл
B12
B6
B3

Вопрос 5
What would be the column input cell for the two-way data table in the spreadsheet shown above?
1 балл
B6
B12
B3

Part 2: Analysis
Suppose you decide to rent three copiers. You want to determine the daily demand for copies that will allow the shop to break even in a year.
Вопрос 6
C
onsider the sample spreadsheet below.

In the spreadsheet:

• 
  Cell B3, color-coded in green, contains the number of copiers rented
  
• 
  Cell B6, color-coded in green, contains the daily demand
  
• 
  Cell B20 contains the selling price per copy ($0.10)
  
• 
  Cell B24 contains the total annual profit
  

How could you fill in the Goal Seek prompts to find the break-even point?
1 балл
Set cell: $B$24 To value: 0 By changing cell: $B$6
Set cell: $B$24 To value: 0 By changing cell: $B$3
Set cell: $B$24 To value: 0 By changing cell: $B$20

7.If you rent three copiers, what daily demand for copies will allow you to break even?
1 балл
775 copies
774 copies
780 copies
1000 copies

8. Graph profit as a function of the number of copiers (one to five) for a daily demand of 500 copies; for a daily demand of 1000 copies; for a daily demand of 1500 copies; for a daily demand of 2000 copies. Interpret your graphs. Then choose the most accurate statement below.
1 балл
If the daily demand is 1500 copies or greater, then profits will increase as you rent more copiers. Otherwise, profits will start to decrease as you rent more copiers. More information about the daily demand is needed before deciding how many copiers to rent.


For any daily demand, profits will always increase as you rent more copiers. It will be best to rent 5 copiers.


For any daily demand, profits will initially increase as you rent more copiers, but then start to decrease as you rent more copiers. It will be best to rent 2 or 3 copiers.