Module $2$ Data Assignment:

Creating a Spreadsheet Model to Calculate Profit

Objective: 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.

What you need:

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. 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 problem #$14$ on page $36$ of your textbook$)$

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 Details:

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?

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 must be formulas, not just values you typed in$.$