Cost Behavior and Related Linear Functions
Gallant Mfg. Co.
wishes to determine the linear cost formula for a number
of its costs from historical data for
the year ended June 30, 2005.
The cost formulas will then be used for such practical purposes as budgeting.
The controller wants you to do the cost formula calculations using two different methods, the hi-lo method,
and linear regression.
Because the template will be used for a number of different cost items, it should be
so that it can be used for different types of cost and different cost-driver units.
In completing the assignment we will introduce or review several features of Excel:
Sorting, use of, SLOPE,
and INTERCEPT functions, relative and absolute referencing, using a sting for headings; Preparation of
multiple graphs on an XY chart.
The data below is for one of these costs called
and the period covers the last 12 months ended June
The related cost driver for this particular cost is
Use the current date as the report
Cost Data (from Factory Ledger)
Prepare a template that will generate cost formulas and a related chart using the sample data for
Processing Costs for both the High/Low method and the Regression method.
, prepare the input section with the following features:
-a brief description telling the user the purpose of the template, preparation date, prepared by,
instructions to assist the user in entering the input.
-treat as separate input items
the name of the cost,
the cost-driver units.
enter only one date as input
(such as the last day of the month of the first or last observation) and then
generate all the month dates in the input schedule from this initial single date (using the date function)
Continued on back………………………
The output section will consist of two additional sheets and three parts.