For this week’s assignment, you will propose (or illustrate) a database solution enabling service representatives to identify the most important customers so that they could receive priority service. You will use….

## Network Design-You have been hired by ABC company to serve as their Pellet Transshipment Manager. ABC company is a joint public-private energy company

# Question 1, Network Design (30 marks):

** **

You have been hired by ABC company to serve as their Pellet Transshipment Manager. ABC company is a joint public-private energy company dedicated to generating clean and safe nuclear energy. ABC uses a new technology based on radioactive pellets. These pellets can generate electricity for years in ABC ‘s nuclear plants. Once the radioactive pellets are depleted, they are removed from active use and collected at the plants for removal at the end of the month. Once a month, the depleted pellets are removed from the plants, transported to an inspection site for examination, and then moved again to a dedicated storage site for safekeeping.

You have been informed that two of ABC ‘s nuclear plants, called Plant One and Plant Two, need to move out depleted pellets in the following amounts:

- Plant One wants to ship out up to 190 pellets per month

- Plant Two wants to ship out up to 230 pellets per month

This is how many pellets these two plants want to ship out per month. However, the exact number of pellets that will actually be moved out from each plant depends on the space that is available at the dedicated storage sites that would ultimately receive them. ABC is mandated by law to move as many pellets out of the plants as the storage sites are able to

receive. You have been informed that two of ABC ‘s dedicated storage sites, called Storage X and Storage Y, are ready to receive depleted pellets from ABC ‘s plants, in the following amounts:

- Storage X is ready to receive a total of 195 pellets per month

- Storage Y is ready to receive a total of 215 pellets per month

Since the two plants want to ship out more pellets per month than the two storage sites can receive, you know that some pellets will have to stay in their plant of origin until a later time. This is not a problem, however, since every one of ABC ‘s plants has the capacity to temporarily store whatever pellets are not shipped out, and the law allows this.

At this time, only one inspection site is available to receive the pellets from the two plants, inspect them, and send them out to the two storage sites. It is called Inspection Site A. Notice that inspection sites do not have storage capacity: the pellets come in, are inspected, and continue their trip to the storage sites.

Moving radioactive material is not easy and therefore it is not cheap either. There is a lot of planning and security involved. Each shipment of pellets has to be closely monitored and protected to prevent the loss of radioactive material. Also, the vehicles transporting the material have to move relatively slowly, to avoid accidents. Therefore, it is very expensive to transport the radioactive pellets from the plant to the inspection site, and from there to the storage site. Based on estimates and historical data, ABC has calculated the following transportation costs, in dollars per pellet moved:

- It costs 10.98 dollars to move one pellet from Plant One to Inspection Site A

- It costs 13.94 dollars to move one pellet from Plant Two to Inspection Site A

- It costs 5.23 dollars to move one pellet from Inspection Site A to Storage X

- It costs 14.12 dollars to move one pellet from Inspection Site A to Storage Y

- You decide to optimize the transshipment of depleted pellets from the plants to the storage sites (stopping on the way at Inspection Site A, of course) in order to minimize the overall monthly transportation In this optimized solution, how many pellets will Plant Two be able to ship out to the Inspection Site A every month?

(5 marks)

- In this optimized solution, how much is the overall monthly transportation cost for ABC? Hint: The answer is more than 8000 but less than 10000.

(5 marks)

ABC has calculated the following transportation costs for Inspection Site B, in dollars per pellet moved:

- It costs 9.75 dollars to move one pellet from Plant One to Inspection Site B

- It costs 13.35 dollars to move one pellet from Plant Two to Inspection Site B

- It costs 6.95 dollars to move one pellet from Inspection Site B to Storage X

- It costs 13.15 dollars to move one pellet from Inspection Site B to Storage Y

- As before, you will optimize the transshipment of depleted pellets from the plants to the storage sites, stopping first at the inspection sites, so as to minimize the overall monthly transportation How much will this cost ABC every month? Hint: In this optimal solution, Plant Two ships a total of 25 pellets per month to Inspection Site B.

(5 marks)

You have just received two e-mails from your boss. The first informs you that a third nuclear plant, called Plant Three, is ready to move out up to 280 depleted pellets per month. The second tells you that a third dedicated storage site, called Storage Z, is ready to receive a total of 240 depleted pellets per month from ABC’s plants. ABC has calculated the following transportation costs for the new plant and storage site, in dollars per pellet moved:

