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 15,000 units and is assumed to be constant throughout the year. Each

unit costs $80. The company’s accounting department estimates that its opportunity cost

for holding this item in stock for one year is 18% of the unit value. Each order placed with

the supplier costs $220. The company’s policy to order whenever the inventory level

reaches zero. Assume that the inventory will be replenished immediately after order in this

case.

Note that the company is also required to ensure that all the demand is met and demand is

known ahead of time.

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. Use them to

develop a mathematical model for the total cost (ordering cost and holding 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 (e.g., opportunity cost, order cost,

etc.).

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 14000 and 17000 units

with a peak of 15000 units.

1. Perform a simulation consisting of 30 occurrences, and calculate the minimum total

cost for each occurrence in Excel. If using R, perform a simulation consisting of 1000

occurrences, and calculate the minimum total cost for each occurrence.

2. Examine probability distribution for the minimum total cost.

3. Examine probability distribution for the order quantity.

4. Examine probability distribution for 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 setup 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 a 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