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:

  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 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.

  1. 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:

    1. (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.
    2. (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.
    3. (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.
  2. 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

Do you have an upcoming essay or assignment due?

All of our assignments are originally produced, unique, and free of plagiarism.

If yes Order Similar Paper