Question

1. ABC distribution does delayed differentiation of products for a customer at its distribution facility. The forecasted demand for one of these products during the next five months is 420, 580, 310, 450 and 540 400 respectively. The per unit costs and the capacity availability for producing these items are given below.

                                                             Month

                              1             2               3             4             5

Customization Cost   $24              $40               $36        $54        $60

Capacity                       500             520               450        550        400

The inventory costs is $0.55 per month for each unit of the product carried in inventory (estimated by inventory levels of each month). ABC began the contract with 90 units of inventory (safety stock) on hand for this product. Their production plan calls for producing at least 400 units per month. To avoid demand risk, ABC wants to maintain a minimum inventory level of at least 50 units for 1, 2, and 3 rd month. On the fourth month they want the inventory level to be at most 90 units as part of the ramping down operations. • Create a spreadsheet model for this problem and solve it using Solver to minimize the total cost (sum of customization and inventory costs) for delayed differentiation for the 5 months in the planning horizon.

Answer 1

Please go through all the pictures, uploading three images, one explaining the distribution of units, one showing the setting of solver, and one showing all the formula used in the spreadsheet.

1) SHowing delivery of condition and everything in the spreadsheet.

2)showing the configuration of solver

3) Showing all the formula used in the spreadsheet.

LP solve p - Excel Mohit Prajapat 2 - 0 x # Page Layout - Formulas Data Review View Developer Help Tell me what you want to do Share File 2 S . Home Insert X Cát Copy - *Format Painter Clipboard x » P Normal Bad AutoSum - 47 O === = = = A - A s Wrap Text Menge & Center - x Insert Delete Format Paste = General 7.% Number Calibri V11 V BIU - B - Font fix Conditional Format as Good Formatting - Table Neutral CIK Clear Sort & Find & Filter - Select - Edin Aignment Cells 024 D E F G H I J K L M N O P Q R S A Production Limit 400 4 Inventory cost- 5 Inventory in hand $ 0.55 90 Month Inventory in hand condition Manufacture Actual inventory Cost SUD D Custom. Cost/Unit Capacity 0 420 S 24.00 580 S 40.00 310 S 36.00 150 $ 51.00 540 $ 50.00 460 500.00 520.00 - 450.00 550.00 400.00 170 $ 50 S 1401 $ 400 12,093.50 18,427.50 14,477.00 21,6 19.50 23.9/2.50 100 -501 S Actual units in 6.30 Total Costa $ 90,620.00 540 Sheet1 Ready # P -- + 91%

LP sphep - Excel Mohit Prajapat *** - x Share Formulas View Data Review Connections File Home Insert Page Layout a Show Queries F rom Table Get External New Data - OJ 13 Recent Source Get A Transform Developer Help 21 Tell me what you want to do Clear Le Consolidate Relationships Data Analysis 2 Solver Properties Z Reply Geography = Sort Stocks Fresh All- Edit Links Connections E' Flash Fill 1 Remove Duplicates Text to Columns Data Validation - Data Tools P What-il Forecast Analysis - Shoe Forecast Group Ungroup - Subtotal Outline need Data Types Sort & Fiter Analyze 133 D E Solver Parameters F G H J K L M N O P Q R S Productia $ Set Objective: 4 Inventory cost- 5 Inventory in hand $9538 0.55 90 OMO O value of Month OM By Changing Variable Cels SG$25:56520 D Custom. Cost/Unit Capacity 0 420 S 24.00 580 S 40.00 310 S 36.00 150 $ 51.00 540 $ 50.00 Add Subies to the Consiraints: SG$23:56$29 <= $D$2:$D$29 SG$23:$G$29 = integer SG$25:56529 >= 400 SG$34:5G533 - SB$25:56529 SHS2:5-527-50 SH$28 <-90 Change Delete Reset All LoadSave Male Unconstrained variables Non-Negative Select a Song Simplex LP Method: Options Sching Method Select the GFG Noninear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver problems, and select the Evolutionary engine for Solver problems that are non-smocth. Help Sale Sheet1 © Point # - + 91%

LP sole p - Excel Mohit Prajapat 2 - 0 x Share Formulas View Data Review Connections OSCH File Home Insert Page Layout 2 Show Queries from Table Get External New Data - Query - 13 Recent Source Get & Transform K20 Developer Help M e Geography 1 L Tell me what you want to do Clear Reapply Sort Filter Advanced Sert Fiter consolidate relationships Properties Data Analysis 2 Solver # Group Ungroup- Subtotal Stocks D Flash Fill € Remove Duplicates Text to Columns Data Validation - Data Tools i What If Forecast Analysis - Sheet Forecast All - Edit Links Connections Data Types Analyze Production Unit 4 Inventory cost =69+F8-59 =3997"SPS4) G10 C10+H105641 C1'C11H11"58841 912"C12H12565 G13 C13H13*5854 124 CSINO B12 2012-01-012 913+H12-13 550 100 Ali hann GIFS -G10 HD -11-10 -G12+H11 ROSSENENNUNNE Sheet1 Ready # - + 73%