Question

please help with 3 question in detail on excel. thank you

CASE STUDY The Sourcing Decision at Forever Young Forever Young is a retailer of trendy and low-cost apparel in the United States. The company divides the year into four sales seasons of about three months each supplier costs 55 yuan/unit (inclusive of all delivery and brings in new merchandise for each season. The company has historically outsourced production to China given the lower costs. Sourcing from the Chinese costs), which at the current exchange rate of 6.5 yuan/ Chapter 6. Designing Global Supply Chain Networks 177 $ gives supplier, however, has a long lead time, forcing Forever Forever Young to keep bringing product in a little bit at a Young to pick season. This does not leave the company any flexibility used, the company is able to meet all demand in each if actual demand differs from the order size. A local supplier has come to management with a proposal to supply product at a cost of $10/unit but do so quickly enough that Forever Young will be able to Young. make supply in the season exactly match demand. Management is concerned about the higher variable cost but finds the flexibility of the onshore supplier very attractive. The challenge is to value the responsiveness The local supplier has also offered another proposal that provided by the local supplier. a variable cost of under $8.50/unit. The Chinese The short lead time of the local supplier allows an order size well before the start of the time based on actual sales. Thus, if the local supplier is period without having any unsold inventory or lost sales. In other words, the final order from the local supplier will exactly equal the demand observed by Forever A Potential Hybrid Strategy would allow Forever Young to use both suppliers, each playing a different role. The Chinese supplier would produce a base quantity for the season and the local supplier would cover any shortfalls that result. The short lead time of the local supplier would ensure that no sales are lost. In other words, if Forever Young committed to a Uncertainties Faced by Forever Young To better compare the two suppliers, management identifies demand and exchange rates as the two major uncertainties faced by the company. Over each of the next two periods (assume them to be a year cach), demand may go up by 10 percent with a probability of 0.5 or down by 10 percent with a probability of 0.5. Demand in the current period was 1,000 units. Similarly, over each of the next two periods, the yuan may strengthen by 5 percent with a probability of 0.5 or weaken by 5 percent with a probability of 0.5. The exchange rate in the current period was 6.5 yuan/S. base load of 900 units with the Chinese supplier in a given period and demand was 900 units or less, nothing would be ordered from the local supplier. If demand, however, was larger than 900 units (say 1,100), the shortfall of 200 units would be supplied by the local supplier. Under a hybrid strategy., the local supplier would end up supplying only a small fraction of the season's demand. For this extra flexibility and reduced volumes, however, the local supplier proposes to charge S11/unit if she is used as part of a hybrid strategy. Ordering Policies with the Two Suppliers Questions Given the long lead time of the offshore supplier, Forever Young commits to an order before observing L Draw a decision tree reflecting the uncertainty over the next two periods. Identify each node in terms of demand and exchange rate and the transition probabilities 2. If management at Forever Young is to pick only one of the two suppliers, which one would you recommend? What is the NPV of expected profit over the next two periods for each of the two choices? Assume a discount factor of -01 per period 3. What do you think about the bybrid approach? Is it worth paying the local supplier extra to use her as part of a hybrid strategy? For the hybrid approach, assume that managemens will order a base load of 900 units from the Chinese supplier tfor each of the two periods, making up aay shortfall in each period at the local sapplier. Evaluate the NPV of expected profits for the hybrid option assuming a discount factor of k-0.1 per period any demand signal. Given the demand uncertainty over the next two periods and the fact that the margin from each unit (margin of about $11.50) is higher than the loss if the unit remains unsold at the end of the season (loss of about $8.50), management decides to commit to an order that is somewhat higher than expected demand. Given that expected demand is 1,000 units over cach of the next two periods, management decides to order 1,040 units from the Chinese supplier for each of the next two periods. If demand in a period turns out to be higher than 1.040 units, Forever Young will sell 1,040 units. However, if demand turns out to be lower than 1,040, the company will have left over product for which it will not be able to recover any revenue The Sourcing Decision at Forever Young Forever Young is a retailer of trendy and low-cost apparel in the United States. The company divides the year into four sales seasons of about three months each and brings in new merchandise for cach season. The company has historically outsourced production to China given the lower costs. Sourcing from the Chinese supplier costs 55 yuan/unit (inclusive of all delivery costs), which at the current exchange rate of 6.5 yuan Chapter 6 Designing Global Supply Chain Networks 177 S gives a variable cost of under S8.50/unit. The Chinese supplier, however, has a long lead time, forcing Forever Young to pick an order size well before the start of the season. This does not leave the company any flexibility The short lead time of the local supplier allows Forever Young to keep bringing product in a little bit at a time based on actual sales. Thus, if the local supplier is used, the company is able to meet all demand in cach period without having any unsold inventory or lost sales. In other words, the final order from the local supplier order size. if actual do and differs from A local supplier has come to management with a proposal to supply product at a cost of $10/unit but do so will exactly equal the demand observed by Forever quickly enough that Forever Young will be able to Young. make supply in the season exactly match demand. Management is concermed about the higher variable cost but finds the flexibility of the onshore supplier very A Potential Hybrid Strategy altractive. The challenge is to value the responsiveness The local supplier has also offered another proposal that provided by the local supplier. would allow Forever Young to use both suppliers, each playing a different role. The Chinese supplier would produce a base quantity for the season and the local supplier would cover any shortfalls that result. The short lead time of the local supplier would ensure that no sales are lost. In other words, if Forever Young committed to a base load of 900 units with the Chinese supplier in a given period and demand was 900 units or less, nothing Uncertainties Faced by Forever Young To better compare the two suppliers, management identifies demand and exchange rates as the two major uncertainties faced by the company. Over each of the next two periods (assume them to be a year each) demand may go up by 10 percent with a probability of 0.5 or down by 10 percent with a probability of 0.5 Demand in the current period was 1,000 units. Similarly. each of the next two periods, the yuan may strengthen by 5 percent with a probability of 0.5 or weaken by 5 percent with a probability of 0.5. The exchange rate in the current period was 6.5 yuan/S. would be ordered from the local supplier. If demand. however, was larger than 900 units (say 1,100), the shortfall of 200 units would be supplied by the local supplier. Under a hybrid strategy, the local supplier would end up supplying only a small fraction of the season's demand For this extra flexibility and reduced volumes, however, the local supplier proposes to charge S11/unit if she is used as part of a hybrid strategy. Ordering Policies with the Two Suppliers Questions Given the long lead time of the offshore supplier Forever Young commits to an order before observing any demand signal. Given the demand uncertainty over the next two periods and the fact that the margin from each unit (margin of about $11.50) is higher than the 1. Draw a decision tree reflecting the uncertainty over the next two periods. Identify cach node in terms of demand and exchange rate and the transition probubilities. 2 If management at Foeever Young is to pick only cac of the two suppliers, which cne would you recommend? What is the NPV of expected profit over the nest two periods for each of the two choices? Assume a discount factor of loss if the unit remains unsold at the end of the season (loss of about S8.50), management decides to commit to an order that is somewhat higher than expected demand Given that expected demand is 1000 units over cach of k-0.1 per period 3. What do you think about the hybrid approach? Is it worth paying the local supplier extra to use her as part of a hybrid strategy? For the hybrid approach, assume that management will order a base load of 900 units from the Chinese supplier for each of the two periods making up any shortfall in cach period at the local supplier. Evaluate the NPV of expected peofits for the hybrid option assuming a discount factor of k 0.1 per period. the next two periods, management decides to order 1,040 units from the Chinese supplier for each of the next two periods. If demand in a period turns out to be higher than 1,040 units, Forever Young will sell 1,040 units. However, if demand turns out to be lower than 1040, the company will have left over product for which it will not be able to recover any revenue.

