A manufacturer has contracted with its customers to manufacture and ship replacement parts within five working days of the end of the day they receive a replacement part order. The manufacturer pays a penalty to the customer of $50,000.00 per part that is late per day it is late; thus, if two replacement parts are both late 5 days, the manufacturer will pay a penalty of $500,000.00. The demand for these spare parts is not seasonal and past demand information can be found in the DEMAND sheet of Assignment3.Part1.xlsx.
To supply these parts to their customers when needed, the manufacturer must custom manufacture the parts using one package of raw material per replacement part. These raw materials may either be in the manufacturers.
warehouse, or they can be ordered from a supplier. Once they have the packages of raw material in the house, it takes one day for the manufacturer to manufacture, package and ship the replacement parts, but due to equipment capacity limitations, they can only manufacture, package, and ship 5 replacement parts per day. Though each replacement part uses exactly one package of raw material, it does not make sense for the manufacturer to keep an inventory of finished replacement parts because these parts are different for each customer and they have many customers. The cost of ordering packages of raw material from the supplier is $200 per order and the cost of shipping each order of the raw material from the supplier to the manufacturer is $20,000.00 because they need to ship it via a leased airplane. Note that the cost of shipping the finished replacement parts is paid for by the customer.
Lead time data for the last 100 orders from the supplier can be found in the SUPPLIER LEAD TIME sheet of Assignment3.xlsx. Orders are sent out by the manufacturer to the supplier at the end of the day and are delivered to the manufacturer at the end of the day. The manufacturer starts with 10 packages of raw material and a three-day-old order for 2 replacement parts, a two-day-old order for 3 replacement parts, and a one-day-old order for 2 replacement parts. Note that you must always manufacture, package, and ship the oldest replacement part orders first. Also, note that the manufacturer has never been more than 10 days late.
Though the manufacturer has plenty of space for storing the packages of raw material, the holding costs for storing the raw material are high, i.e. $200 per unit per day, because of the cost of the raw material.
You have been asked to build a spreadsheet Monte Carlo simulation model to determine the optimal ordering policy for the packages of raw material needed to minimize costs, including late delivery penalty costs, over a year. Specifically, you need to:
• On the DEMAND sheet, build and create a range name for a vertical or horizontal lookup table that you can use to randomly generate the number of replacement parts ordered by customers each day. The range name for the table should be Ordered_Parts_Per_Day_Cumulative_Probability_Table. Make sure to include 0 parts ordered on a day as an option. Note that days with 0 ordered parts may or may not be in the demand datasheet. On the same sheet, briefly explain your calculations for the sheet.
• On the SUPPLIER LEAD TIME, build and create a range name for a vertical or horizontal lookup table that you can use to randomly generate the number of days it takes the supplier to fulfill each order (assuming that the lead time is not a function of the order size). Name the table.
Lead_Time_Cumulative_Probability_Table. Briefly explain your work.
• Label and create range names for all parameters and decision variables in a sheet named MODEL.
• Assume that the optimal policy is to reorder when the sum of the number of packages on hand, plus the number of packages on order at the end of the day, less the number of unfilled replacement part orders at the end of the day, is at or below a reorder point. When ordering, the quantity ordered should be equal to the difference between the order up to point and the quantity used to determine whether to order. Using those assumptions build a simulation model of the ordering process for 250 working days in the sheet named MODEL. Briefly explain (on the same sheet) each of the components of one typical row of the model.
• Replicate that model 40 times and use the replicated model to determine the average cost of one year, including ordering costs, shipping costs, and penalty costs, while keeping in mind available raw material packages and production limits. Label and create range names for these values.
• Build a 2-dimensional data table in the MODEL sheet to the right of the model, to determine the optimal reorder point/order up to point combination given the manufacturer’s constraint. Highlight in pink a range of very good solutions using conditional formatting and suggest and label a specific solution. Briefly explain your work and your answers.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
