I need help completing the highlighted "Project Available" and "Planning Releases" rows.

Case 4.1: Apix Polybob goes with the Excel spreadsheet

AutoSave = Case Study 4-1 MRP Table(1) - Excel Search Citlali Me File Page Layout Formulas Data Review View Help Home Insert X Georgia 11 General PH - A A A Paste 29 Wrap Text Merge & Center $ % Insert Del Conditional Formatas Cell Formatting Table Styles Styles Clipboard Font Alignment 5 Number Cel A1 Bio Item B Lead time 1 lot size 80 On Hand 10 B D D E F G H 4 1 J L 1 2 4 6 8 9 10 3 50 5 50 7 60 9 60 11 50 10 10 40 70 20 10 30 60 40 80 10 80 80 30 80 . 1 2 4 6 8 9 3 100 10 18 5 100 2 120 11 100 -- 120 Item B Lead time 1 Lot size 80 On Hand 10 1 2 PERIOD 3 Gross Requirements 4 Scheduled Receipts 5 Projected Available 10 6 Planned Order Releases 7 8 Item C Lead time 1 Lot size 150 On Hand 40 9 PERIOD 10 Gross Requirements 11 Scheduled Receipts 12 Projected Available 40 13 Planned Order Releases 14 15 Item D Lead time 2 Lot size 200 On Hand 180 16 PERIOD 17 Gross Requirements 18 Scheduled Receipts 19 Projected Available 180 20 Planned Order Releases 21 3 5 . 1 6 7 9 10 11 8 150 350 150 150 AutoSave OM Case Study 4-1 MRP Table(1) - Excel Search Citlali Mez File Home Insert Page Layout Formulas Data Review View Help X 11 - A A == - 2 Wrap Text General Georgia BIV- Paste Merge & Center $%, 8 Insert Dele Conditional Format as Cell Formatting Table Styles Styles Clipboard S Font Alignment 5 Number Cell AL 2 ItemB Lead time 1 Lot size 80 On Hand 10 B D E F G H ! J L 1 3 5 6 7 9 11 4 390 8 390 10 390 990 21 22 Item E Lead time 2 Lot size 400 On Hand 400 23 PERIOD 24 Gross Requirements 25 Scheduled Receipts 26 Projected Available 400 27 Planned Order Releases 28 29 Item F Lead time 2 Lot size 500 On Hand 50 30 PERIOD 31 Gross Requirements 32 Scheduled Receipts 33 Projected Available 50 34 Planned Order Releases 35 1 3 5 6 7 9 11 2 380 4 380 8 380 10 380 SOO CASE STUDY 4.1 Apix Polybob Company Ken Mack, plant manager for the Apix Polybob Company, was having a heated discussion with Jack Gould, the production and inventory control manager. Ken was getting tired of frantic calls from Jim Uphouse, the marketing manager, concerning late orders for their Polybob (polybobs are a fictitious product) customers and was once again after Jack to solve the problem. Some of the discussion points follow: Material Requirements Planning 109 JACK: "Look, Ken, I'm not sure what more we can do. I've reexamined the EOQ (eco- nomic order quantity lot size) values and all the reorder points for all our inven- tory for all our Polybob models, including all component levels and purchased items. I've implemented strict inventory control procedures to ensure our accu- racy levels to at least 80%, and I've worked with the production people to make sure we are maximizing both labor efficiency and utilization of our equipment. The real problem is with those salespeople. We no sooner have a production run nicely going, and they change the order or add a new one. If they'd only leave us alone for a while and let us catch up with our current late order bank, we'd be okay. As it is, everyone is getting tired of order changes, expediting, and making everything into a crisis. Even our suppliers are losing patience with us. They tend to disbelieve any order we give them until we call them up for a crisis shipment." KEN: "I find it hard to believe that you really have the EOQ and reorder point val- ues right. If they were, we shouldn't have all these part shortages all the time while our overall inventory is going up in value. I also don't see any way we can shut off the orders coming in. I can imagine the explosion from Jim if I even suggested such a thing. He'll certainly remind me that our mission statement clearly points out that our number-one priority is customer service, and refusing orders and order changes certainly doesn't fit as good customer service." JACK: "Then maybe the approach is to deal with Frank Adams (the chief financial officer). He's the one who is always screaming that we have too much inven- tory, too much expediting cost, too much premium freight costs from suppliers, and poor efficiency. I've tried to have him authorize more overtime to relieve some of the late order conditions, but all he'll say is that we must be making the wrong models. He continually points to the fact that the production hours we are paying for currently are more than enough to make our orders shipped at standard, and that condition has held for over a year. He just won't budge on that point. Maybe you can convince him." KEN: "I'm not sure that's the answer either. I think he has a point, and he certainly has the numbers to back him up. I'd have a real rough time explaining what we were doing to Ron Marrison (the CEO). There's got to be a better answer. I've heard about a systems approach called material requirements planning or something like that. Why don't you look into that? Take a representative model and see if that approach could help us deal with what appears to be an impossible situation. I'm sure something would work. I know other factories have similar production conditions yet don't seem to have all our problems." B C (2 each) E (3 each) F (2 each) ED Following is the information about Polybob model A that Ken suggested as a repre- sentative model to use for the analysis: Component B Inventory 10 Lot Size 80 150 200 400 500 Lead Time 1 1 40 711 to Scheduled Receipts None None None None 500, week 1 Reorder Point 5 15 50 70 80 D 2 E 180 300 50 2 F 2