978-1111971724 Kinney 9e_Excel_13-30_IE Excel

Exercise 13-30 Name:

Insert your answers in the gray-shaded cells. If an answer is incorrect,

the word “wrong” will appear.

Administration HR Accounting

Administration – 0.10 0.10

Human resources 0.10 – 0.10

Accounting 0.10 0.10 –

Checking 0.30 0.30 0.40

Savings 0.40 0.20 0.20

Loans 0.10 0.30 0.20

Type equation to show how much cost is allocated to each department

(A) Administration =

(B) Human resources =

(C) Accounting =

Admin. HR Acctg.

Direct cost 540,000$ 360,000$ 300,000$

Admin. (627,271) 62,727 62,727

Human resources 46,363 (463,625) 46,363

B = $360,000 + 0.10(540,000 + 0.10B + 0.10C) + 0.10C

C = $300,000 + 0.10($540,000 +0.10B + 0.10C) + 0.10B

B = $360,000 + $54,000 + 0.01B + 0.01C + 0.10C

B = $414,000 + 0.01B + 0.11C

0.99B = $414,000 + 0.11C

C = $300,000 + $54,000 + 0.01B + 0.01C + 0.10B

C = $345,000 + 0.11B + 0.01C

0.99C = $354,000 + .011B

C = $357,578 + 0.1111B

Solution

$540,000 + 0.10B + 0.10C

$360,000 + 0.10A + 0.10C

$300,000 + 0.10A + 0.10B

0.99B = $414,000 + 0.11($357,578 + 0.1111B)

0.99B = $414,000 + $39,333+ 0.0122B

0.9778B = $453,333

B = $409,087

C = $357,578 + 0.1111($463,625)

C = $357,578 + $51,509

C = $409,087

A = $540,000 + 0.10($463,625) + 0.10($409,087)

A = $540,000 + $46,362.50 + $40,908.70

A = $627,271.20

Total costs 1$ 11$ 5$

Note: The Administration, Human Resources, and Accounting columns will not sum to $0

because of rounding. Total costs will also be off due to rounding.

Check. Sav. Loans

630,000$ 337,500$ 675,000$

188,181 250,908 62,727

139,088 92,725 139,088

163,634 81,817 81,817