- It costs 13.67 dollars to move one pellet from Plant Three to Inspection Site A

- It costs 8.11 dollars to move one pellet from Plant Three to Inspection Site B

- It costs 13.22 dollars to move one pellet from Inspection Site A to Storage Z

- It costs 12.94 dollars to move one pellet from Inspection Site B to Storage Z

- Again you will optimize the transshipment of depleted pellets from the three plants to the three storage sites, stopping first at the inspection site, so as to minimize the overall monthly transportation In this optimal solution, how many pellets is Plant One sending to Inspection Site B?

(5 marks)

- In this optimized solution, how much is the overall monthly transportation cost for ABC? Hint: The answer is more than 12500 but less than

(5 marks)

ABC has learned that a new regulation, to be enacted soon, will require that no depleted pellets be stored at the plants past the monthly removal date. To accommodate this, the storage sites have increased the total number of pellets they are willing to receive every

month, as follows:

- Storage X is now ready to receive a total of 200 pellets per month

- Storage Y is now ready to receive a total of 250 pellets per month

- Storage Z is now ready to receive a total of 250 pellets per month

The new regulations also make it easier for pellets to be moved, which reduces all the transportation costs in the network by 4.23 percent. (This means you have to multiply all the previous transportation costs by 0.9577 in order to get the new costs.) One last time, you will optimize the transshipment of depleted pellets from the three plants to the three storage sites, stopping first at the inspection sites, so as to minimize the overall monthly transportation cost. (Hint: In this optimized solution, Plant Two is shipping 200 pellets per month to Inspection Site A.)

- In this optimized solution, how much is the overall monthly transportation cost for ABC?

(5 marks)

# Question 2, Sales and Operations (Aggregate) Planning (20 marks)

** **

** **

You are working for ABC Company, helping them run their Sales & Operations Planning (S&OP) program for a line of make to stock items. Specifically, you are running the aggregate planning model to help assist the Chief Operating Officer (COO) and the Vice President of Sales to agree upon the next 12 months plan.

The aggregate model is provided in the Blackboard in Excel for you. The model provided here is offered without any input data. That data is provided below, and you must enter it by hand into the spreadsheet. When solving this model, while the number of workers must be an integer, the amounts of money, hours and items do not have to be integers.

The demand data for the model is provided below, and you must enter it into the spreadsheet. The aggregate monthly demand of the item is as follows:

January |
February |
March |
April |

2767 |
2503 |
2269 |
1733 |

May |
June |
July |
August |

1009 |
523 |
293 |
733 |

September |
October |
November |
December |

1277 |
1511 |
1987 |
3067 |

The remaining data for the model is provided below, and you must enter it into the spreadsheet. Please enter the input values with all the decimal values given below.

- Material cost ($/unit): 94.01

- Inventory holding costs ($/unit/month): 20.23

- Cost of stockouts ($/unit/month):89

- Cost for hiring and training new worker:0

- Cost for laying off an employee:0

- Labor hours required per item produced:76

- Regular worker cost ($/month): 2856.0

- Overtime cost ($/hour):5761

- Outsourcing cost ($/item): 215.39

- Hours worked by employee per month: 144.44

- Max overtime (hours/month/employee):12

- Starting inventory:0

- Ending inventory (min): 0

- Starting backlog: 0

- Allowable ending backlog (max): 0.0

- Starting workforce:0

- Ending workforce (min): 0

- Ending workforce (max): 24.0

- Base price ($/item): 251.09

- You want to develop an initial aggregate plan for the next 12 Use the initial data provided above and assume that there are no planned promotions or discounts over the next 12 months. Find the optimal plan over the next 12 months. (Hint: The optimal solution will hire 16 employees in January, and fire 11 in April and 5 in May). Based on this optimal solution, answer the following questions.

What is the total annual profit?

(3 marks)

- Using the status quo (a) as starting point, the senior team has asked you to assess the impact of running a three-month promotion during the Summer with a 15% discount in June (month 6), a 25% discount in July (month 7), and a 15% discount in August (month 8). Use an elasticity value of 5, and solve

- Will the total annual profit increase in this scenario, compared to the profit in (a)?

(1.5 marks)

- Solving optimally, what is the total annual profit with this promotion?

(1.5 marks)

