Question

PLEASE DETAIL ALL FINAL ANSWERS AS EXCEL FORMULAS - EXAMPLE =B14*B16

PLEASE DETAIL ALL FINAL ANSWERS AS EXCEL FORMULAS - EXAMPLE =B14*B16

PLEASE DETAIL ALL FINAL ANSWERS AS EXCEL FORMULAS - EXAMPLE =B14*B16

PLEASE DETAIL ALL FINAL ANSWERS AS EXCEL FORMULAS - EXAMPLE =B14*B16

PLEASE DETAIL ALL FINAL ANSWERS AS EXCEL FORMULAS - EXAMPLE =B14*B16

Chocolates by Jacki has provided information relating to its current year. The Controller has asked you to complete a First-Stage Allocation to Activity Cost Pools. Use the information included in the Excel Simulation and the Excel functions described below to complete the task.

• Cell Reference: Allows you to refer to data from another cell in the worksheet. From the Excel Simulation below, if in a blank cell, “=B7” was entered, the formula would output the result from cell B7, or 400,000 in this example.
• Absolute Reference: Allows you to maintain the original cell reference when a formula is copied to another cell. The cell reference is “locked” by putting a dollar sign ($) before the column and row references. By default a cell reference is relative, so when you copy a formula to another cell the values update based on a relative reference. For example, if you copy the formula “=B8+B9” from cell C1 to cell C2, the formula in cell C2 will relatively update to be “=B9+B10” since the copied formula moved down one cell, the formula cell references also moved down one cell. Conversely, by adding the absolute cell reference “locks” to the equation, the formula will not change when copied to any other cell. For example, “=$B$8+$B$9” written in cell C1 is copied to cell C2, the formula in cell C2 will remain “=$B$8+$B$9”. You can also use a mixed cell reference by only “locking” the column or row only ($B8 or B$8) which locks that specific column or row and the other reference becomes a relative reference and “moves” with the formula as its copied to another cell.   For example, if you copy the formula “=B$8+B9” from cell C1 to cell C2, the formula in cell C2 will update to be “=B$8+B10” since the copied formula moved down one cell, the formula cell relative references also moved down one cell, but the absolute “locked” reference remained the same.
• Basic Math functions: Allows you to use the basic math symbols to perform mathematical functions. You can use the following keys: + (plus sign to add), - (minus sign to subtract), * (asterisk sign to multiply), and / (forward slash to divide). From the Excel Simulation below, if in a blank cell “=B18+B19” was entered, the formula would add the values from those cells and output the result, or 250,000 in this example. If using the other math symbols the result would output an appropriate answer for its function.
• SUM function: Allows you to refer to multiple cells and adds all the values. You can add individual cell references or ranges to utilize this function. From the Excel Simulation below, if in a blank cell “=SUM(B13,B14,B15)” was entered, the formula would output the result of adding those three separate cells, or 375,000 in this example. Similarly, if in a blank cell “=SUM(B13:B15)” was entered, the formula would output the same result of adding those cells, except they are expressed as a range in the formula, and the result would be 375,000 in this example.

A C E F G 1 The following information relates to overhead cost at Chocolates by Jacki for the current year. 2 Total costs and distribution of resource consumption across activity cost pools: 4 Activity Cost Pools 5 Customer Product Customer Orders Development Relations Total Cost Other Total 6 Production Department: Indirect factory wages $ 400,000 50% 30% 15% 5% 100% 55% 30% 0% 100% Factory equipment depreciation 150,000 15% Factory utilities Factory property taxes and insurance 11 0% 120,000 59% 34% 7% 100% 10 0% 75,000 57% 33% 10% 100% 12 General Administrative Department: 13 275,000 10% 40% Administrative wages and salaries 20% 30% 100% 14 Office equipment depreciation 37% 100% 40,000 32% 16% 15% 15 Administrative property taxes and insurance 60,000 0% 0% 0% 100% 100% 16 17 Marketing Department: Marketing wages an 19Selling expenses 18 alaries 150,000 20% 10% 40% 30 100,000 32% 16% 37% 100% 15% 20 21 Use the data to complete a First-Stage Allocation to Activity Cost Pools. 22 When completing the table, copy formulas as appropriate 23 24 Activity Cost Pools 25 C B A 25 Total 26 Production Department 27 28 29 30 31 General Administration Department: 32 33 34 35 36 37 Marketing Department 38 39 40 41 Total 42 43 44 45

