Statistics

Decision Making

Overview and Rationale

This assignment is designed to provide the students with hands-on experiences in performing what-if scenarios and optimization techniques. You are provided with a business scenario and you are asked to create a model for decisions making. Then you are asked to use optimization techniques to optimize the efficiency of your model.

Read the scenario provided in the Decision Making assignment document. Complete Part1 in Excel. Use R to complete Part 2. Finally, write a report with your findings. Be sure to answer all questions presented in the assignment document.

Course Outcomes

This assignment is directly linked to the following key learning outcomes from the course syllabus:

CO1: Use descriptive, Heuristic and prescriptive analysis to drive business strategies and actions

CO4: Utilize applied analytics and definitions of measures of success to provide a strategic analytic roadmap for an organization.

CO5: Incorporate general industry practices in end-to-end analytics development cycles, including, data engineering, analytics modeling, optimization, (e.g. risk minimization) and strategic development;

Assignment Summary

An Inventory Management Decision Model

Inventories represent a considerable investment for every organization; thus, it is important that they be managed well. Excess inventories can indicate poor financial and operational management. On the other hand, not having inventory when it is needed can also result in business failure. The two basic inventory decisions that managers face are how much to order or produce for additional inventory, and when to order or produce it to minimize total inventory cost, which consists of the cost of holding inventory and the cost of ordering it from the supplier.

Holding costs, or carrying costs, represent costs associated with maintaining inventory. These costs include interest incurred or the opportunity cost of having capital tied up in inventories; storage costs such as insurance, taxes, rental fees, utilities, and other maintenance costs of storage space; warehousing or storage operation costs, including handling, recordkeeping, information processing, and actual physical inventory expenses; and costs associated with deterioration, shrinkage, obsolescence, and damage. Total holding costs are dependent on how many items are stored and for how long they are stored. Therefore, holding costs are expressed in terms of dollars associated with carrying one unit of inventory for one unit of time.

Ordering costs represent costs associated with replenishing inventories. These costs are not dependent on how many items are ordered at a time, but on the number of orders that are prepared. Ordering costs include overhead, clerical work, data processing, and other expenses that are incurred in searching for supply sources, as well as costs associated with purchasing, expediting, transporting, receiving, and inspecting. It is typical to assume that the ordering cost is constant and is expressed in terms of dollars per order.

For a manufacturing company that you are consulting for, managers are unsure about making inventory decisions associated with a key engine component. The annual demand is estimated to be 20,000 units and is assumed to be constant throughout the year. Each unit costs $90. The company’s accounting department estimates that its opportunity cost for holding this item in stock for one year is 15% of the unit value. Each order placed with the supplier costs $195. The company’s policy to order whenever the inventory level reaches a predetermined reorder point that provides sufficient stock to meet demand until the supplier’s order can be shipped and received; and then to order twice as many units.

Part I

Part I should be completed in Excel. R scripts are not accepted for part I.

As a consultant, your task is to develop and implement a decision model to help them arrive at the best decision. As a guide, consider the following:

1. Define the data, uncontrollable inputs, model parameters, and the decision variables that influence the total inventory cost.

2. Develop mathematical functions that compute the annual ordering cost and annual holding cost based on average inventory held throughout the year, and use them to develop a mathematical model for the total inventory cost.

3. Implement your model on an Excel spreadsheet. R script files are not accepted.

4. Use data tables to find an approximate order quantity that results in the smallest total cost.

5. Plot the Total Cost versus the Order Quantity

6. Use the Excel Solver to verify your result of part 4 above.

7. Conduct what-if analyses by using two-way tables in Excel to study the sensitivity of total cost to changes in the model parameters.

8. In the word document, explain your results and analysis to the vice president of operations.

Part II

This part must be completed in R. In case R has been used for this part, the assignment will consist of 3 submissions:

• The word document,

• The Excel workbook for part I,

• The R script file (with extension “.R”) of part II.