- There is some pressure from the community to avoid firing Using the status quo (a) as starting point, the senior team has asked you to assess the impact of implementing a policy that no employees can be fired. Solve optimally.

Which of the following statements are true for this policy, compared to the status quo? Select all that apply.

- Number of employees hired remains the
- Overtime hours

- Backlogged units increase

- Internal production
- Outsourced production
- Total annual profit

(4 marks)

- There are some concerns about the sustainability of the operations at the Using the status quo (a) as starting point, the senior team has asked you to assess the impact of a policy that would eliminate all outsourcing of production. Solve optimally.

- How will the total annual profit under this policy compare to the profit of the status quo (a)?

(1.5 marks)

- Solving optimally, what is the total annual profit under this policy? (Enter your answer in dollars).

(1.5 marks)

- There is some new technology coming into the market that may allow ABC to produce the items faster than Using the status quo (a) as starting point, you have been asked to assess the impact that such a technology could have if it could reduce the labor hours required per item produced by one third. (Hint: This means that the hours required to produce an item using the new technology would be two thirds of the hours required to produce an item in the status quo). Solve optimally.

Which of the following statements would be true considering the faster production scenario, compared to the status quo (a)?

1)

- Firing employees is no longer
- Number of employees fired decreases by one

- Outsourced production is no longer

- Outsourced production decreases by two thirds.
- Total annual profit
- Total annual profit

(3 marks)

2) Solving optimally, what is the total annual profit in this scenario?

(1 mark)

- There are some concerns about the limitations of ABC warehousing Using the status quo (a) as starting point, the senior team has asked you to assess the impact of limiting inventory to a maximum of 1000 items. (Hint: This means that at no point can the inventory levels exceed this limit). Solve optimally.

What is the total annual profit?

(3 marks)

# Question 3, Inventory Management, Multi-period models (25 marks):

** **

ABC company supplies tires to most of New England’s taxi cabs: thousands of taxis across Massachusetts, New Hampshire, Maine, Vermont, Connecticut, and Rhode Island depend on it to have tires available for immediate purchase. A long-standing relationship with local taxi collectives has made ABC the go-to supplier of tires for cabs in the region.

In an effort to maintain logistics costs low, the CEO of ABC has hired you as a consultant for their logistics department, and assigned you the task of looking into the cost of inventory at their Distribution Center (DC) located in Franklin, Massachusetts. Through some interviews and data collection on site you learn that ABC has several stores across the New England region. All of them are served from this single DC. You have been asked to define a Q policy for the inventory in this DC.

The demand of tires from taxi cabs is normally distributed and relatively stable at around 3,500 units per month, with a standard deviation of 375 units per month. The Sales department tells you that ABC pays $217 per unit. Through talks with Finance and Logistics personnel, you estimate that the firm’s holding charge is about 16% of the item’s cost per year.

Tires have to be shipped in full containers from the West Coast. Every time ABC places an order, it has to be in multiples of 100 units, and incurs an ordering cost of $1,650 for each order it places. Since the tires are moved by rail and truck, the lead time from the vendor is relatively long at 3 weeks. Payment is due at the moment the order is placed, and ownership of the items is transferred to you as soon as the order is placed. ABC’s policy is to maintain a customer service level (CSL) of 95%.

(Hint: Notice that some values above are given in terms of weeks, others in terms of months, and others in terms of years. For the sake of transforming values from one unit to the other, you can assume there are exactly 12 months in a year and exactly 4 weeks in a month.)

a)

- For orders from ABC to its supplier, what order size would you recommend? Use the economic order quantity (EOQ) formula, but subject to the constraint of ordering multiples of

(3 marks)

- On an average, how frequently would ABC place these orders? Give your answer in terms of weeks, with one

(2 marks)

b)

- What is your Reorder Point ? Give your answer rounded to the nearest multiple of

(2 marks)

- What is the expected annual cost of carrying cycle stock? Round to the nearest multiple of $100.

(2 marks)

- What is the expected annual cost of carrying the safety stock? Round to the nearest multiple of $100

(1 mark)

The head of the Finance and Accounting department has reminded you that, since you pay for the items upfront and take ownership of them as soon as the order is placed, you should also consider the cost of the ‘pipeline inventory.’ Holding pipeline inventory is a little cheaper than holding inventory on hand. The reason for this is that the cost of carrying inventory on hand includes both the cost of money (which is 8% for ABC) and the cost of storing and managing the inventory in the DC (which accounts for the rest).