Answer 1

Activity Cost Pools 4. Product Developmenustomer Relatio Total Total Cost Customer Orders Other 5 6 Production Department Indirect Factory Wages 50% 30% 15% 5% 100% 7 400000 30 % 0%6 55% 15% 100% 8 Factory Equipment Depreciation 150000 59% 100% 9 Factory Utilities 34% 09% 7% 120000 57% 100% 10 Factory Property Taxes and Insuance 11 75000 33% 06 10% 12 General Adminstration Department; 13 Adminstrative Wages &Salaries 275000 20% 10% 40% 30% 100% 16 % 0 % 15% 14 Office Equipment Depreciation 32% 37% 100% 40000 0% 0% 100% 15 Adminstrative Poperty Taxes & Insurance 60000 100% 16 17 Marketing Department; 18 Marketing Wages & Salaries 20% 10% 40% 30% 100% 150000 16 % 15% 100% 19 Selling Expenses 100000 32% 37% 20 21 22

Activity Cost Pools Product Development 24 Other Total Customer Orders Customer Relations 25 26 Production Department 27 Indirect Factory Wages 200,000 60,000 400,000 120,000 20.000 28 Factory Equipment Depreciation 29 Factory Utilities 82,500 45,000 22,500 150,000 70,800 8,400 40,800 120,000 30 Factory Prperty Taxes and Insuance 31 42,750 7,500 24,750 75,000 32 General Adminstration Department; 33 Adminstrative Wages &Salaries 34 Office Equipment Depreciation 35 Adminstrative Property Taxes & Insurance 55.000 27,500 110.000 82.500 275,000 12,800 6,400 6.000 14,800 40,000 60,000 60,000 36 37 Marketing Department; 38 Marketing Wages & Salaries 30,000 15,000 60,000 45,000 150,000 15,000 37,000 100,000 39 Ex 32,000 16.000 40 41 Total 525,850 295,450 251.000 297,700 1,370,000 42

43 Formulas used; 44 Activity Cost Pools Product Development Customer Relations Customer Orders Other Total 45 46 47 Production Department 48 Indirect Factory Wages 49 Factory Equipment Depreciation B7* C7 B7*D7 B7*E7 B7*F7 B27+C27+D27+E27 B8 F8 B8 D8 B8*E8 B28+C28+D28+E28 B8*C8 B9 F9 50 Factory Utilities B9 C9 B9 E9 B29+C29+D29+E29 B9 D9 B10 F10 51 Factory Prperty Taxes and Insuance B10*C10 B10*D10 B10*E10 B30+C30+D30+E30 52 53 General Adminstration Department; 54 Adminstrative Wages &Salaries B13 E13 B13*C13 B13 D13 B13 F13 B33+C33+D33+E33 55 Office Equipment Depreciation 56 Adminstrative Property Taxes & Insurance B14*C14 B14*D14 B14*E14 B14*F14 B34+C34+D34+E34 B15 E15 B15*C15 B15*D15 B15 F15 B35+C35+D35+E35 57 58 Marketing Department; 59 Marketing Wages & Salaries 60 Selling Expenses B18*C18 B18 D18 B18 E18 B18 F18 B38+C38+D38+E38 B19 E19 B19 F19 B19 C19 B19 D19 B39+C39+D39+E39 61 62 Total SUM(E27:E39) SUM(F27:F39) SUM(B27:B39) SUM(C27:C39) SUM(D27:D39) 63 64