Overview:

You are working as a business consultant, helping companies to better manage their costs. One of your clients is Supremo Burritos, a chain of 100 quick service burrito stores across the state of Pennsylvania. Supremo Burritos has approached you because they would like to better understand their operating costs aside from their costs for food, labor and rent. You have been given a dataset which includes several pieces of data the client has collected over the past two years. Your goal is to use data analysis to help Supremo Burritos management generate a cost equation that will be useful for predicting future other operating costs for your client using the best available cost driver or drivers.

Getting started:

Download the Excel file from Class that contains the dataset you will need. The dataset includes 24 months of data for Supremo Burritos Other Operating Costs as well as 6 possible drivers. The dataset also includes additional data on the underlying items that make up the total Other Operating Costs.

To complete the project you will need to follow the all the steps in parts 1, 2, 3 and 4 and write a memo as described in part 5.

Before starting parts 1 through 4, I would recommend setting up a separate tab in your excel spreadsheet for each possible cost driver and including all of the required items in parts 1-4 that relate to that specific cost driver.

For Parts 1-3, Treat Other Operating Costs as a single cost pool

Part 1: Estimate the basic cost equation for Other Operating Costs using the High-low method separately for each possible cost driver

Use the High-low method to determine the cost equation that expresses how monthly Other Operating Costs behave in relation to that particular cost driver. Clearly show the two months you used to generate the High-low equation. Be sure to clearly state and label the resulting high-low equation for each possible cost driver.

Part 2: Using the chart feature in Excel prepare a scatter plot of Other Operating Costs and each possible cost driver.

Add the simple regression line to the graph. Format, resize and label the graph appropriately.

Use the graph to visually check for possible outliers. Identify any data points you think are outliers or add a comment that none appear to exist. Even if you see potential outliers, continue to use the full data set in the following analysis.

Part 3: Use the Data Analysis tool in Excel to estimate the cost equation for Other Operating Costs for each of the possible cost drivers using simple regression (run a simple regression analysis separately for each possible cost driver). You may need to add the Analysis ToolPak in Excel to see the data analysis tool. To add the Data Analysis ToolPak go to Add-ins under Excel Options and select Analysis ToolPak.

Use the resulting regression output to estimate the general cost equation for Other Operating Costs. Be sure to clearly state and label the cost equation generated using simple regression for each cost driver.

For Part 4 examine possible cost drivers for Other Operating Costs using two separate cost pools

Use simple regression to separately estimate the general cost equation for Labor Overhead and Other Overhead for each of the 6 possible cost drivers. Be sure to clearly state and label the cost equation for each of the two overhead cost pools generated using simple regression for each possible cost driver.

Part 5: Draft a memo to your client summarizing your findings and advising them on how best to predict and estimate Other Operating Costs. This should include your best judgment of whether or not they should keep Other Operating Costs as 1 cost pool or split it into the 2 cost pools they are considering and your judgment of the best cost driver or cost drivers to use given your recommended approach. (If you recommend 1 cost pool you should be recommending 1 best cost driver for that pool and if you recommend they use the 2 cost pools you should be recommending the best cost driver for each of the 2 cost pools.) Your recommendation of cost driver(s) should include an explanation of why you believe this (these) cost driver(s) will be best for estimating Other Operating Costs.