Week $6$ Data Assignment:

Creating a Spreadsheet Model to Calculate Profit

Objective: In this exercise you will design a spreadsheet model to calculate profit for a small copier company. You will build on that model by using data tables to evaluate several options for the decision variable. Finally, you will use a two$-$way data table to determine the optimal number of copiers for several levels of demand.

What you need:

You should have a computer with a working version of Microsoft Excel.

Your submission:

You should submit an Excel file with your spreadsheet model and data tables using the Assignments feature in Canvas. You can organize Parts $1,2,$ and $3$ all on one sheet or with each on their own sheet. Make sure the spreadsheet$($s$)$ are organized so that I can easily see the work you've done for each Part.

Background Information: $($based on #$14$ on page $36$ in AW text$)$

You are thinking of opening a small copy shop. It costs $$5000$ to rent a copier for a year, and it costs $$0\mathrm{.}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\mathrm{.}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.

Assignment$/$Activity:

Part $1-$ Create the Basic Spreadsheet Model

Based on your demand forecast, you estimate that you will be able to sell an average of $1,500$ copies per day. You have rented $3$ copiers.

Before you get started on the actual spreadsheet, think about each of the pieces of information that you've been given:

a$.$ What should the company's objective be$?$

b$.$ What is the decision variable for this company?

c$.$ Are there any uncertain variables $($any variables you are estimating without certainty$)?$

d$.$ What are the given inputs?

Before you start entering values in the spreadsheet, think about your overall plan for organizing the spreadsheet. Where will you put each of the different pieces of information

Before you start entering values in the spreadsheet, think about your overall plan for organizing the spreadsheet. Where will you put each of the different pieces of information.

Design a spreadsheet model to calculate profit for the company based on the information given, the number of copiers provided, and the estimated demand.

a$.$ Remember that the calculations in the workspace and objective sections of the spreadsheet should be formulas, not just values you typed in$.$

b$.$ To test whether your spreadsheet is working correctly, change the number of copiers to $4.$ Does profit change the way you expect? Now change it to $2$ copiers. You can do the same kind of testing on the demand variable.

Part $2$

In reality, the company has the ability to choose the number of copiers to rent, so in this part of the problem you will use your spreadsheet model to determine how many copiers to rent. Continue to assume that demand is $1,500$ copies per day.

$4.$ Create a one$-$way data table to evaluate profit for a range of possible copier options. Evaluate $0$ to $8$ copiers.

a$.$ Use conditional formatting to highlight all possibilities that lead to positive profits in green and all possibilities that lead to negative profits in red.

b$.$ Based on the demand information provided, how many copiers should you rent for your company? Just make a note on the spreadsheet beside the quantity of copiers that maximizes profit.

Week $6$ Data Assignment:

Creating a Spreadsheet Model to Calculate Profit

Objective: In this exercise you will design a spreadsheet model to calculate profit for a small copier company. You will build on that model by using data tables to evaluate several options for the decision variable. Finally, you will use a two$-$way datp table to determine the optimal number of copiers for several levels of demand.

What you need:

You should have a computer with a working version of Microsoft Excel.

Your submission:

You should submit an Excel file with your spreadsheet model and data tables using the Assignments feature in Canvas. You can organize Parts $1,2,$ and $3$ all on one sheet or with each on their own sheet. Make sure the spreadsheet$($s$)$ are organized so that I can easily see the work you've done for each Part.

B$$ckground Information: $($based on #$14$ on page $36$ in AW text$)$

You are thinking of opening a small copy shop. It costs $$5000$ to rent a copier for a year, and it costs $$0\mathrm{.}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\mathrm{.}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.

Assignment$/$Activity:

Part $1-$ Create the Basic Spreadsheet Model

Based on your demand forecast, you estimate that you will be able to sell an ave