Evaluate profit for a range of values for number of copiers

Question

Assignment:

Data Assignment: Creating a Spreadsheet Model to Calculate...

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 and several possible levels of demand. Finally, you will modify the spreadsheet model by adding user-friendly form controls. This will allow the end-user to consider a variety of scenarios for decision variables, uncertain variables, and given inputs.

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, data tables, and profit calculator form using the Assignments feature in Canvas. You can organize the spreadsheet as you see fit, but make sure the spreadsheet(s) are organized in such a way that I can easily see the work you've done for each part. Parts 1 and 2 will be worth 15 points each and Part 3 will be worth 20 points.

Background Information: (based on #14 on page 36 of the 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.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.

Assignment/Activity:

Part 1 - Make 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.

1. Before you get started on the actual spreadsheet, think about each of the pieces of information that you've been given:
2. What should the company's objective be?
3. What is the decision variable for this company?
4. Are there any uncertain variables (any variables you are estimating without certainty)?
5. 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.

1. Remember that the calculations in the workspace and objective sections of the spreadsheet should be formulas, not just values you typed in.
2. 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 - Data Table to Evaluate Profit

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. To extend the problem even further, now we will evaluate our decisions based on a variety of possible demands.

1. Two-way data table to evaluate profit for a range of values for the number of copiers and the estimated demand for copies. In the rows you should evaluate 2 up to 8 copiers. In the columns you should evaluate 500 up to 2000 copies for daily demand (counting by intervals of 250 copies).

• Use conditional formatting to highlight positive profit values in green and negative profit values in red.
• Based on your analysis, is there a level for demand for which the copy shop should just shut down and close the business?
• At the beginning of this problem we stated that the copy shop rented 3 copiers. If that statement is true, what level of demand does the copy shop need to encounter to break even. You do not need to perform new calculations here, just state the levels of demand where profits shift from negative to positive!

Part 3 - Use Form Controls to Evaluate Profit

1. User-friendly form that allow the end user to evaluate different levels of copiers and demand, along with changing some of the initial parameters from the problem.

• Form control to allow the user to consider several possible values for the number of copiers rented. The form should allow the user to consider from 2 up to 8 copiers rented.
• Form control to allow the user to consider several possible values for the daily demand for copies. The form should allow the user to consider from 500 up to 2000 copies as values of daily demand, measured in intervals of 250 copies.
• Form control to allow the user to consider several possible values for the price of copies. The form should allow the user to consider values from $0.05 to $0.15, measured in $0.01 increments.
• Important: At least one of the form controls you design in a), b), or c) should be a Combo Box or a List Box.
• Output: On the same sheet as the form controls that you have created, you should use the spreadsheet model to calculate the following annual values: Total Copies Sold, Total Revenue, Cost of Copier Rental, Cost of Store, Cost of Copies, Total Profit.

A few notes and hints (on next page):

In Part 1 (and each part after), make sure you have some mechanism in the spreadsheet model that determines the actual amount of copies sold based on both estimated demand and max production. In other words, you can not sell copies above max production even if you have enough demand or vice versa.

In Part 1, feel free to change the values for the given inputs to see what happens to profit. This is the great thing about spreadsheet models and data tables - that you can quickly run through a ton of hypotheticals for the business and evaluate the impact it would have on your decisions and results. It will also give you a sense of what you will be trying to do in Parts 2 and 3.

In Part 3: this would be a common exercise that you would go through before deciding to open a business. First you calculate profits under your most likely forecast. Next you make a decision on levels of production based on your profit forecasts. Then you evaluate these decisions under a variety of values for your forecast in case your forecast was wrong. Finally, you will have to make a decision on the actual number of copiers to rent based on your forecast. Once that is done you can't undo the decision even if your forecast is wrong.

Therefore, it is worth calculating a break even value to figure out the minimum level of demand that is required to make the business successful for the number of copiers you rented.