Inventory management consultant

Reference no: EM13335855

Question Set 1.

You are an inventory management consultant. You have been hired by Trader Joe’s to help optimize their inventory reordering points for pineapples. Specifically, you will set up a Monte Carlo simulation to find profit-maximizing reorder points for pineapples. The daily demand distribution for pineapples is:

Probability Demand

0.05 25

0.1 30

0.2 35

0.15 40

0.25 45

0.2 50

0.05 55

Trader Joe’s is open every day and may order pineapples on any day. Working days are considered to start at 8am and last for 24 hours. Orders are placed between 4am and 5am and arrive between 6am and 7am the same day. The cost of an order is assessed on the working day it is placed; an order placed at 4:30am Friday morning would have the cost assessed on Thursday, since the new working day starts at 8am Friday. Orders may be placed for boxes of 100 pineapples at a shipping cost of $20. The store pays $150 per box and sells each pineapple for $3.49. At the end of each day, pineapples may spoil and need to be thrown out. We will use the following simplified spoilage formula. If the day’s ending inventory is in cell E10, the spoilage formula would be “=INT(RAND()*E10*0.3 + 0.5)”.

1. Build a table for the cumulative probability distribution for demand. It should be set up for use with the VLOOKUP function and have the cumulative probabilities on the left. (5pts)

2. Build a table for tracking sales and inventory of pineapples for four weeks (Sunday through Saturday). Assume inventory on the first day is 50 pineapples. For order quantities, assume an order (for 100 pineapples) is placed if inventory after spoilage is less than 45. Your table will need columns for:

• Beginning Inventory

• Demand (randomly found from the distribution in 1., above)

• Sales

• Ending Inventory

• Spoilage

• Order Size

Remember, sales should be the smaller of beginning inventory and demand. Spoilage should be applied after ending inventory is calculated. Order size should consider the inventory level after spoilage is applied; the person ordering checks for spoilage immediately before placing the order. (15pts)

3. Build a table for tracking costs and revenues for the four weeks’ pineapple sales. This should use the information provided in your answer to 2., above. Your table will need entries for:

• Revenue

• Goods Cost (based on each day’s orders)

• Shipping Cost ($20 for each time an order is placed)

• Profit/Loss (revenue minus costs)

When calculating profit (or loss) for the four weeks, you can ignore the first day’s initial inventory and the last day’s ending inventory. (10pts)

4. Create a cell with the total profit/loss for the four weeks and color it orange. Create a data table (as was done in lab 9) showing the profit/loss result of 100 simulation runs. Create a cell with the average total profit for the 100 runs and color it green. As in previous assignments, this must be an actual data table structure to earn full credit. (10pts)

Reference no: EM13335855

Questions Cloud

What are the dangers of overemphasizing or denying : What are the dangers of overemphasizing or denying either the deity or humanity of Christ, and what are the errors to avoid?

Describe three business safegaurds : Describe three business safegaurds ( risk management ) that may have been prevented the harm caused by Mr. Madoff

Social media and new forms are continuously emerging : The Internet has provided many forms of social media and new forms are continuously emerging. Which forms of social media do you use on a regular basis and why? Are there any social media that you previously used but no longer use? If so, explain why..

Determine the adjusted cash balance per bank : At July 31, Kuhlmann Company has the following bank information: cash balance per bank $7,420, outstanding checks $762, deposits in transit $1,120, and a bank service charge $20. Determine the adjusted cash balance per bank at July 31.

Inventory management consultant : You are an inventory management consultant. You have been hired by Trader Joe’s to help optimize their inventory reordering points for pineapples. Specifically, you will set up a Monte Carlo simulation to find profit-maximizing reorder points for pin..

Distance-language-culture-time zone and technology : Using real world global organizations as examples for global teams, prepare a report on an organization can best benefit from a global team, including how the organization can provide development and support for global teams. Be sure to address the i..

Economics theory and a market structure table : Using Mcdonalds as an organization identify the market structure of that organization. Describe the characteristics of the organization that make it a specific market structure. Evaluate the effectiveness of this structure for the organization while ..

Visual-fit method based on each potential cost driver : Cedar Rapids Implements Company produces farm implements. Cedar Rapids is in the process of measuring its manufacturing costs and is particularly interested in the costs of the manufacturing maintenance activity, since maintenance is a significant mi..

Manufacturer of telephone and communications devices : Phonetronix is a small manufacturer of telephone and communications devices. Recently, company management decided to investigate the profitability of cellular phone production. They have three different proposals to evaluate.

User Account

All Pages