ematik Kaydet Chapter 5 Assignment Excel caner er Sayfa Dzeni Formuler Veri Gozden Geir Garum Yard Paylas Aldamalar Liburton Se (ntage Lyrlenme VUNUD Birli Avraci * Dosya Giris Ede X kes Lh Kopyala ~ 3 mlynesi Pann 2 Mein Kayde === Birletir Ortale D EX The SI Biim Vurgus Kosul Tablo Clarak ParaBinmi Bisikendime - Biimlerdi: Stilor KT A - Otomatic Toplam Dolour Terrie 0-A- ParaBirmio Virgil Srais veftre Dulve Uygule- Ses - Tenis tra Herr Den Das J K L M N o P 0 R S U V W Yazdrma... - CHAPTER 5 B CDE F G H / Bill Johnson, sales manager, and Diane Buswell, controller, at Current Designs are 8 beginning to analyze the cost considerations for one of the composite models of the 9 kayak division. They have provided the following production and operational costs 10 necessary to produce one composite kayak 11 12 Kevlar $250 per kayak 13 Resin and supplies $100 per kayak 14 Finishing kit (seat, rudder, ropes, etc.) $170 per kayak 15 Labor $420 per kayak 18 Selling and administrative expenses-variable $400 per kayak 17 Selling and administrative expenses-fixed $119,700 per year 18 Manufacturing overhead-fixed $240,000 per year 19 20 Bill and Diane have asked you to provide a cost-volume-profit analysis, to help them 21 finalize the budget projections for the upcoming year. Bill has informed you that the 22 selling price of the composite kayak will be $2,000. 23 24 Required 25 Use sheet tab Part 1 26 1. (a) Calculate variable costs per unit 27 (b) Determine the unit contribution margin. 29 (c) Using the contribution margin per unit, determine the break even point in units for this product ine (di Assume that Current Designs plans to earn $270,600 on this product line. Using the 31 contribution margin per unit, calculate the number of units that need to be sold to 32 achieve this goal (e) Based on the most recent sales forecast Current Designs plans to sell 1,000 units of 34 this model. Usng your results from part (C), calculate the margin of safety and the 35 margin of safety ratio 36 (1) Create a break-even chart for Current Designs using Excel's charltools. 37 38 WHAT IF? 39 2. Suppose Current Designs locales a suppier that is able to provide fast-dry resin. While 40 the resin cost will increase by $20 per kayak for this fast dry formula, the fixed aucun wehead crusts will inc $72 000 nervosa eliminating the rental of Problem Statement Part Part 2 110 Aramak iin buraya yazin O 10 TUR 13:20 ALIM21 Chometik Kaydet Chapter 5 Assignment - Excel canerne Dosya Giris El Saya Dzen Formulier Veri Gzden geir Gorum Yardm Liberation Sam Otomatik Toplam htuge Lyarlar vurgus XK I Vapight Kopyala Big Boyan PAN 14 AA == 1 Mutni Kayde === Britierte Doldur Vurus Bnik Auraci Parabrim la Virgul KT Ik SI Biom Temele Kogulu Tablo Ost ParaBinmi Biiminde Bismland Shiller Sriavelin Uyyu - Downlom Yar Tipi Hipane Hummel J M N P Q R S T U V Yazdm. CHAPTER 5 A B C D F 1 13 Resin and supplies $100 per kayak 14 Finishing kit (seat, rudder, ropes, etc.) $170 per kayak 15 Labor $420 per kayak 16 Selling and administrative expenses variable $400 per kayak 17 Selling and administrative expensesfixed $119,700 per year 18 Manufacturing overhead-fixed $240,000 per year 19 20 Bill and Diane have asked you to provide a cost-volume-profit analysis, to help them 21 finallze the budget projections for the upcoming year. Bili has informed you that the 22 selling price of the composite kayak will be $2,000. 23 24 Required 25 Use sheet tab Part 1 26 1. (a) Calculate variable costs per unit 27 (1) Determine the unit contribution margin 28 (c) Using the contribution margin per unit, determine the break-even point in units for this 29 product line. 30 (aAssume that Current Designs plans to carn $270,600 on this product line. Using the 31 contribution margin per unit, calculate the number of units that need to be sold to 32 achieve this goal 33 (e) Based on the most recent sales forecast, Current Designs plans to sell 1.000 units of 34 this model. Using your results from part (c), calculate the margin of safety and the margin of safety ratio 35 36 (1) Create a break-even chart for Current Designs using Excel's chart tools. 37 36 WHATF? 39 2. Suppose Current Designs locates a supplier that is able to provide fast-dry resin While 40 the resin cost will increase by $20 per kayak for this fast-dry formula, the fixed 41 manufacturing overhead costs wil decline by $72.980 per year by eliminating the rental of 42 a drying machine. Perform what-il analysis to determine how many units Current Designs will need to produce and self these changes occur. Ilustrate this change with a break- even chart. Should Current Designs make the change? 45 46 Problem Statement Part 1 Part 2 + Say 3 Aramak iin buraya yazin O i Cromatik Kaydet Chapter 5 Assignment - Excel Cangrer 2 Dosya Giris Ede Sayfa Duzeni Formuler Veri Guaden Geir Garum Yardm Liberation San 12A == te Uyum Vurgus D EX 2 Mein Kayde 45 Bireytir Ortale VURU Birli Avraci ParaBirmio Virgil Paylas Aldamalar Otomatik Toplam Dolour Srais veftre ulve Terize Uygule- Ses - Dnya Yapter The -A- Sirim KTA - SI 3 macs Kosul Tablo Clarak ParaBirimi Bisikendime - Biimlendi- Para Tan Tipi Hin Saya HO 18 H J K M N 0 P Q R S T U V 4A 0 C D F G 1 Chapter 5: Part 1 2 3 Data 4 kevare 5250 per kayak 5 Resin and supplies S100 per kayak 8 Finishing kit (seat, rudder, ropes, etc.) $170 per kayak 1 Labor S420 per kayak 8 Selling and administrative expenses variable S400 per kayak 9 Seling and administrative expenses fixed $119.700 per year 10 Manufacturing overhead-fixed $240.000 per year 11 Seiling price per unit $2.000 per kayak 12 Proht desired $270 DO 13 14 15 Required 16 Follow the step by step directions to perform cost volume profit analysis and to create a break even chart. 17 18 (a) 19 20 21 22 23 Total 24 25 (b) Contribution margin per unit Hreak-even point units 21 (c) 28 Unils to achieve goal units 30 31 (e) 33 34 35 Actual (expected sales Hreak-even sales Margin of safety in dollars Margin of safety ratio Problem Statement Part 1 Part 2 + 110 Aramak iin buraya yazn O E. 40 TUR 1321 ALIM.21 Otomatik Kaydet Chapter 5 Assignment - Excel Cangres 2 Dosya Giris Ede Sayfa Dzeni Formuler Veri Guaden Geir Gerorum Yardm Liberation San 12 AA == te Uyum VUUD Birli Avraci D EX Mein Kayde Birgir ve Ortale - Kopyala Yapter 3 im Besi Para Vurgus Kosul Tablo Clarak ParaBirimi Bisikendime - Biimlerdi- Paylas Aldamalar Otomatik Toplam Doldur Sarais vef He bulve Temiz Uygule- Sus Duniya -A- Sirim The K TAD ParaBirmio Virgol ==== TATTI Hira Her 18 1 D E F G H J K M N P Q R S T V units units 0 0 1000 A B C 19 20 21 22 23 Total 24 25 (b) Contribution margin per unit 28 27 (c) Break even point 28 29 (d) Units to achieve goal 30 31 (e) Actual (expected sales 32 Break even sales 33 Margin of safely in dollars 34 Margin of safety ratio 35 38 ) Number of units 0 37 Sales revenue Totalfixed costs Total costs 40 41 12 43 44 45 48 17 48 49 50 5 52 53 54 Problem Statement Part 1 Part 2 + 110 Aramak iin buraya yazin O E. 10 TUR 1321 ALIM21 F Demak Kaydet Chapter 5 Assignment - Excel Cangres 2 Dosya Girls Ede Sayfa Duzeni Formuler Veri Gozden Geir Gororum Yardm Paylas Aldamalar Liberation San 12A intuja lyuriam Vurgus Tabio C Para Birimi VUTUD Bir Ayraci ParaBirmiloj Virgo 27 0 kes Kopyala 30 cm lys Pann va K TA- Sirim Kosil A- de Otomatic Toplam ES Colour Sris vette Bulve Tere Uygula Sus Dunia Tan Tipi MENT Heler 1 u L L M N P R S V w 63 1 Part2 A B c E F G H 2 3 Data Part2 4 Kevlar $250 per kayak 5 Resin and supplies per kayak 6 Finishing kt (seat, rudder, ropes, etc.) (, , $170 per kayak 7 Labor $420 per kayak 8 Selling and administrative experises variable $400 per kayak 9 Selling and administrative expenses fixed $119.700 per year 10 Manufacturing overhand fixed per year 11 Selling priceperunt $2.000 per kayak 12 Profr desired $270 600 13 14 15 Required 18 Folow the step-by-step directions to perform col-volume profil analysis and lu create a break even chall 17 18 (a) 19 20 21 22 23 Tule 24 25 b) Contribution margin perint 26 27 c) Break-even point unts 28 28 (d) Units to achieve goal unts 30 31 (e) Actual (expected) sales 32 Break-even sales 32 Margin of safety in dolans 34 Margin of satety rato 0 1000 36 U Number of units 37 Sales revenue 38 Totalfoed costs 39 Total costs 40 Problem Statement Gir Part 1 Part 2 + Aramak iin buraya yazn O Kd 10 TUR 132 ALIM21