Using optimized modeling techniques to solve business analytics case studies and scenarios

Answer

Deliverable: (CO 1) Evaluate various mathematical modeling techniques

You are thinking of opening a small copy shop. It costs $5,000 to rent a copier for a year and it costs $0.03 per copy (including paper) to operate the copier. Other fixed costs of running the store will amount to $400 per month. You plan to charge an average of $0.10 per copy, and the store will be open 365 days per year. Each copier can make up to 100,000 copies per year.

• Using the file entitled BIAM400-Milestone 1 Spreadsheet in the Course Project Materials folder, please complete parts a, b, and c:
• Show all of your work for each step. You will need to copy and paste from the Excel spreadsheets for each step to show your work.

Show (copy and paste) here:

1. For one to five copiers rented and daily demands of 500, 1,000, 1,500, and 2,000 copies per day, find annual profit. That is, find annual profit for each of these combinations of copiers rented and daily demand using a What-if analysis. Use conditional formatting to highlight negative numbers and positive numbers in the table.

What If Analysis:

Place Solution here:

Part (b) Create a Break-even example.

Place Screen print of Break-even Analysis here:

Part ( c) Write your observations of what the data in the two-way table means and why this is important analysis information.

Write response here:

30 points (4 points per question)

Deliverable: (CO 2) Analyze model components for optimization

1. What are Decision Variables?
2. What is an objective cell?
3. What does the condition of nonnegativity require?
4. If a manufacturing process takes 4 hours per unit of x and 2 hours per unit of y and a maximum of 100 hours of manufacturing process time are available, then an algebraic formulation of this constraint is:
5. Why is it important to use color coding when working on an optimization model?
6. Suppose a company sells two different products, x and y, for net profits of $6 per unit and $3 per unit, respectively. The slope of the line representing the objective function is:
7. Explain what a break-even analysis is and how it is used.
8. Explain why it is important to name cells and ranges in an optimization model.
9. Give an example of how conditional formatting can be used.

Week 5 - Milestone 2- CO 3, CO 4, CO 5- due in week 5

Total points 100: points

25 points (2.5 points per question)

Deliverable: (CO 3) Construct Network Models and interpret results

• IMPORTANT NOTE: For question #3 (CO 3), please use the xlsx file located in the Course Project Materials Folder to answer the questions below.

1. Problems which deal with the direct distribution of products from supply locations to demand locations are called:

1. The objective in transportation problems is typically to:

1. A particularly useful Excel function in the formulation of transportation problems is the:

1. The decision variables in transportation problems are:

1. For all routes with positive flows in an optimized transportation problem, the reduced cost will be:

1. From the file model, under cost, why are the numbers for Detroit, Dallas, and Max per route shaded blue?

1. In a typical minimum cost network flow model, what do the nodes represent?

1. Why is total cost highlighted in grey?

1. Can Solver be used to find an optimal solution?

1. List examples of two constraints that can be used in Solver.

50 points

Deliverable: (CO 4) Construct decision trees and interpret the results

A farmer must decide whether to take protective action to limit damage to his grapefruit crop in the event that the overnight temperature falls to a level well below freezing. If the temperature drops too low he runs the risk of losing his entire crop, valued at $75,000. Based on the National Weather Service, the probability of such a temperature drop is 60%. He can insulate his crop by spraying water on all the trees, which will cost $20,000. This action might succeed in protecting the crop, with the following possible outcomes:

1. What should the farmer do?
2. Why?

Here is the data and the partially completed decision tree. Using Palisade Precision Tree software, build the Decision Tree and use the results to answer the questions above. (Make sure you designate labels and formulas for the decision tree).

25 points

Deliverable: (CO 5) Perform sensitivity analysis and interpret results

Please complete the following questions after the completing the analysis.

1. The primary difference between simulation models and other types of spreadsheet models is that simulation models contain:
2. List two important distinctions of probability distributions
3. Discrete distributions are sometimes used in place of continuous distributions because:
4. The RAND () function in excel models is what probability distribution?
5. Explain why @Risk charts are useful.
6. Does this chart below represent a normal bell curve? Why or why not?

1. Use the chart below to answer the next two questions:

1. Is this chart a normal bell curve?
2. Is this chart skewed? If so in what direction?

Week 8 - Milestone 3 (CO 6 and CO 7) and complete the following:

CO 6 Deliverable (See Below)

CO 7 Deliverable (See Below)

Lessons Learned (Add this written section)

Conclusions (Add this written section)

Recommendations (Add this written section)

Bibliography (Add this written section)

50 points

Deliverable: (CO 6) Classify various models and their characteristics

1. List one of the important issues that defines types of arrivals in a queuing system:

1. When a customer already in line in a queuing system becomes impatient and leaves the system before starting service, this is called:

1. In the diagram below will changing simulation warmup time and simulation run time alter results? In what ways?

1. List and explain two types of service disciplines?

1. A queuing system where customers join a single line and then are served by the first available server are said to be:

1. A requirement for steady state analysis of a queuing system is that:

1. The appearance or description of an exponential distribution is:

1. Regression analysis is:

1. List at least two assumptions you can make for the chart below?
2. Explain at least two observations you can make from the chart below.

50 points

Deliverable: (CO 7) Perform regression analysis to determine a course of action intended to deal with a business problem

Howie's Bakery is one of the most popular bakeries in town, and the favorite at Howie's is French bread. Each day of the week, Howie's bakes a number of loaves of French bread, more or less according to a daily schedule. To maintain its fine reputation, Howie's gives to charity any loaves not sold on the day they are baked. Although this occurs frequently, it is also common for Howie's to run out of French bread on any given day—more demand than supply. In this case, no extra loaves are baked that day; the customers have to go elsewhere (or come back to Howie's the next day) for their French bread. Although French bread at Howie's is always popular, Howie's stimulates demand by running occasional 10% off sales.

Howie's has collected data for 20 consecutive weeks, 140 days in all. These data are listed in the file BIAM400Milestone3CO7 located in the Course Project Materials file. The variables are Day (Monday-Sunday), Supply (number of loaves baked that day), OnSale (whether French bread is on sale that day), and Demand (loaves actually sold that day). Howie's wants to see whether regression can be used successfully to estimate Demand from the other data in the file. Howie reasons that if these other variables can be used to predict Demand, then he might be able to determine his daily supply (number of loaves to bake) in a more cost-effective way.

• Using Microsoft Excel the tabs in BIAM400Milestone3CO7 file. Answer the following questions.

1. How successful is regression with these data?

1. Is Howie correct that regression can help him determine his daily supply?

1. Is any information missing that would be useful? How would you obtain it? How would you use it? Is this extra informationreally necessary?

To complete Milestone 3, please see Lessons Learned, Conclusions, Recommendation, and Bibliography. See headings below.

Lessons Learned (Completed as part of Milestone 3)

Conclusions (Completed as part of Milestone 3)

Recommendations (Completed as part of Milestone 3)

Bibliography (Completed as part of Milestone 3)