Actual Overhead Costs:

Indirect salaries $474,000

Cleaning supplies $24,000

Shipping costs $180,000

Rent on warehouse $72,000

Depreciation $360,000

Utilities $60,000

Parts and repairs $18,000

Insurance $12,000

Total $1,200,000

1. Using a brand new excel workbook, copy the Actual Overhead Cost from the Student Disk-C5 Activity Based Costing into a new workbook. (Lose 2 points for failure to copy over to a new workbook.)

a. Name this worksheet "ABC Input" (.5 points)

b. Name each of the costs (each cell of column B). The name should start with the first initial

of your last name and then appropriate name for the costs. For example, the Amount of $474,000 for Dr. Mahoney could be named "mindirectsalaries". These names must appear in formulas to receive credit. (1 point)

c. Starting in cell F2 make table and enter in the percentages of how each actual overhead costs is allocated to each of the four ABC cost activities. Be sure to include a Totals column (using a formula) to ensure all percentages add up to 100% with a formula. (This information is located in the text). (.5 point)

i. Add a conditional format for the Total column to ensure that it equals 100% (1 point)

d. Directly below the table in 1c, type in the budgeted costs drivers for each of the four ABC costs activities. (.5 point)

e.make another directly below 1d, type in the actual cost drivers by candle types of sculpted, imprinted and additive for each pool. (.5 point)

f. In another line below 1e, type in the number of units produced for sculptured, imprinted and additive (190,000, 203,000 and 68,000). (.5 point)

g. Starting in cell A14, add the appropriate labels and direct labor and direct material costs one

of each candle as follows: Sculptured

Imprinted

DM: $4.10 DL: $0.70

DM: $3.10 DL. $0.55

DM: $4.25 DL: $0.81 (.5 point)

Additive

h. Below 1g, add a proper label and the actual overhead of $520,000 for the quarter. (.5 point) i. Format the worksheet in good form, using color etc. (.5 points)

2. On a new spreadsheet named "POHR" determines the POHR (predetermine overhead rate) four each of the four ABC activities (3.5 points).

3. On a new sheet named "Applied Overhead", calculate through the use of formulas the amount of overhead that will be allocated to each specialty candle line four each of the four ABC activities.

a. Calculate the Overhead applied to one candle for all product lines. (3 points)

b. Calculate the total costs of one candle for all product lines. (3 point)

c. Calculate the amount of under or over applied overhead for the quarter? Use an If

statement to determine under or over applied.