Description

Lab 16 Estimating Cost Behavior

Keywords: Regression, Cost Behavior, Fixed Variable Costs

Lab Insight: In managerial accounting, we learn about cost behavior.  Principally we learn about fixed costs, those costs that don’t change with the number of goods or services produced, and variable costs, those costs that vary with the level of output.  Furthermore, a combination of some level of fixed costs and some level of variable costs are known as mixed costs (or semi-variable costs).

Often times, firms don’t know the exact nature of their cost behavior.  For this reason, they will sometimes do various types of analyses, including regression analyses, to get a better handle on their cost behavior structure.

Required:

1.      Use regression analysis to estimate the fixed and variable costs.

2.       Use regression analysis to understand if costs are different during the busy season.

3.       Use regression analysis to see if the company is better off incorporating the busy season into its cost behavior analysis.

Ask the Question: How can regression analysis be used to understand the cost behavior analysis for AERT?

Master the Data: Advanced Environmental Recycling Technologies (AERT) sells plastic/wood composite decking through its brand name, Choicedek.  See more information on Choicedek at https://www.choicedek.com/ .

AERT sells its product through Lowe’s and sells most of its products in the spring and summer, when consumers install or remodel their decks.

The data set provides some weekly sample data for its costs in Lab 16 Data.xlsx.

 Open Excel File Lab 16 Data.xlsx

We are trying to prepare the data for analysis.  Here is the data dictionary.

Data Dictionary:

Step 1:

To estimate the cost behavior for AERT, we will run basic regression analysis.  To prepare the data, we need to insert a “1” in the cells that correspond to busy season and a “0” in the cells that correspond to the off-busy season.  Since the product sells primarily in the spring and summer, they build up their inventory from the beginning of the year through the first part of June.

To do this, in column “C”, starting with cell C2, we insert a 1 for those weeks from the beginning of the year until (and including) 6/4/2022.  We insert a “0” (zero) for those weeks starting 6/11/2022 until the end of the year.  We will assume we have a huge amount of data, so we will use an excel formula to do this.  Note that “if” statements in excel that use dates as the logical qualifier, must use a DATEVALUE function, like this: =IF(A2<DATEVALUE("6/5/22"),1,0).  This tells excel to look at the date in column A, if the date is less than 6/5/22, put a “1” into column C, otherwise put a 0 in column C.  Please add this formula to C2, =IF(A2<DATEVALUE("6/5/22"),1,0), and copy it down to the bottom of the data in column C.

The first few rows of the spreadsheet should appear as follows:

Part 1:

Perform the Analysis:

Step 2:

Regression analysis is a way of using mathematics and statistics to determine which of several variables has an impact on an outcome and how big that impact is on the outcome.

Regression analysis helps to answer the questions: Which factors matter most in predicting the outcome? Which factors can we ignore?

In this case, we are trying to figure out which cost drivers are associated with the outcome, or total overhead costs.

We can think about this like an algebraic equation where y is the dependent variable and x is the independent variables, where y = f(x).  In other words, we will run an equation where y, or total production costs = f(production) where the dependent variable is total production costs for that production week and the independent variables are the amount of board feet of production.