- c) Using a carrying charge of 8% for the pipeline inventory, what is the expected annual cost of carrying the pipeline inventory? Round to the nearest multiple of

$100

(5 marks)

In a meeting with the CEO, and the heads of Sales, Logistics and Finance, you present your findings on what ABC pays for carrying cycle, safety and pipeline inventory. They are all very attentive to what you are presenting.

“I have a question”, says the head of Sales, “As you know, currently we have a policy of keeping a 95% customer service level. I have repeatedly asked for this level to be raised, to reduce lost sales.”

“And I have repeatedly pointed out that this would increase our inventory holding cost”, interrupts the head of Logistics.

“Let’s ask the consultant”, says the CEO, “Which of these inventory carrying costs would be affected by us increasing the customer service level?”

Select all that apply: d)

- Cycle stock
- Safety stock

- Pipeline stock

(5 marks)

“Here is my point”, continues the head of Sales, “Right now, we are leaving a lot of money on the table. Look, I estimate that every year we lose about a hundred thousand dollars in sales because we run out of stock. That’s a lot of money! I have run some numbers, and I think that – if we increase the customer service level from 95% to 99.9%, we would reduce these lost sales tenfold, from $100,000 to only $10,000 per year.”

“So, it all comes down to the increase in inventory carrying cost”, says the CEO. “How much more would we have to pay in inventory holding cost if we were to implement a CSL of 99.9%?”

- e) Please provide the increase in inventory holding cost, rounded to the nearest multiple of $1000.

(5 marks)

# Question 4, Inventory Management, Single period models (25 marks):

** **

ABC Sportswear company designs and sells wetsuits to the U.S. market. The designs of the wetsuits are updated each year. The process for updating the design typically starts in January the year before the designs are to be released. At this time, the purchasing, design, and sales departments have a two-day meeting to discuss the product portfolio for the upcoming year, including design, functionality, and price. In March, the designs are finalized, and the purchasing department starts negotiating with suppliers across the world.

Production usually starts in September or October the year before the designs are to be released, and lasts until December or January depending on the supplier. In February, retailers start placing their orders to ABC, with retail sales of the new designs typically starting in early April.

The season stretches from April to September. As the season progresses, retailers place replenishment orders to ABC, who supplies the retailers from a central warehouse. Sales peak in early summer. Towards the end of the summer, sales decline dramatically. When the season ends in September, ABC pushes out any remaining inventory to the retailers by offering all models at only 25% of the original selling price.

Consider a specific SKU# 001237 (Model A200, size L).

For the upcoming year, ABC has negotiated with a supplier to have SKU# 001237 produced and delivered to ABC at $175 per unit. SKU# 001237 is sold to retailers at $225, for a unit margin of $50.

Suppose total demand for SKU# 001237 for the upcoming season (the time during which the product is sold at full price) is estimated to follow a Normal distribution with mean 2,500 and standard deviation 400.

- How many A200, size L, should ABC produce to maximize expected profit? Please round to closest

(5 marks)

Last year, SKU# 001237 turned out to be extremely popular and sold out quickly. Since ABC makes a good margin on the product, management has decided they do not want the same situation again.

“We cannot have the probability for out-of-stock during the season to be more than 5% on this model”, said the sales manager recently.

- How many SKU# 001237 should ABC produce to ensure that the stock-out probability is exactly 5% during the season? Assume the same demand distribution as in Part 1. Please round to closest

(5 marks)

Consider the policy from Part b.

- With your suggested production quantity, how many units of SKU# 001237 can ABC expect to be short during the regular season? Please answer with two

(5 marks)

Consider the policy from Part b.

- What is the expected contribution to profit from SKU# 001237? Please round to closest integer Answer without the dollar symbol.

(5 marks)

The policy suggested by the sales manager leads to a lower expected profit than the “optimal” policy calculated in Part a. The sales manager motivates this by claiming that the policy in Part a is not taking into account future lost profits by being out of stock. “So”, he argues, “the expected profit implied by the ‘optimal policy’ is biased upwards.” Suppose there is an additional cost per unit out-of-stock, γ. Suppose further that the policy suggested in Part b is the policy that maximizes expected contribution when this additional cost is taken into account.

- What is the additional cost γ? Please round to closest integer Answer without the dollar symbol.