Pano Yaz Tipi Hizalama Sayu L25 fi J K B D F. G H 1 4 Excel Functions and Tools: Copying Formulas; Cell-Referencing to other 5 Worksheets' Using Sparklines 6 7 As you learned in the previous chapters, Current Designs has two main product lines- 8 composite kayaks, which are handmade and very labor-intensive, and rotomolded 9 kayaks, which require less labor but employ more expensive equipment Current 10 Designs' controller, Diane Buswell, is now evaluating several different methods of assigning overhead to these products. It is important to ensure that costs are 11 12 appropriately assigned to the company's products. At the same time, the system that is used must not be so complex that its costs are greater than its benefits. Diane has 13 decided to use the following activities and costs to evaluate the methods of assigning 14 overhead. 15 16 Designing new models $121,100 17 Creating and testing prototypes 152,000 18 Creating molds for kayaks 188,500 19 Operating oven for the rotomolded kayaks 40,000 20 Operating the vacuum line for the composite kayaks 28,000 21 Supervising production employees 180,000 22 Curing time (the time that is needed for the chemical processes to finish 23 before the next step in the production process, many of these costs are 24 related to the space required in the building) 190.400 25 Total $900,000 26 27 As Diane examines the data, she decides that the cost of operating the oven for the 28 rotomolded kayaks and the cost of operating the vacuum line for the composite kayaks 29 can be directly assigned to each of these product lines and do not need to be allocated with the other costs. 30 31 Required 32 Use sheet tab Part 1 33 1. For purposes of this analysis, assume that Current Designs uses $234,000 in direct labor 34 costs to produce 1,000 composite kayaks and $286,000 in direct labor costs to produce 35 4,000 rotomolded kayaks each year. 36 (a) One method of allocating overhead would allocate the common costs to each product 37 line by using an allocation basis such as the number of employees in working on each 38 type of kayak or the amount of factory space used for the production of each type of 39 kayak. Diane knows that about 50% of the area of the plant and 50% of the Problem Statement Part 1 Part 2 Dosya Giris Ekle Sayla Dzeni Formller Veri Gzden Geir Grnm Yardim Liberation Sans X kes [ Kopyala - Biim Boyac Yaptur 11 - A A 9- 13 Metni Kaydet KTA BOA E Birletir ve Ortala Genel - E-% Koullu Tablo Ola Biimlendirme Biimlend Pano Yaz Tipi F Hizalama Sayu L25 f J 1 + 4 B D F G H 31 Required 32 Use sheet tab Part 1 33 1 For purposes of this analysis, assume that Current Designs uses $234,000 in direct labor 34 costs to produce 1,000 composite kayaks and $286,000 in direct labor costs to produce 35 4,000 rotomolded kayaks each year. 36 (a) One method of allocating overhead would allocate the common costs to each product 37 line by using an allocation basis such as the number of employees in working on each 38 type of kayak or the amount of factory space used for the production of each type of 39 kayak. Diane knows that about 50% of the area of the plant and 50% of the 40 employees work on the composite kayaks, and the remaining space and other 41 employees work on the rotomolded kayaks. Using this information, and remembering 42 that the cost of operating the oven and vacuum line have been directly assigned, 43 determine the total amount to be assigned to the composite kayak line and the 44 rotomolded kayak line, and the amount to be assigned to each of the units in each 45 line. 46 (b) Another method of allocating overhead is to use direct labor dollars as an allocation 47 basis. Remembering that the costs of the oven and the vacuum line have been 48 assigned directly to the product lines, allocate the remaining costs using direct labor 49 dollars as the allocation basis. Then, determine the amount of overhead that should 50 be assigned to each unit of each product line using this method 51 (c) Activity-based costing requires a cost driver for each cost pool. Use the following 52 information to assign the costs to the product lines using the activity-based costing 53 Driver Amount for 54 Composite Rotomolded Activity Cost Driver Kayaks Kayaks 55 Designing new models Number of models 3 1 56 Creating and testing prototypes Number of prototypes 6 2 57 Creating molds for kayaks Number of molds 12 1 58 Supervising production employees Number of employees 12 12 59 Curing time Number of days of curing time 15,000 2,000 60 61 What amount of overhead should be assigned to each composite kayak using this method? What amount of overhead should be assigned to each rotomolded kayak using 62 this method? 63 64 (d) Which of the three methods do you think Current Designs should use? Why? 65 WHAT IF? Pano G % 88 Yazi Tipi Koullu Tablo Olarak Biimlendirme Biimlendit- Hizalama L25 5 X Sayi 5 fo J M B C D E F G H 1 dollars as the allocation basis. Then, determine the amount of overhead that should be assigned to each unit of each product line using this method. (c) Activity-based costing requires a cost driver for each cost pool. Use the following information to assign the costs to the product lines using the activity based costing Driver Amount for Composite Rotomolded Activity Cost Driver Kayaks Kayaks Designing new models Number of models 3 1 Creating and testing prototypes Number of prototypes 6 2 Creating molds for kayaks Number of molds 12 1 Supervising production employees Number of employees 12 12 Curing time Number of days of curing time 15,000 2.000 What amount of overhead should be assigned to each composite kayak using this method? What amount of overhead should be assigned to each rotomolded kayak using this method? (d) Which of the three methods do you think Current Designs should use? Why? WHAT-IF? 2. Perform what-if analysis to answer the following independent questions: a. Suppose the number of prototypes of composite kayaks increases to 45 as a result of additional testing required by the American Boating Council. Create a column sparkline using the overhead costs assigned to each kayak model under activity-based costing with and without this change. Visually identify what effect this will have on the cost of each model of kayak. What occurs to the total costs of employee supervision? b. Suppose instead, that the estimated cost of creating and testing prototypes increased by $100,000. Determine the percentage change in the prototype cost allocations for each kayak model. Explain how the effect of this change in the unit cost of each model of kayak differs from the change in number of prototypes 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 BE 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 E Problem Statement Part 1 Part 2 Aramak iin buraya yazn s Chapter 4 Assignment ! tomatik Kaydet Veri Grnm Yardm Formller Gzden Geir Elde sya GI Sayfa Dzeni == Metni Kaydet istee Uyarlanmis X Kes L Kopyala - apistat 3 Biim Boyac Pano is Liberation Sans - 11-AA KT A---A- % 99.99 # Birletir ve Ortala Koullu Tablo Olara Biimlendirme Biimlendir 5 Sayi Hizalama Yaz Tipi F G H $ L51 fx B D E 4 5 Activity 6 Designing new models 7 Creating and testing prototypes 8 Creating molds for kayaks 9 Operating oven for the rotomolded kayaks 10 Operating the vacuum line for the composite kayaks 11 Supervising production employees Curing time (the time that is needed for the chemical processes to finish before the next step in the production process; many of 12 these costs are related to the space required in the building) 13 Total 14 15 Cost 121.100 152.000 188.500 40.000 28.000 180.000 + 190.400 900.000 $ Driver Amount for Composite Rotomolded Kayaks Kayaks 3 1 6 2 12 1 12 12 15.000 2.000 16 Activity Cost Driver 17 Designing new models Number of models 18 Creating and testing prototypes Number of prototypes 19 Creating molds for kayaks Number of molds 20 Supervising production employees Number of employees 21 Curing time Number of days of curing time 22 23 Direct labor costs for composite kayaks $ 234.000 24 Direct labor costs for rotomolded kayaks $ 286.000 25 Number of composite kayaks 1.000 26 Number of rotomolded kayaks 4.000 27 28 Percentage of employees and percentage of square feet used: 29 For composite kayaks 50% 30 For rotomolded kayaks 50% 31 32 33 Required Problem Statement Part 1 Part Aramak iin buraya yazin cement au Istege Oyarlanmis [ Kopyala Yaptr 3 Biim Boyac KTA la A- EE Birletir ve Ortala - %, 8 Koullu Tablo Olar Biimlendirme Biimlendi Pano Yaz Tipi Hizalama Sayi [51 fo H 4 B D E F G 31 32 33 Required 34 Follow the step-by-step directions to determine the cost allocations for Current Designs. 35 36 37 (a) Composite Rotomolded 38 Directly assigned costs 39 Remaining cost allocation: 40 Remaining costs 41 Cost allocation 42 Total direct and allocated costs 43 Number of kayaks 44 Cost assigned to each kayak 45 46 (b) Composite Rotomolded 47 Directly assigned costs 48 Remaining cost allocation: 49 Remaining costs 50 Overhead rate 51 Cost allocation 52 Total direct and allocated costs 53 Number of kayaks 54 Cost assigned to each kayak 55 56 (C) Estimated Expected Overhead Use of Cost Activity-Based Overhead 57 Activity Cost Pools Cost Drivers Rates 58 Design 59 Prototypes 60 Molds 61 Supervision Problem Statement Part 1 Part 2 Aramak icin buraya yazn O S Otomatik Kaydet H Chapter 4 Assignment (1) - Excel PAre Dosya Girls Ekle Sayfa Dzeni Formller Veri Gzden Geir Grnm Yardm Istege Uyarlanmis B Yaptir X kes [ Kopyala Biim Boyac Pano Liberation Sans 11 AA == 29 Metni Kaydet KTA OA Birletir ve Ortala Yaz Tipi Hizalama EN % 9 % Koullu Tablo Olarak Biimlendirme - Biimlendir- Sayi 151 A D E F G H Activity-Based Overhead Rates A B 52 Total direct and allocated costs 53 Number of kayaks 54 Cost assigned to each kayak 55 56 (c) Estimated Expected Overhead Use of Cost 57 Activity Cost Pools Cost Drivers 58 Design 59 Prototypes 60 Molds 61 Supervision 62 Curing time 63 64 Composite Expected Use of Cost Overhead 65 Activity Cost Pools Drivers Rates 66 Design 67 Prototypes 68 Molds 69 Supervision 70 Curing time 71 Total amount allocated 72 Cost directly assigned 73 Total cost 74 Number of kayaks 75 Cost assigned to each kayak 76 77 (d) 78 79 80 Problemy Statement Part 1 Part 2 Rotomolded Expected Use of Cost Overhead Drivers Rates Cost Assigned Cost Assigned Part2 0 Otomatik Kaydet Chapter 4 Assignment (1) - Excel Gitis Sayla Dazeni Formoller Veri Gzden Geir Grnm Yardim Dosya Elle X kes h Kopyala Yapist Biim Boyacist Istee Uyarlanmis Liberation Sans -11 " ' KTA - EB-A 29 Metni Kaydet Birletir ve Ortala KE - %38 Binlik Ayrac Koullu Tablo Olarak Virgl Biimlendirme Biimlendir- Still Pana Yaz Tipi Hitalama Say! L51 X D E H 1 F G Rotomolded Expected Use of Cost Overhead Drivers Rates Cost Assigned Cost Assigned B. 64 Composite Expected Use of Cost Overhead 65 Activity Cost Pools Drivers Rates 66 Design 67 Prototypes 68 Molds 69 Supervision 70 Curing time 71 Total amount allocated 72 Cost directly assigned 73 Total cost 74 Number of kayaks 75 Cost assigned to each kayak 76 77 (d) 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 Problem Statement Part 1 Part Aramak iin buraya yazin o BE S Biim Boyacisi Biimlendirme Biimlendir Pana Yaz Tipi Hizalama Say! H12 X E F H I J K G Part 2 A B D 1 Chapter 4: 2 3 Data 4 5 Activity 6 Designing new models 7 Creating and testing prototypes 8 Creating molds for kayaks 9 Operating oven for the rotomolded kayaks 10 Operating the vacuum line for the composite kayaks 11 Supervising production employees 12 Curing time (the time that is needed for the chemical processes to 13 Total 14 15 Cost $ 121.100 252.000 188.500 40.000 28.000 180.000 190 400 $1.000.000 + Driver Amount for 2.000 Composite Rotomolded 16 Activity Cost Driver Kayaks Kayaks 17 Designing new models Number of models 3 1 18 Creating and testing prototypes Number of prototypes 6 2 19 Creating molds for kayaks Number of molds 12 1 20 Supervising production employees Number of employees 12 12 21 Curing time Number of days of curing time 15.000 22 23 Direct labor costs for composite kayaks $ 234.000 24 Direct labor costs for rotomolded kayaks $ 286.000 25 Number of composite kayaks 1 000 26 Number of rotomolded kayaks 4.000 27 28 Number of employees percentage 29 For composite kayaks 50% 30 For rotomolded kayaks 50% 31 32 33 Required 34 Follow the step-by-step 'what-if directions to determine the cost allocations for Current Designs Problem Statement Part 1 Part 2 + s o RE Aramak iin buraya yazin al Chapter 4 Assignment (1) - Excel 0 Dosya Giris Ede Sayfa Dzeni Formuller Veri Gzden Geir Grnm Yardim 2- Metni Kaydet istege Uyarlanm X X Ke La Kopyala - Biim Boyacisi Liberation Sans 11 11 A A == KTA a A E Yaptr Birlestir ve Ortala - % 83 Birlik Ayract P Kosullu Tablo Olarak Virgul Yi Biimlendirme Biimlendit Stiller Pano 19 Yaz Tipi Hitzalama Sayi H12 1 X 1 K A B C D E F G H H 31 32 33 Required 34 Follow the step-by-step 'what-if directions to determine the cost allocations for Current Designs. 35 36 37 Estimated Expected Overhead Use of Cost Activity-Based Overhead 38 Activity Cost Pools Cost Drivers Rates 39 Design 40 Prototypes 41 Molds 42 Supervision 43 Curing time 44 45 Composite Rotomolded Expected Expected Use of Cost Overhead Cost Use of Cost Overhead Cost 46 Activity Cost Pools Drivers Rates Assigned Drivers Rates Assigned 47 Design 48 Prototypes 49 Molds 50 Supervision 51 Curing time 52 Total amount allocated 53 Cost directly assigned 54 Total cost 55 Number of kayaks 56 Cost assigned to each kayak 57 58 What-if? 59 Part 2 Composite Rotomolded Increase in prototypes for composite kayaks 80 Problem Statement Part Part 2 S Aramak iin buraya yazin Otomatik Kaydet Chapter 4 Assignment (1) - Excel j AA Dosya Giris Ekle Sayfa Dzeni Formulier Veri Gzden Geir Grnm Yardm Liberation Sans Metni Kayder istege Uyarlanmis X kes [ Kopyala- Yapists 3 Biim Boyac Pano 11-AA a. A K TA- E Birletir ve Ortala -% ! CHE Binlik Ayrac. ParaBirin Koullu Tablo Olarak Virgul Yuzde Biimlendirme Biimlendir Stilter Yaz Tipi FS Hietalama 5 Says H12 X B D E F G H 1 K L M 49 Molds 50 Supervision 51 Curing time 52 Total amount allocated 53 Cost directly assigned 54 Total cost 55 Number of kayaks 56 Cost assigned to each kayak 57 58 What if? 59 Part 2a Composite Rotomolded Increase in prototypes for composite kayaks 60 61 Composite Rotomolded 114 000 38.000 No change in number of prototypes 62 63 64 65 Part 2b 66 Cost allocation 67 Original cost allocation for prototypes 88 69 Percentage change 70 71 72 73 74 75 76 77 78 79 80 Problem Statement Part 1 Part 2 Aramak iin buraya yazin