AND COST MANAGEMENT ACCOUNTING 2
SPREADSHEET ASSIGNMENT #1: Variable vs. Absorption Costing
Given below are the sales, production, and cost data for Burton Co. for the years ended December 31,
2002through 2005
Sales in units
Production in units
Beginning inventory in units
Direct material cost per unit
Direct Labour cost per unit
Variable overhead cost per unit
Fixed Overhead
Variable selling and
administrative exp. per unit
Fixed selling and admin.
Selling price per unit
Denominator level in units
2002
12,000
17,000
0
$5
$6
$3
$90,000
$4
2003
16,000
18,000
5,000
$6
$7
$3
$105,000
$5
2004
18,000
12,000
7,000
$7
$9
$4
$120,000
$6
2005
14,000
13,000
1,000
$7
$10
$5
$120,000
$6
$50,000
$40
15,000
$60,000
$45
15,000
$80,000
$50
15,000
$90,000
$52
15,000
Other Information
Assume FIFO for inventory flows.
Assume that the unit costs for the beginning inventory in 2001 i.e, the production cost relating to 2002) were the
same as the production costs in 2003 (This would not be the normal situation; production costs in 2003would
normally be different from 2002 we will assume they were the same for simplicity)
This will require that you include in your input, appropriate cost data for 2001for 5 items: per unit material,
labour, variable overhead costs plus lump sum fixed overhead and the denominator level) .
Required:
1.
From an input sheet based on the above data, prepare a detailed income statement for the four years on a
single chart, for the each of the variable costing method and the absorption costing method two
sheets, (i.e., one for each method with all four years (columns) on one schedule). Include a reconciliation of the
two methods on one of the sheets.
2.
Include four macro buttons in the Input Section that do the following: Print Preview the Input sheet, Print
Preview the Variable Costing Income Statement only, Print Preview the Absorption costing income
statement only, Move the data one column to the left (dropping the oldest year in the process) so that a
blank column is freed up to enter the next years data. Before you press the stop record button, you
should return to the Input Sheet where you buttons are located. It is not necessary to include the year
headings in the Macro. The year headings should be updated outside the macro by manually entering a
single new year-end date (mm/dd/yyyy) in the input, referencing it to the year headings, and formatting the
year heading cells using custom format yyyy. You should only need to enter one date in this
assignment . You will need to demo you macros in the lab.
The best way to move the data is to copy the four years one column to the left using Paste Special > Values ;
then, clear the data in the column in which the new years data will be entered. Date headings for the year should
change as well (either as part of the macro or as part of the date input). When you prepare this macro , after you
press the stop record button, hit the undo button to return the data to the original configuration . Then Save the
file BEFORE you run the macro.
RF 01/06