Chapter 4. Linear Programming Models. Example 4.1 – Advertising Model. General Flakes Company advertises a low-fat breakfast cereal in a variety of 30 second television ads placed in a variety of television shows.
PowerPoint Slideshow about 'Linear Programming Models' - didrika
An Image/Link below is provided (as is) to download presentation
Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
Inputs and range names. Enter the number of employees needed on each day of the week.
Employees beginning each day. Enter any trial values for the number of employees beginning work on each day in the Employee_starting range.
Employees on hand each day. Enter the formula =$B$4 in cell B14 and copy it across to cell F14. Proceed similarly for rows 15-20, being careful to take “wrap arounds” into account.After completing these rows calculate the total number who show up each day by entering the formula =SUM(B14:B20) in cell B21 and copying across to cell H21.
Total employees. Calculate the total number of employees in cell B25 with the formula =SUM(Employees_Starting).
Inputs. Enter the input data in the range B4:B14 and in the Forecasted_demand range.
Production, hiring and firing plans. Enter any trial values for the number of pairs of shoes produced each month, the overtime hours used each month, the workers hired each month, and the workers fired each month.
Workers available each month. In cell B17 enter the initial number of workers available with the formula =B5. Because the number of workers available at the beginning of any other month is equal to the number of workers from the previous month, enter the formula =B20 in cell C17 and copy it to the range D17:E17. Then in cell B20 calculate the number of workers available in month 1 with the formula =B17+B18-B19 and copy this formula to the range C20:E20.
Overtime capacity. Enter the formula =$B$7*B20 in cell B25 and copy it to the range C25:E25.
Production capacity. Calculate the regular-time hours available in month 1 in cell B22 with the formula =$B$6*B20 and copy it to the range C22:E22 for the other months. Then calculate the total hours available for production in cell B27 with the formula =SUM(B22:B23) and copy it to the range C27:E27. Calculate the production capacity for month 1 by entering the formula =B27/$B$12 in cell B32, and copy it to the range C32:E32.
Inventory each month. Enter the formula =B4+B30 in cell B34. For any other month, the inventory after production is the previous month’s ending inventory plus tat month’s production, so enter the formula =B37+C30 in cell C34 and copy it to the range D34:E34. Then calculate the month 1 ending inventory in cell B37 with the formula =B34-B36 and copy it to the range C37:E37.
Monthly costs. Calculate the various costs shown in rows 40 through 45 for month 1 by entering the formulas =$B$8*B18=$B$9*B19=$B$10*B20=$B$11*B23=$B$13*B30=$B$14*B37in cells B40 through B45. Then copy the range B40:B45 to the range C40:E45 to calculate these costs for the other months.
Totals. In row 46 and column F, use the SUM function to calculate cost totals, with the value in F46 being the overall total cost.