Answer 1

boubion to 1 yellouing unjarmation ase forouided uoiLh the 1000 units Clero1ent demand 0 153854 A an Clonent enchange malea 5 Bv e each al the neat 2upoiods demand umay ga up ihy loy usitha perababulity l O 5 emand umay godouan by 16 Lsitha probability oid 5 percbabuty ban umay steranathen by 5 usitha ban umay weaken by by uoith a peababidity of D-5 eenasnie umay take place galang Bvan the unent pesiod-the Cuonenk demand -1e00 units erab0 5 Perab 6.5 emand goe demand ges douan 1000x o ) 00x1 ) \ om units nits 16-5) 10-5 (0.5 10-5) enchange Srats gos dovan erchange rake ges up Cncharge gale Jea ip Lo 1538 5x 1053 016154 enchange grate Jocs douan Lo 16154 (0.14616) D-1461 1hus at dhe end e the dierat eriod we thaue uoreu Scenaguig demands &caykale Porbub 6-16154 Sceranis 7 Demand going up and enchange aate 1GO 0 25 05y D 5 Jeing up beeranio & Demand geina up and enchange grate Il00 O14616 025 6 Sx05 goi na udeuen ROcmand goira deuon and enchange grale eenanie 3 6-16154 8 25 Joig up 165x06 emand goirg douon ad enchange grbe Joing dlouan 014616 enanioy -25

