Using the data in Exhibit 19.3 prepare Excel Spreadsheets for each of the Production Plans( 1 to 4 ) shown in exhibit 19.4. In order to receive full credit you must use Excel Formulas for all calculation and you can only enter base case information once. .All additional uses of that information must be through use of a formula. I have the completed tables but just need the computations/excel formulas

  

 

 

Sales and Operations Planning Chapter 19 497 exhibit 19.3 Aggregate Production Planning Requirements Employee January February March April May June Beginning inventory Demand forecast Safety stock (25 x Demand forecast) 400 1,800 450 450 375 275 225 275 1.500 1,100 900 1,100 1,000 375 275 225 275 400 Production requirement (Demand forecast+Safety stock-Beginning inventory) 1.850 1,425 1.000 850 1,150 1.725 Ending inventory Beginning inventory + Production requirement-Demand forecast) 450 375 275 225 275 400 Before investigating alternative production plans, it is often useful to convert demand fore casts into production requirements, which take into account the safety stock estimates. In Exhibit 19.3, note that these requirements implicitly assume that the safety stock is never actu- ally used, so that the ending inventory each month equals the safety stock for that month. For example, the January safety stock of 450 (25 percent of January demand of 1,800) becomes the inventory at the end of January. The production requirement for January is demand plus safety stock minus beginning inventory (1,800+450-400-1850) Now we must formulate alternative production plans for the JC Company. Using a spread- sheet, we investigate four different plans with the objective of finding the one with the lowest total cost. Plan 1. Produce to exact monthly production requirements using a regular eight-hour day by varying workforce size. Plan 2. Produce to meet expected average demand over the next six months by maintain- ing a constant workforce. This constant number of workers is calculated by finding the average number of workers required each day over the horizon. Take the total production requirements and multiply by the time required for each unit. Then divide by the total time that one person works over the horizon [(8,000 units x 5 hours per unit)+(125 days x 8 hours per day) 40 workers]. Inventory is allowed to accumulate, with shortages filled from next month's production by backordering. Negative beginning inventory balances indicate that demand is backordered. In some cases, sales may be lost if demand is not met. The lost sales can be shown with a negative ending inventory balance followed by a zero beginning inventory balance in the next period. Notice that in this plan we use our safety stock in January, February, March, and June to meet expected demand Plan 3. Produce to meet the minimum expected demand (April) using a constant work- force on regular time. Subcontract to meet additional output requirements. The number of workers is calculated by locating the minimum monthly production requirement and deter- mining how many workers would be needed for that month [(850 units x 5 hours per unit) +(21 days x 8 hours per day) 25 workers] and subcontracting any monthly difference between requirements and production. Plan 4. Produce to meet expected demand for all but the first two months using a con- stant workforce on regular time. Use overtime to meet additional output requirements. The number of workers is more difficult to compute for this plan, but the goal is to finish June KEY IDEA with an ending inventory as close as possible to the June safety stock. By trial and error it can be shown that a constant workforce of 38 workers is the closest approximation. The next step is to calculate the cost of each plan. This requires the series of simple calcula- tions shown in Exhibit 19.4. Note that the headings in each row are different for each planets These because each is a different problem requiring its own data and calculations The final step is to tabulate and graph each plan and compare their costs. From Exhibit 19.5 we can see that using subcontractors resulted in the lowest cost (Plan 3) Exhibit 19.6 shows the effects of the four plans. This is a cumulative graph illustrating the expected results on the total production requirement. In practice, there are often many different types of special can be due to union contracts, or other factors relating to worker availability for example