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) 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 I - 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. 1. 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? 2. 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. 3. 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. Part 3 To extend the problem even further, now we will evaluate our decisions based on a variety of possible demands. 5. Create a 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 columns you should evaluate 0 up to 8 copiers. In the rows you should evaluate 0 up to 2000 copies for daily demand (counting by intervals of 250 copies). a. Use conditional formatting to highlight positive profit values in green and negative profit values in red. b. Based on your analysis, is there a level for demand for which the copy shop should just shut down and close the business? c. 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! A few notes and hints: - 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. - In part 1 (and each part after), 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. - 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