elAsSMAte pate Page Aud the ende o year peried 2 the decisio teee losulds AS2 emand douon Berand up Enerenge sale up ROemand deuai emand up Cnchange ralk deuon Enchang raledoon (P-03 erehange aate up (P:0 25) (09 (9.9) (05) enand O5 tos) (65) K p dn940 andts)p douan e emad HA06 Wenand up 210 Scmand Denand Aernand Senan Denand tt (66 Enchange enehange aledoun Cxharg eale up deun daan u deun deon hang nhonge tp up TVHAI6 A -15347 Io 16 Do D H 616x 015 n-13 88 To16154x trextante Int615Hx151 0451 10 16154 X051 014962 0-16962 0.1534 see dabla belauo CTnst, delauL Ihus at hu end o yoid , ue thau 16 cenagi as each uoidha 4 enobabi liy Eo97 I05YDSXDRD) 00635 nalt ub TD-1654 ×10ら]016962 CoLmandges up Cnopxi-112 units) Seenaoio 1 LO-25) L0onits ennchange aabe deun I6 161 b 153 10 00 Demand gou douan hang ale up L1100x0 9 996 units) TOI6194x1651 6-t6t6 enchargt art dean ) eanchang TO IG154x 31:0134 Bcnandg ed wp ale uLEEIDMA 65)TE462 tnchange rale b61-65 1100x11-120unuts dn 14616x 095= 0-13885 0 15347 seevepeio &Lo2) Bemand goudeen Enchaugl Aak up 0.1486×1-05 0534チ unils enhangb YU Aale douon 011616x 0 95 013885 Benand goe echangy Aaleupto 16154 x116962 10ox 11-84 940 units nids enchagralk dea to16154x 0-951 013 36 9o0 units gou douen enhangi rake up ro:16 5Hy Lo 6-1696 2 Denad Btoun han ale deron Ia16 42 01534. 100 x09 841-erv ts

elASSMate Date Page Semand goes up emhange raleup r614616x=Dh 0-153 41 900x199ounits Seeharig 4 N aate douon ID-14616X6-951- 0-13885 encharge enard Aenrange goef douans ralt up 900 x89=810 units DaYY LO14616X1 051 015347 :LS grale douan ennhange 40Y l0 14616x0 951 013 85 nons TS uah o03790r FRANRIR A POXH1O1 eoTx paid 0 CEAT 0 PE2I:O UNel dob 42

clAsSMAte Date. Page Al he end you ed 1,V losts in core of ead of itbe Supplior bupp lio Lupplien vaniable costs ib40x55X0-16 1s4) $4240 Scenani e 1 cdemand:110D unit e1riatunity csss Cil00-1040)xte9 50 50 otal-$9810 1106x 10-$11,0O vagiable oools - LI0H055X0 HE6) beehari & idenand 110D 830 Wnitk (i1o6-1046) x9.50 $570 taetunily costs $8950 Aelal 900x 10. 1000 vaniable cols LI640x 95x DI6154) $9a40 47 000 9 60x 10 1046x55 X 614616 beenoie 8.960 Supplio 1-9810x 0 5 x 01) + 8936x05xo HOT3OXTNX&DI(92, fV Msts undess (9246x6 5X6) t8306X6 5x6)) Pv o lorts und Supplie &C1000x 0.sxo) 1 L11,006 x65x0-) 4(9000x0.s xoDt (10ox 6 5x6.1) 2000

Page dyeasy R lue haue the yall ouing lerasias Ad the ead Supplicu 1 alt Sufplicn DY lo$12190 (I)enard CalDurit 1640X55X0 16962 -3A102 Lortunity icass Liio la42) 4 50 = 4 16)5 121by 16 I) &enand 310 unids) 08/84881 vaociabe canis: 164oY5x 015344) 48 48 offiertunity coris Li210-1040 ) xq.50 16) 5 ARRAY16, 393 ah Temend (0unids). V4riable casts 390x 10 f900 L1646x 88X0-169 62) =$910 2 9960 990 x10 4cmard (490 unik) 09riakle cordS 1010 X69x0-169 62) =8978dal& 1316-0 w aSN denend 0unide) vagiable cosls Li046x 05X 0:150341)8118| eppuert unity coe r84 D-1046) Xq.56 1615 B10,393 vagriabe Costs CID40 X D0X O-19895) -4191 3 ffuert unity coru CIUD-1040) X9 50 viaerd (1210 uni) 1/bY 16 $11e0 $1615 $955+ Bemand (196 vagui able larts C10M0X 55x 015343)818 4r 996 x1D 9980 Vaoiable icosts 990x 10

ASSMALE Date Page 9900 990 x 10 X)emand 4 1640 Y 55X 0169 62 1103 990x 1D 1610 XB5 X 0 15346 831A 48190 810x 16 (xi)mand 8lDuniks) 1616 y 55X 6.1696 9 102 9103 21DX 16 48190 [xli)enand l8IDuni) 1846×クらメ 0'15346 990X164 990d Senand 190unita) 1646×ららX 6)534チ 8779 tao aldniso emand 1940uniks) 1100 (x iu) 190x16 1846 x 55X O'13885 1912 01Y Off 4emand (810units) 1646B9 XD 15347 BIDX 16-81&D loNaldwid 1646y 55X O13883 ernand 1810unis) e) 手1942 C4189x918 In dh giners perahleme ithe we cannal lind the N{V elling peite has inot been giuery NAY RE 9882 0COAONd Jrtound en Sutfdice 1 92.n4Vdievy Luppelilen& Uaks unole 100 nt