ALY 6050 Northeastern University Inventory Management Decision Model Project
Description
Submissions should consist of an Excel workbook and a Word document. Please attach
both files when submitting the project. Part I of this project should be completed in
Excel and R script files are not accepted. Using R for Part II is optional. In case R has
been used for part II, the assignment will consist of 3 submissions: (i) the word document, (ii)
The Excel workbook for part I, (iii) the R script file (with extension .R) of part II.
3. In the Word document, write a report summarizing the results obtained.
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:
-
Define the data, uncontrollable inputs, model parameters, and the decision variables that
influence the total inventory cost. -
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. - Implement your model on an Excel spreadsheet. R script files are not accepted.
- Use data tables to find an approximate order quantity that results in the smallest total cost.
- Plot the Total Cost versus the Order Quantity
- Use the Excel Solver to verify your result of part 4 above.
-
Conduct what-if analyses by using two-way tables in Excel to study the sensitivity of total cost to
changes in the model parameters. - In the word document, explain your results and analysis to the vice president of operations.
Part II
This part may be completed in either Excel or in R. In case R has been used for this part, the assignment
will consist of 3 submissions: (i) the word document, (ii) The Excel workbook for part I, (iii) 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 mode of 15000 units.
-
If using Excel, 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. Next, use the results of your
simulation to:- (i) Estimate the expected minimum total cost by constructing a 95% confidence interval for
it and determine the probability distribution that best fits its distribution. Verify the
validity of your choice. - (ii) Estimate the expected order quantity by constructing a 95% confidence interval for it
and determine the probability distribution that best fits its distribution. Verify the
validity of your choice. - (iii) Estimate the expected annual number of orders by constructing a 95% confidence
interval for it and determine the probability distribution that best fits its distribution.
Verify the validity of your choice.
- (i) Estimate the expected minimum total cost by constructing a 95% confidence interval for
-
In In the word document, explain your results and analyses to the vice president of
operations.
Needs help with similar assignment?
We are available 24x7 to deliver the best services and assignment ready within 3-4 hours? Order a custom-written, plagiarism-free paper
Order Over WhatsApp Place an Order Online