Assume that all problem parameters have the same values as those in part I, but that the annual demand has a triangular probability distribution between 15000 and 25000 units with a peak of 20000 units.

1. Perform a simulation consisting of 30 occurrences, and calculate the minimum total cost for each occurrence. If using R, perform a simulation consisting of 1000 occurrences, and calculate the minimum total cost for each occurrence.

2. Determine the probability distribution that best fits the minimum total cost.

3. Determine the probability distribution that best fits the order quantity.

4. Determine a probability distribution that best fits the annual number of orders.

5. In the word document, explain your results and analysis to the vice president of operations.

Format & Guidelines

Submissions should consist of 3 files; an Excel workbook, and R file and a Word document:

• The word document,

• The Excel workbook for part I,

• The R script file (with extension “.R”) of part II.

The report should follow the following format:

(i) Introduction

(ii) Analysis

(iii) Conclusion

And be 1000 - 1200 words in length, not including the title page, and presented in the APA format.

Rubric

Category Exceeds Standard Meets Standards Approaching Standards

Below Standards

Excel and R: Problem Modeling & Set-up ALY6050-CO1 ALY6050-C05

Completely and concisely modeled the problem in Excel (or R) for each method

Accurately modeled the problem in Excel (or R) for each method

Correctly modeled the problem in Excel (or R) for each method, but the model lacks detailed insight into the problem or the set- up is awkward.

Modeled the problem in Excel (or R) for each method, but there are some gaps in the problem modeling and setup

Excel and R: Problem Solution & Accuracy ALY6050-CO4 ALY6050-CO5

Efficiently obtained correct and accurate solutions in Excel (or R) by using the appropriate analytic tools of the software

Obtained complete and accurate solutions in Excel (or R) by using the appropriate analytic tools of the software

Obtained correct solutions in Excel (or R) using the appropriate analytic tools of the software, but the application of the tool is awkward.

Obtained solutions in Excel (or R) by using the appropriate analytic tools of the software, but the solution is not complete.

Word/Report: Problem Description & Introduction ALY6050_CO5

Provides a thorough and concise summary of the problem descriptions and introduced the problem using rich and significant ideas

Provides an accurate and succinct summary of the problem descriptions and problem introduction

Provides an accurate summary of the problem descriptions and problem introduction, but the description is too wordy or not succinct

Provided a summary of the problem descriptions and problem introduction, but it is inaccurate or incomplete

Word/Report: Description of Problem Analysis ALU6050_CO4 ALY6050_CO5

Provides a thorough and precise description of the analytic concepts and theories used in analyzing the problem

Accurately describes the analytic concepts and theories used in analyzing the problem

Describes the analytic concepts and theories used in analyzing the problem, but description lacks appropriate detail or precision

Describes the analytical concepts and theories used in analyzing the problem, but descriptions are incorrect or the analytical concepts and theories are incorrect

Word/Report: Description of Conclusions ALY6050_CO4

Provides conclusions and results obtained in the project using a high level of critical thinking and reasoning

Provides relevant conclusions and results obtained in the project that reflect critical thinking and reasoning

Provides conclusions and results obtained in the project, but not all conclusions or results are relevant to the problem or not

Provides conclusions and results obtained in the project, but they are irrelevant and reflect a lack of critical thinking

Category Exceeds Standard Meets Standards Approaching Standards

Below Standards

all conclusions reflect good reasoning

Word/Report: Writing Mechanics, Title Page, & References

Completely free of errors in grammar, spelling, and punctuation; and completely correct usage of title page, citations, and references. The report contains a minimum of 1000 words

There are no noticeable errors in grammar, spelling, and punctuation; and completely correct usage of title page, citations, and references. The report contains a minimum of 1000 words

There are very few errors in grammar, spelling, and punctuation; and completely correct usage of title page, citations, and references. The report contains a minimum of 1000 words

There are more than five errors in grammar, spelling, and punctuation; or the usage of title page, citations, and references are incomplete; or the report contains less than 1000 words