AutoSave Of H e f Concrete Helmet Case Workbook for students-1 (1) - Excel Mireya Diaz MD - x Comments File Home Insert Draw Page Layout Formulas Data Review View Share A Conditional Formatting Insert X C - Arial B 11 = 3 Help Search General $ - % = 3 = 3 albe 3 9 Format as Table 2 Delete Editing Ideas Paste 1 - U - A - Cell Styles Format Clipboard Font Alignment Number Styles Cells Ideas A18 Wages Factory Direct labor F G H 3 Item 4 Administartive salaries 5 Advertising for helmets 6 Depreciation on factory building 7 Depreciation on office equipment 8 Insurance on factory building 9 Miscellanious expenses -factory 10 office supplies 11 Professional fees 12 Property taxes on factory building 13 Raw material used in production 14 Rent on production equipment 15 Research and development 16 Sales commission 17 utility cost-factory 18 Wages Factory Direct labor Totals Direct Materials Direct Labor Manufacturing Overhead Period Costs $ 10,500 $ 11.000 1,500 800 1.200 500 300 800 400 | $ 60,000 5,000 10,000 $ 32,000 900 $ 87,500 $ 60,000 $ 87,500 $ 9,500 $ 65,400 - ... Concrete HelmetCase Instructions part 1 - 5pt part 2 - 5pt part 3 - 6pt parts 4-6 - 6pt parts 7-9 - 10pt part 10 (a-d) - 6pt par ... + part 10 (e) - 3pt | Ready - 11 + 6:37 PM 5/11/2020 100% E 10 o ra 2 AutoSave Of H e f Concrete Helmet Case Workbook for students-1 (1) - Excel Mireya Diaz MD - x Comments File Home Insert Draw Page Layout Formulas Data Review View Share A Conditional Formatting Insert X C - Arial B - 12 = 3 Help Search General $ - % = 3 = 3 ab 3 1 9 Format as Table 2 Delete- Editing Paste Ideas U - A - Cell Styles Format Clipboard Font Alignment Number Styles Cells Ideas G13 : fic E F G H I J K L M N A Variable Costs 300 $ 3 Item 4 Administartive salaries 5 Advertising for helmets 6 Depreciation on factory building 7 Depreciation on office equipment 8 Insurance on factory building 9 Miscellanious expenses -factory 10 office supplies 11 Professional fees 12 Property taxes on factory building 13 Raw material used in production 14 Rent on production equipment 15 Research and development 16 Sales commission 17 utility cost-factory 18 Wages Factory Direct labor Totals Fixed Costs Total Costs $ 10,500 $ 10,500 $ 11,000 $ 11,000 $ 1,500 $ 1,500 800$ 800 1,200 $ 1,200 500 $ 500 300 800 $ 400 $ 60,000 5,000 $ 5,000 $ 10,000 $ 10,000 32,000 900 $ 900 87,500 $ 42,900 $ 222,400 800 400 $ 60,000 $ 32,000 $ $ 87,500 179,500 ... Concrete Helmetcase Instructions part 1 - 5pt part 2 - 5pt part 3 - 6pt parts 4-6 -6pt parts 7-9 - 10pt part 10 (a-d) - 6pt part 10 (e) - 3pt par ... + ] Ready -- + 6:37 PM 5/11/2020 100% E Bio @ o 1) 2 AutoSave Of H e f Concrete Helmet Case Workbook for students-1 (1) - Excel Mireya Diaz MD - x Comments File Home Insert Draw Page Layout Formulas Data Review View Help Search Share A X Arial - 12 = = = a b Custom Conditional Formatting Insert - - Format as Table 2X Delete Editing Ideas Paste B. Cell Styles Format A Font Clipboard Alignment Number Number Styles Cells Ideas H28 x fx F G H I J K L M N O PA C D E Febuary 1,2020 B 6 Work in process inventory, 7 Direct materials: Raw materials inventory, February 1,2020 Raw materials purchase Total Cost of Materials Available for Use ending raw material Direct Materials Used $ $ 72,000 72,000 (12,000) $ 60,000 14 + Direct labor: $ 87,500 + Manufacturing overhead: Depreciation on factory building Insurance on factory building Miscellaenous expenses-factory Property taxes on factory building Rent on production equipment Utility cost-factory Total manufacturing overhead 24 Total manufacturing costs incurred during the period 25 Total manufacturing costs in Work-in-process ... Concrete Helmetcase Instructions part 1 - 5pt part 2 - 5pt A A A A A A 1,500 1,200 500 400 5,000 900 $ 9,500 $ 157,000 $ 157,000 parts 4-6 -6pt parts 7-9 - 10pt part 10 (a-d) - 6pt part 3 - 6pt part 10 (e) - 3pt par ... ] 1 -- Ready + 6:37 PM 5/11/2020 100% E Bio @ o 1) 2 AutoSave Of H e f Concrete Helmet Case Workbook for students-1 (1) - Excel Mireya Diaz MD - x Comments File Home Insert Draw Page Layout Formulas Data Review View Share A X -14 = = A Conditional Formatting Help Search General $ - % Insert Arial B - = 5 ab 3 - I 9 Format as Table Editing Paste Ideas V - 2X Delete- Format A - Cell Styles Clipboard Font Alignment Number Styles Cells Ideas A12 X for under variable costing system can be used for different purpose, because it is used for various managerial decision making purpose. Helps management in cost volume profit A 1 Part 4 - Determine the cost of producing a helmet 2 Cost of goods manufactured/total units manufactured 157000/10000= $15.70 6 Part 5 - Identify the type of cost accounting system that Concrete Helmet Company is probably using at this time. Explain. 7 Concrete Helmet Company is using costing system, because this is based on total manufacturing. Which is 8 variable + manufacturing cost. And product cost consists of variable cost and fixed cost. 9 11 Part 6 - Under what circumstances migt Concrete use a different cost accounting system? Explain. under variable costing system can be used for different purpose, because it is used for various managerial decision making purpose. Helps management in cost volume profit analysisand break even analysis. Also helps in flexible budget preparations and profitability at variouslevels of output, and for evaluations of new product launches andpricing decisions. 13 16 ... Concrete Helmetcase Instructions part 1 - 5pt part 2 - 5pt part 3 - 6pt parts 4-6 -6pt parts 7-9 - 10pt part 10 (a-d) - 6pt part 10 (e) - 3pt par ... @ + J Ready -- 1) 2 - + 6:38 PM 5/11/2020 100% E @ o AutoSave Of H e f Concrete Helmet Case Workbook for students-1 (1) - Excel Mireya Diaz MD - x Comments File Home Insert Draw Page Layout Formulas Data Review View Share Help Search Number A X Arial - 12 = = = ale Conditional Formatting Insert - Format as Table CE 2X Delete Editing Ideas Paste - A - Cell Styles Format Clipboard Font Alignment Number Styles Cells Ideas D25 : ~ fic * B 2019 C A D E F G H I J K L M N O P Q R A 22 break even point in units fixed cost unit contribution margin units $42,900 $21.25 2,019] ... Concrete HelmetCase Instructions part 1 - 5pt part 2 - 5pt part 3 - 6pt parts 4-6 -6pt parts 7-9 - 10pt part 10 (a-d) - 6pt part 10 (e) - 3pt - Ready par ... + ] -- + 6:38 PM 5/11/2020 100% E @ o 1) 2 AutoSave Of H e f Concrete Helmet Case Workbook for students-1 (1) - Excel Mireya Diaz MD - x Comments File Home Insert Draw Page Layout Formulas Data Review View Share A Conditional Formatting Insert X C - Arial B 3. 11 = 3 Help Search General $ - % = 3 = 3 ab 3 1 9 Format as Table 2 Delete- Editing Ideas Paste U - A Font Cell Styles Format Clipboard Alignment E Number Styles Cells Ideas E13 4. A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC A 7 The Business Situation 8 Concrete Helmet Company is a newly formed company that manufactures bicycle helmets. The company began operations January 1, 2020. Its accountant quit 9 the second week of operations, and the company is searching for a replacement. The company has decided to test the knowledge and ability of all candidates interviewing for the position. 10 Each candidate will be provided with the information below and then asked to prepare a series of reports, schedules, and budgets based on the information. 11 The information provided to each candidate is as follows: 12 Cost Items and Account Balances 13 Administrative salaries.................. ............ S 10,500.00 14 Advertising for helmets... $ 11,000.00 15 Cash, January 1.... 16 Depreciation on factory building....... $ 1,500.00 17 Depreciation on office equipment.......... $ 800.00 18 Insurance on factory building. .......... $ 1,200.00 19 Miscellaneous expenses - factory............. $ 500.00 20 Office supplies expense....... $ 300.00 21 Professional fees. $ 800.00 22 Property taxes on factory building $ 400.00 23 Raw Materials used in production.. $ 60,000.00 24 Rent on production equipment.............. $ 5,000.00 25 Research & Development.. $ 10,000.00 26 Sales commissions (10% of sales).............. $ 32,000.00 27 Utility costs - factory.... $ 900.00 28 Wages - Factory direct labor..... $ 87,500.00 29 Work in process, January 1. 30 Work in process, January 31... 31 Raw materials inventory, January 1...... 32 Raw materials inventory, January 31............$ 12,000.00 33 Raw materials purchases. $ 72,000.00 34 Finished goods inventory, January 1............ S NAME Concrete HelmetCase Instructions part 1 - 5pt part 2 - 5pt part 3 - 6pt parts 4-6 -6pt parts 7-9 - 10pt part 10 (a-d) - 6pt Ready part 10 (e)-5... + | 0 -- H + 4:32 PM o ra ) 25 /11/2020 70% E AutoSave Of H e f Concrete Helmet Case Workbook for students-1 (1) - Excel Mireya Diaz MD - x Comments File Home Insert Draw Page Layout Formulas Data Review View Search Share Help General A X Arial -11 = = = ab Conditional Formatting Insert - Format as Table 2 Delete- Editing Ideas Paste 3. Cell Styles Format A Font Clipboard Alignment Number Styles Cells Ideas E13 x for F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC A 4 A B C D E 36 Production and Sales Data 37 Number of helmets produced 38 Expected sales in units for January 39 Expected sales in units for February 40 Desired ending inventory 41 Direct materials needed per 1 finished unit 42 Direct materials cost 43 Desired direct materials inventory 44 Direct labor hours per unit 45 Direct labor hourly rate 10,000 8,000 $40 Unit sell price 10,000 20% of next month's sales 1 Kilogram $6 per kilogram 20% of next months' production 0.35 hour $25 per hour 47 Cash Flow Data 48 Cash collection from customers: 70% in month of sale and 30% the following month. 49 Cash payments to suppliers: 75% in month of purchase and 25% the following month. 50 Income tax rate: 35% 51 Manufacturing overhead and selling and administrative costs are paid as incurred. 52 Desired ending cash balance: $ 20,000 (financing is necessary if cash balance falls below the $20,000 minimum). 54 Instructions Using the data presented above, complete the following 10 parts using this Excel workbook. 1. Classify the costs as either product costs or period costs using the five-column table in the Excel Workbook for part 1. Enter the dollar amount of each cost in the appropriate column and total each classification 2. Classify the costs as either variable or fixed costs. Assume there are no mixed costs. Enter the dollar amount of each cost in the appropriate column and total each classification. Assume Misc. expense-factory and Utility costs-factory are fixed costs. 3. Prepare a schedule of cost of goods manufactured for the month of January. Don't forget the proper heading. 4. Determine the cost of producing a helmet (production cost per unit). 5. Identify the type of cost accounting system that Concrete Helmet Company is probably using at this time. EXPLAIN. 6. Under what circumstances might Concrete Helmet Company use a different cost accounting system? Explain. 7. Compute the unit variable cost per helmet. 8. Compute the unit contribution margin AND the contribution margin ratio (%). NAME Concrete Helmet Case Instructions part 1 - 5pt part 2 - 5pt part 3 - 6pt parts 4-6 -6pt parts 7-9 - 10pt part 10 (a-d) - 6pt part 10 (e) - ... + Ready 0 -- H + 4:32 PM ra ) 25 /11/2020 70% E o AutoSave Of H e f Concrete Helmet Case Workbook for students-1 (1) - Excel Mireya Diaz MD - x Comments File Home Insert Draw Page Layout Formulas Data Review View Share = = = Conditional Formatting Insert A Parte - X O Help Search General $ - % Arial B -11 ab 3 I Format as Table 2 Delete- Editing Ideas U A - - Cell Styles Format Clipboard Font Alignment Number Styles Cells Ideas 146 4 M N O P Q R S T U V W X Y Z AA AB AC A A B C D E F G H I J K L 6. Under what circumstances might Concrete Helmet Company use a different cost accounting system? Explain. 7. Compute the unit variable cost per helmet. 8. Compute the unit contribution margin AND the contribution margin ratio (%). 9. Calculate the break-even point in sales dollars AND the break-even point in units. 10. Prepare the following budgets for the month of January. a) Sales b) Production c) Direct materials d) Direct labor e) Selling & Administrative expenses f) Cash g) Budgeted income statement PROJECT CHECK FIGURES: - Total costs = $222,400 Total Period costs = $65,400 Cost of Goods Manufactured = $157,000 - Total cash disbursements = $214,100 - Net Income = $83,850 NAME Concrete HelmetCase Instructions part 1 - 5pt part 2 - 5pt part 3 - 6pt parts 4-6 -6pt parts 7-9 - 10pt part 10 (a-d) - 6pt 1 Ready part 10 (e)-5... + | 0 | Co H -- do + 70% 4:32 PM 5/11/20209 AutoSave Of H e f Concrete Helmet Case Workbook for students-1 (1) - Excel Mireya Diaz MD - x Comments File Home Insert Draw Page Layout Formulas Data Review View Share A X = = = ab Conditional Formatting Help Search General $ - % Insert Arial B - - 12 - I - Format as Table 2X Delete Editing Paste Ideas - A - Cell Styles Format Clipboard Font Alignment Number Styles Cells Ideas B3 : * fic A E J K L M F (C) Sales budget Direct Materials Budget Total Sales Total kilograms needed for production Total materials required Armstrong Helmet Company Production Budget For the month ended December 31, 2013 Total cost of direct materials purchases Total Required units _ (d) Direct Labor Budget Required production units Total required direct labor hours NAME Concrete HelmetCase Instructions part 1 - 5pt part 2 - 5pt part 3 - 6pt parts 4-6 -6pt parts 7-9 - 10pt part 10 (a-d) - 6pt Ready part 10 (e)- ... C D 9 -- F + 4:32 PM o ra ) 25 /11/2020 100% E Concrete Helmet Case Workbook for students-1 (1) - Excel Mireya Diaz MD - x Comments Share Formulas - 12 Data = Review = = View ab Help Search General $ - % Conditional Formatting - - - Format as Table Insert E3 Delete Format Editing Paste AutoSave Of H e f File Home Insert Draw Page Layout A X Arial - B I - A Clipboard Font 119 : * v for 1 Part 10 (e) Ideas Cell Styles Alignment Number Styles Cells Ideas co E F G H I J K L M N O P 3(e) Selling & Administrative Budget Variable Expenses: Fixed Expenses: Total Fixed Expenses: Total Selling & Administrative Expenses: 20 ... Concrete HelmetCase Instructions part 1 - 5pt part 2 - 5pt part 3 - 6pt parts 4-6 -6pt parts 7-9 - 10pt part 10 (a-d) - 6pt part 10 (e) - 3pt Ready par ... + 1 0 -- t 4:33 PM ra ) 25 /11/2020 100% E o AutoSave Of H e f Concrete Helmet Case Workbook for students-1 (1) - Excel Mireya Diaz MD - x Comments File Home Insert Draw Page Layout Formulas Data Review View Share A X 12 = = = ab Help Search General $ - % Conditional Formatting Insert Arial B - - I V - Format as Table 2 Delete Editing Ideas Paste - A - Cell Styles Format Font Alignment Number Styles Cells Ideas fx Part 10 (F-g) Clipboard : Part 10 (8-9) c o E F G H J K L M 1 (g) Cash Budget Budgeted Income Statement Beginning cash balance Add: Receipts Gross Profit Total available cash Less: Disbursements Income Tax Expense Net Income Total Disbursements Excess (deficiency) of available cash over disbursements Financing (if any): Ending cash balance ... part 1 - 5pt part 2 - 5pt part 3 - 6pt parts 4-6 -6pt parts 7-9 - 10pt part 10 (a-d) - 6pt part 10 (E) - 3pt part 10 (f-g) - 9pt + Ready + 10 - 4) = @ 5 100% J G + 12020 2 F9
