CHAPTER 4 THE ART OF MODELING WITH SPREADSHEETS
玛丽和魔女之花
青海高考分数线SOLUTION TO SOLVED PROBLEMS
4.S1Production and Inventory Planning Model
Surfs U p p roduces h igh-‐end s urfboards. A c hallenge f aced b y S urfs U p i s t hat t heir d emand i s highly s easonal. D emand e xceeds p roduction c apacity d uring t he w arm s ummer m onths, b ut is v ery l ow i n t he w inter m onths. T o m eet t he h igh d emand d uring t he s ummer, S urfs U p
typically p roduces m ore s urfboards t han a re n eeded i n t he w inter m onths a nd t hen c arries inventory i nto t he s ummer m onths. T heir p roduction f acility c an p roduce a t m ost 50 b oards per m onth u sing r egular l abor a t a c ost o f $125 e ach. U p t o 10 a dditional b oards c an b e produced b y u tilizing o vertime l abor a t a c ost o f $135 e ach. T he b oards a re s old f or $200. Becau o f s torage c ost a nd t he o pportunity c ost o f c apital, e ach b oard h eld i n i nventory f rom one m onth t o t he n ext i ncurs a c ost o f $5 p er b oard. S ince d emand i s u ncertain, S urfs U p would l ike t o m aintain a n e ndi
ng i nventory (safety s tock) o f a t l east 10 b oards d uring t he warm m onths (May–September) a nd a t l east 5 b oards d uring t he o ther m onths (October–April). I t i s n ow t he s tart o f J anuary a nd S urfs U p h as 5 b oards i n i nventory. T he f orecast o f demand o ver t he n ext 12 m onths i s s hown i n t he t able b elow. F ormulate a nd s olve a l inear programming m odel i n a s preadsheet t o d etermine h ow m any s urfboards s hould b e p roduced each m onth t o m aximize t otal p rofit.
初学游泳口诀Jan Feb Mar Apr May Jun July Aug Sep Oct Nov Dec
10 14 15 20 45 65 85 85 40 30 15 15
This i s a d ynamic p roblem w ith 12 t ime p eriods (months). T he a ctivities a re t he p roduction quantities i n e ach o f t he 12 m onths u sing r egular l abor a nd t he p roduction q uantities i n each o f t he 12 m onths u sing o vertime l abor.
To g et s tarted, w e s ketch a s preadsheet m odel. E ach o f t he 12 m onths w ill b e a s eparate column i n t he s preadsheet. F or e ach m onth, t he r e
gular p roduction q uantity (a c hanging cell) m ust b e n o m ore t han t he m aximum r egular p roduction (50). S imilarly, f or e ach month t he o vertime p roduction q uantity (a c hanging c ell) m ust b e n o m ore t han t he maximum o vertime p roduction (10). E ach m onth w ill g enerate r evenue, i ncur r egular a nd overtime p roduction c osts, i nventory h olding c osts, a nd a chieve a r esulting p rofit. T he g oal will b e t o m aximize t he t otal p rofit o ver a ll 12 m onths. T his l eads t o t he f ollowing s ketch o f a s preadsheet m odel.
The e nding i nventory e ach m onth w ill e qual t he s tarting i nventory (the g iven s tarting
inventory f or J anuary, o r t he p revious m onth’s e nding i nventory f or f uture m onths) p lus a ll production (regular a nd o vertime) m inus t he f orecasted s ales. T he e nding i nventory a t t he end o f e ach m onth m ust b e a t l east t he m inimum s afety s tock l evel. T he r evenue w ill e qual the s elling
p rice t imes f orecasted s ales. T he r egular (or o vertime) p roduction c ost w ill b e the r egular (or o vertime) p roduction q uantity t imes t he u nit r egular (or o vertime)
production c ost. T he h olding c ost w ill e qual t he e nding i nventory t imes t he u nit h olding cost. T he m onthly p rofit w ill b e r evenue m inus b oth p roduction c osts m inus h olding c ost. Finally, t he t otal p rofit w ill b e t he s um o f t he m onthly p rofits. T he f inal s olved s preadsheet, formulas, a nd S olver i nformation a re s hown b elow.
Unit Cost (Reg)Unit Cost (OT)Selling Price Holding Cost Starting Inventory
<=
龟兔赛跑图片Max Regular <=
Max OT
Forecasted Sales Ending Inventory
>=
Safety Stock
形容大树
The v alues i n R egularProduction (C10:N10) a nd O TProduction (C14:N14) s how h ow m any
surf b oards S urfs U p s hould p roduce e ach m onth s o a s t o a chieve t he m aximum p rofit o f $31,150.
Set Objective Cell: TotalProfit To: Max
By Changing Variable Cells:
RegularProduction, OTProduction Subject to the Constraints:
RegularProduction <= MaxRegular OTProduction <= MaxOT
EndingInventory >= SafetyStock Solver Options:
Make Variables Nonnegative Solving Method: Simplex LP
4.S2Aggregate Planning: Manpower Hiring/Firing/Training
Cool P ower p roduces a ir c onditioning u nits f or l arge c ommercial p roperties. D ue t o t he l ow
cost a nd e fficiency o f i ts p roducts, t he c ompany h as b een g rowing f rom y ear t o y ear. A lso, d ue to s easonality i n c onstruction a nd w eather c onditions, p roduction r equirements v ary f rom
month t o m onth. C ool P ower c urrently h as 10 f ully t rained e mployees w orking i n
manufacturing. E ach t rained e mployee c an w ork 160 h ours p er m onth a nd i s p aid a m onthly wage o f $4000. N ew t rainees c an b e h ired a t t he
b eginning o f a ny m onth. D ue t o t heir l ack o f initial s kills a nd r equired t raining, a n ew t rainee o nly p rovides 100 h ours o f u ful l abor i n their f irst m onth, b ut a re s till p aid a f ull m onthly w age o f $4000. F urthermore, b ecau o f required i nterviewing a nd t raining, t here i s a $2500 h iring c ost f or e ach e mployee h ired. A fter one m onth, a t rainee i s c onsidered f ully t rained. A n e mployee c an b e f ired a t t he b eginning o f any m onth, b ut m ust b e p aid t wo w eeks o f s everance p ay ($2000). O ver t he n ext 12 m onths, Cool P ower f orecasts t he l abor r equirements s hown i n t he t able b elow. S ince m anagement anticipates h igher r equirements n ext y ear, C ool P ower w ould l ike t o e nd t he y ear w ith a t l east 12 f ully t rained e mployees. H ow m any t rainees s hould b e h ired a nd/or w orkers f ired i n e ach month t o m eet t he l abor r equirements a t t he m inimum p ossible c ost? F ormulate a nd s olve a linear p rogramming s preadsheet m odel.
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 1600 2000 2000 2000 2800 3200 3600 3200 1600 1200 800 800
This i s a d ynamic p roblem w ith 12 t ime p eriods (months). T he a ctivities a
re t he n umber o f
workers t o h ire a nd f ire i n e ach o f t he 12 m onths.
To g et s tarted, w e s ketch a s preadsheet m odel. E ach o f t he 12 m onths w ill b e a s eparate column i n t he s preadsheet. F or e ach m onth, t here a re c hanging c ells f or b oth t he n umber o f workers h ired a nd f ired. B ad o n t he v alues o f t he c hanging c ells, w e c an d etermine t he number o f t rainees a nd t rained e mployees. T he n umber o f l abor h ours g enerated b y t he employees m ust b e a t l east t he r equired l abor h ours e ach m onth. F inally, l abor c osts (for trainees a nd t he t rained w orkforce), h iring c ost, a nd s everance p ay l eads t o a t otal m onthly cost. T he g oal w ill b e t o m inimize t he t otal c ost o ver a ll 12 m onths. T his l eads t o t he following s ketch o f a s preadsheet m odel.
Labor Monthly Wage
国家英语怎么说Hiring Cost Severance Pay
Labor Hours/Trainee/Month
Labor Hours/Trained Worker/Month
巨暹罗鲤
Starting Trained Workforce
Minimum to Start the Trainees
Next Year
Trained Employees >=
三峡拼音
Labor Hours Available
>=
Required Labor Hours
