Activity$-$Based Costing Using Excel $($Predictive Analytics$)$

Kirkland Industries $($a contract assembly manufacturer$)$ has decided to adopt activity$-$based costing techniques to determine its manufacturing overhead rates. The production manager has identified three activities $($materials movement, assembly, and packaging$/$shipping$)$ and a number of possible activity measures $($# of jobs, direct labor hours, machine hours, # of boxes shipped, and # of components used$).$ Working together, the production and accounting managers have used historical data from $2014$ to $2021$ to determine total activity costs by month. Those results are included in the a file linked here. The workbook also includes totals for the various activity measures from the same $2014-2021$ period. $($A video demonstrating Excel tools used to answer the questions in this problem is available on the website.$)$

Budgeted overhead dollars and activities for the year are:

Budgeted Overhead Budgeted Measures

Materials movement $$1,080,000$ # of jobs $480$

Assembly $$1,950,000$ Direct labor hours $16,000$

Packaging$/$Shipping $$1,584,000$ Machine hours $7,800$

# of boxes shipped $48,000$

# of components used $4,000,000$

a$.$ Use the correlation tool in Excel to determine which measure should be used for each activity. Hint: The correlation tool can be found on the Data Analysis menu in the Analyze section of the Data tab in Excel. If the Analyze section does not appear, you will need to load the Analysis ToolPak. Click the File tab, click Options, and click Add$-$Ins. Make sure Excel Add$-$ins appears in the Manage field. Check the Analysis ToolPak option and click OK$.$

Hint: Open the Correlation tool and highlight the data. If the first row is highlighted, check the box for Labels in the first row. From your results, determine which variable has the highest correlation value for each of the three overhead categories.

Note: Round the correlation value to four decimal places.

Activity Measure Correlation Value

Materials movement Answer

Answer

Assembly Answer

Answer

Packaging and shipping Answer

Answer

b$.$ Using the measures identified in a$,$ determine the activity rates for allocating manufacturing overhead to jobs.

Activity Activity rate

Materials movement Answer

Assembly Answer

Packaging and shipping Answer

c$.$ What would the predetermined rate be if direct labor hours were used to allocate all manufacturing overhead costs?

Note: Round answer to two decimal places.

$Answer

d$.$ Assume Kirkland had a job that required $36$ direct labor hours, $16$ machine hours, $1,875$ components, and $68$ boxes.

How much manufacturing overhead would be applied to that job under ABC? $Answer

How does that compare to the amount applied if direct labor hours were used to allocate overhead?

Note: Round answer to the nearest dollar.

$Answer