Page

of $2$

Zoom

ACCT $3314$ Project $1$

XYZ Manufacturing, Inc. is a mid$-$sized company that specializes in producing high$-$quality

electronic components for various industries, including automotive, consumer electronics, and

aerospace. The company was founded in $2005$ and operates from a modern manufacturing

facility in Ohio. XYZ Manufacturing prides itself on maintaining lean and efficient operations, but

the company$$s management has noticed variability in its overhead costs. They are now

interested in determining which cost driver best explains the fluctuations in their manufacturing

overhead and improving the accuracy of their overhead forecasting.

The company has been tracking monthly data for its total manufacturing overhead costs and

the following three potential cost drivers:

$$ Total Batches: The number of production batches processed during the month.

$$ Total Direct Labor Hours: The total number of labor hours worked on production.

$$ Total Machine Hours: The total number of machine hours used in production.

Management wants to analyze which of these cost drivers correlates most closely with total

manufacturing overhead costs to create a more accurate system for forecasting overhead. They

plan to implement a predetermined overhead rate that will be applied to all jobs based on one of

these cost drivers.

Project Requirements

In this project, students will take the role of cost accountants at XYZ Manufacturing. Their task

will be to analyze historical data, forecast future costs, and apply overhead to production jobs

using a predetermined overhead rate. The following steps outline the key components of the

project.

Part $1$: Data Analysis $($Historical Data from January $2020$ to December $2023)$

You will be provided with monthly data from January $2020$ to December $2023,$ including:

$$ Total Manufacturing Overhead Costs

$$ Total Batches

$$ Total Direct Labor Hours

$$ Total Machine Hours

Step $1$: Correlation Analysis

$$ Analyze the data and calculate the correlation between total manufacturing overhead

costs and each of the three potential cost drivers: total batches, total direct labor hours,

and total machine hours. Use the Correlation function in the Data Analysis Toolpack

in Excel to complete this.

$$ Create a scatterplot for each combination of total overhead cost and cost driver in

Excel and format the scatterplot using Quick Layout #$9.$

$$ Determine which cost driver has the highest correlation with total manufacturing

overhead costs.

Part $2$: Forecasting for $2024$

Based on your analysis, you will now create forecasts for the year $2024.$

Step $2$: Forecast Total Manufacturing Overhead Costs and the Chosen Cost Driver

$$ Using the historical data and the cost driver with the highest correlation, forecast total

overhead costs and the cost driver for each month of the year $2024.$

$$ Use the forecast.ets function in Excel to calculate the forecast. Seasonality should be

set to $12.$

Part $3$: Calculating the Predetermined Overhead Rate

Step $3$: Calculate the Predetermined Overhead Rate for $2024$

$$ Using your forecasted total overhead costs and chosen cost driver for the year $2024,$

calculate the predetermined overhead rate for the company.

$$ Hint: you will need to sum the total overhead costs for each forecasted month for the

year $($and do the same thing with the chosen cost driver$).$

Part $4$: Applying Overhead to Jobs for $2024$

Step $5$: Apply Overhead to Jobs Completed in $2024$

$$ You will be given a set of jobs completed during $2024.$ For each job, you will have

information about the actual usage of each of the potential cost drivers $($e$.$g$.,$ direct labor

hours, batches, or machine hours$)$ for that job.

$$ Using the predetermined overhead rate, calculate the applied overhead for each job.

Grading Criteria