How-to-set-up-model-in-Excel-using-working-formulas

• Sewing Worth, Inc. (SWI) would like to develop an inventory policy that will minimize the total cost associated with the company’s inventory of sewing machines. The type of inventory policy they prefer involves a fixed Reorder Point and Order Quantity. The reorder point is the level of inventory at which an order is sent to the supplier. The order quantity is the amount ordered each time. These are decisions, but once selected, they will remain constant for the entire year (are not changed on a week-by-week basis). The current inventory policy is a Reorder Point of 1 unit, and an Order Quantity of 5 units. At this time, the company has a beginning inventory of 5 units. SWI faces an uncertain demand for these sewing machines each week, as shown in the following table:
  • Run the simulation model using the current decision values.
    • Copy and paste the charts, with statistics, for each Forecast to a new spreadsheet in your workbook (label the sheet ‘2a Charts’).
  • Using the model developed in Part a, SWI would like to determine if a better inventory policy can be found by using a Decision Table (you do not need to create a second version of the model to perform this task). They would like to look at changing the two Decision as follows: Order Quantity has a range from 1-20 (discrete), and Reorder Point has a range from 1-10 (discrete). Create a Decision Table for the Total Cost Forecast developed in Part a. Use both Decisions to create the table; test 20 values for Order Quantity and test 10 values for Reorder Point. Copy and paste the table over to a new sheet within your HW2 workbook and label the sheet ‘2b’. Answer the following question about the report:
    • What is the optimal combination of Order Quantity and Reorder Point that will minimize the Mean Total Cost? Highlight the optimal value in the table with a yellow cell color.
  • Return to the model developed in Part a, but now set the Order Quantity and Reorder Point to the optimal values determined in Part b rather than the values provided in the problem description; then run the simulation model with these new values.
    • Copy and paste the charts, with statistics, for each Forecast to a new ‘2c Charts’ sheet in your workbook (make sure you clearly label/distinguish the two sets of charts).

Some relevant cost data is known. It costs $1 per week to hold each sewing machine in inventory and it costs $100 to place an order, regardless of the order size. When an order is placed it takes two weeks (assume the order is placed on the last day of the week if inventory is at or below the reorder point, and the order is received on the first day of the second week thereafter; thus there is a week that is skipped between order placement and receipt). The current estimated cost of a lost sale is $50. Build a simulation model for this problem. This will need to be a week-by-week model that utilizes beginning inventory and demand to determine sales, ending inventory, costs, and if an order is required to be placed (again, you should have all of these calculations for each week). Calculate the annual Total Cost (sum of ordering, lost sales, and holding costs) and annual Fill Rate (percentage of demand that is met with sales) to determine the impact of the inventory policy. Note: you will need to use some Excel functions to create this model.