can you do table A through G for POQ =2. All excel charts using formula POQ

The Crime Stopper Alarm Company produces two models, A and T, for which the product structures are shown in figure 1 below: A(1) T(1) B(5) 3C(2) 2L(1) KO 2D(1) E(3) 3D(1) N(2) 2F3) G(2) 2F(3) G(2) Figure 1: Product Structures for A and T [BOM] The quantity required to make one of the next-higher-level items is shown to the left pf an item, and the lead time in weeks for each procurement or production stage is indicated in parentheses on each diagram. The company has on-hand balances and scheduled receipts as indicated in Table 1 below: Item A Scheduled Receipt Table 1: Inventory Status Lead time Lotsizing Policy+ On Hand FOQ, LAL, POQ 0 Safety stock 0 To TT no + Automatic Zoom Figure 1: Product Structures for A and T [BOM] The quantity required to make one of the next-higher-level items is shown to the left pf an item, and the lead time in weeks for each procurement or production stage is indicated in parentheses on each diagram. The company has on-hand balances and scheduled receipts as indicated in Table 1 below: Table 1: Inventory Status Item Safety stock Lead time Lotsizing Policy+ On Hand Scheduled Receipt A 0 1 FOQ, L4L, POQ 0 T 0 1 FOQ, L4L, POR 0 B 0 5 FOQ, LAL, POO 0 0 2 FOQ, LAL, POQ 50 100 in week 2 D 0 1 FOO, LAL, POO 1,000 E 0 3 FOO, L4L, POQ 0 F 0 3 FOQ, LAL, POO 1,000 5,000 in week 2 G 0 2 FOO, LAL, POO 500 K 0 6 FOQ. LAL, POQ 0 L 0 1 FOO, L4L, POQ 60 150 in week 1 N 0 2 FOQ, L4L, POO 0 +-> Your coding should cover the following: FOQ - Fixed Order Quantity L4L - Lot-for-Lot and POQ - Periodic Order Quantity (n> 1] MRP Page 1 of 3 2 Table 2: Master Schedule for Items A and T Week 0 1 2 3 4 5 6 7 8 9 10 11 Model A 100 200 150 100 Model T 225 180 210 250 YOUR ASSIGNMENT Specifically, you are required to develop an Excel-based/VBA MRP explosion model for the three products using the given MPS data provided in Table 2, the current inventory level provided in Table 1 and respond to the following questions/issues: (a) Your code should be responsive to any change in the following parameters: on-hand inventory level, lead time, safety stocks, and lot-sizing policy with appropriate checking for infeasibility. As noted in Table 1, the user should be able to select which lot size policy to use for any item in the inventory. (b) What planned order releases would you obtain from the original data as provided in Tables 1 and 2 (ie using the given standard lot size policy of LAL for all items)? (c) If the current on hand inventory of every item is zero, and the user decides to select the fixed order quantity (FOQ = 20) policy for each item, what planned order releases would you obtain for all items? (d) What planned order releases would you obtain for all parts if the following changes, regarding the common parts, in Table 3 are effected? Table 3: Requested Changes Item On-hand inventory Safety Stock Lot sizing policy 70 40 LAL 100 POQ n 2 FOQ = 50 LAL-Lot-for-lot POQ-Periodic Order Quantity D F G SO 70 50