Using Excel build Inventory Model
Write a report follow instructions
See attached File
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 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 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 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