- Email: [email protected]
A spreadsheet analysis of different costing systems
JourmI Prmted
of Accounrrng Educatron, Vol. 6, pp. 345-353, in the USA. All rights reserved.
1988 Copyright
Teaching and Educational
0748.5751188 13.00+.00 0 1988 Pergamon Press plc
Note
A SPREADSHEET ANALYSIS OF DIFFERENT COSTING SYSTEMS Peter Chalos
UNIVERSITY OF ILLINOIS-CHICAGO Abstrucf: This note describes a project that uses a spreadsheet to illustrate the conceptual similarities and differences between actual, normal, and standard costing systems, under both variable and absorption reporting formats. Budgeting and variance analysis are also included in the illustration. The purpose of the project is to enhance, via a comprehensive numerical example, the student’s understanding of these three costing systems. A spreadsheet formulation enables the student to perform sensitivity analysis, while eliminating the need for repetitive income statement formatting and calculations.
Cost accounting textbooks usually explain actual, normal, and standard cost accounting systems at various points throughout the text. Each of these cost systems in turn is typically illustrated under either a variable or full absorption reporting format. Seldom is a comprehensive illustration given which reconciles the similarities and differences of the three systems. Once all cost systems have been discussed, a comprehensive illustration and review of all systems and reporting formats would be beneficial. This would highlight system and format similarities and differences for the student. Such an illustration is ideally suited to spreadsheet analysis. This note reports on a project designed to provide a comprehensive illustration of budgets and production variances in conjunction with different cost systems and reporting formats. A spreadsheet formulation of the problem is provided which enables the instructor to supplement traditional calculations and account illustrations with a sensitivity analysis of the problem’s parameters. I There are several advantages associated with the spreadsheet analysis of different costing systems. First, the student is introduced to a commonplace technology with which she/he will soon be working. Second, given the breadth and computational complexity of the problem, substantial time savings may be realized. Finally, and perhaps most importantly, the student
tUnless the student is very conversant with actual, normal and standard cost systems, it is suggested that account cost flows, either manual or electronic, be required in addition to the suggested spreadsheet analysis. This reinforces the student’s basic conception of the problem and the issues at hand. Classroom experience to date-with the project also suggests that students with previous spreadsheet experience should be required to provide their own cell formulas. For the inexperienced, this might be unduly burdensome and the instructor may wish to include all cell formulations. 345
P. Chalos
346
is able to perform sensitivity analyses upon project cost and revenue data, which would otherwise not be feasible. This note will outline the project information provided and then sequentially illustrate the cost and revenue input required of the student, the cost of goods sold worksheet, a comparison of actual results to master and flexible budgets, a production variance analysis, and the income statements and reconciliation of net income under all cost systems and reporting formats. Two spreadsheets, one for year one and the other for year two, will be presented. In year one, production will exceed sales, building up inventory. In year two, production will be less than sales, decreasing inventory.
PROJECT
DESCRIPTION
Problem Data The following are standard costs per television for Acme Television Inc. The standard costs remain the same for Years 1 and 2. Acme Co. Standard Cost per Television
Material Labor Variable OH Fixed OH Total Standard
Quantity
Price/Unit
15 lbs. 4 hrs.
$lO/lb. $15/hr.
Cost per Television
Total per Television $150 $ 60 $ 60 $100 $370
Estimated production and sales is 1,500 televisions each year. Estimated sales price is $1,400 a unit each year. Overhead is based on direct labor hours, where OH=$150,000+$15 DLH. Estimated marketing and administrative expenses are $240,000 fixed and $55 a unit variable. Listed below are actuaI results of Operations for Years 1 and 2: Year 2*
Year 1 Production Material Labor Variable OH Fixed OH Variable Marketing & Administration Fixed Marketing & Administration Sales
*Assume
FIFO inventory.
1,600 units 25,000 lbs. x $8 6,200 hrs. ~$16 80,000 140,000 75,000 250,000 1,200 units x $1,250
1,300 units 18,500 lbs. x$11 5,500 hrs. ~$14 85,000 165,000 85,000 250,000 1,700 unitsx$1,550
A Spreadsheet
Analysis
347
Problem Input and Cost of Goods Sold Total revenue and marketing and administrative expenses are entered by the student based upon the given data. Product costs should be calculated as delineated in Table 1 and entered in the template provided by the instructor. Table 2 requires student input for beginning finished goods, in both dollars and units and unit data for cost of goods manufactured and cost of
Table 1. Revenue and costs Year 1 A 6 C D E F G H
I J
Revenue-F Est Revenue-B Est Revenue-Act Material Cost-Act Labor Cost-Act Material Cost-Std Labor Cost-Std Var Mktg & Admin-Act Fix Mktg & Admin-Act Var Mktg & Admin-F Est
$1,680,000 $2,100,000 $1,500,000 $ 200,000 $ 99,200 $ 240,000 $ 96,000 $ 75,000 $ 250,000 $ 66,000
Var Mktg & Admin-B Est Fix Mktg & Admin-Est Var OH-Act Fix OH-Act Var OH-App Fix OH-App Var OH-Std Fix OH-Std FX OH-B
$ 82,500 $240,000 $ 80,000 $140,000 $ 93,000 $155,000 $ 96,000 $160,000 $150,000
Var Mktg & Admin-B Est Fix Mktg & Admin-Est Var OH-Act Fix OH-Act Var OH-App Fix OH-App Var OH-Std Fix OH-Std Fix OH-B
$ 82,500 $240,000 $ 85,000 $165,000 $ 82,500 $137,500 $ 78,000 $130,000 $150,000
Year 2 A 6 C D E F G H
I J
Revenue-F Est Revenue-B Est Revenue-Act Material Cost-Act Labor Cost-Act Material Cost-Std Labor Cost-Std Var Mktg & Admin-Act Fix Mktg & Admin-Act Var Mktg & Admin-F Est
$2,380,000 $2,100,000 $2,635,000 $ 203,500 $ 77,000 $ 195,000 $ 78,000 $ 85,000 $ 250,000 6 93,500
Kev: A ’ Revenue-F Est: Units sold x std price per unit B Revenue-B Est: Units budgeted x std price per unit C Revenue Act: Units sold x actual price per unit D Material-Act: Pounds used x price per pound E Labor-Act: Hours used x price per hour F Material-Std: Std pounds per unit x std price per pound x actual units prod G Labor-std: Std hours per unit x std price per hour x actual units prod H Variable Mktg & Admin-Act: Entered as given I Fixed Mktg & Admin-Act: Entered as given Variable Mktg & Admin-F Est: Units sold budgeted x budgeted cost unit J K Variable Mktg & Admin-B Est: Units sold actual x budgeted cost unit Fixed Mktg & Admin-Est: Entered as given L M Variable OH-Actual: Entered as given N Fixed OH-Actual: Entered as given 0 Variable OH-Applied: Variable OH rate x actual direct labor hours Fixed OH-Applied: Fixed OH rate x actual direct labor hours Variable OH-Std: Variable OH rate x standard direct labor hours : R Fixed OH-Std: Fixed OH rate x standard direct labor hours S Fixed OH-Budgeted: Entered as given
P. Chalos
348
Table 2. COGS worksheet Year Units
Act. Full
Act. Var.
1
Norm. Full
Norm. Var.
Std. Full
Std. Var
Beg. Fin. Goods COG Manufactured Avail. for Sale COGS End. Fin. Goods
O$
O$
O$
0
$
O$
O$
0
1,600 1,600 1,200
$519,200 $519,200 $398,400
$379,200 $379,200 $284,400
$547,200 $547,200 $410,400
$392,200 $392,200 $294,150
$592,000 $592,000 $444,000
$432,000 $432,000 $324,000
400
$129,800
$ 94,800
$136,800
$ 98,050
$148,000
$108,000
Std. Full
Std. Var.
Year 2 Units Beg. Fin. Goods COG Manufactured Avail. for Sale COGS End. Fin. Goods
Act. Full
Act. Var
400
$129,800
$ 94,800
$136,800
$ 98,050
$148,000
$108,000
1,300 1,700 1,700
$530,500 $660,300 $660,300
$365,500 $460,300 $460,300
$500,500 $637,300 $637,300
$363,000 $461,050 $461,050
$481,000 $629,000 $629,000
$351,000 $459,000 $459,000
O$
O$
O$
Norm. Full
0
Norm. Var.
$
O$
O$
goods sold. (Because it is assumed that Year 1 is the first year Acme Inc. is in business, there is no beginning finished goods.) For the cost of goods manufactured, students are required to input the units produced. This number is given as part of the actual results of operations data. Formulas are used for the cost of goods manufactured cell in each costing method. Actual full-absorption COGM is the sum of all actual manufacturing costs for the period, provided as student input in Table 1. This includes material, labor, and both variable and fixed overhead. (Year 1 COGM= 200,000+99,200+80,000+ 140,000.) Actual variable COGM is the sum of all actual manufacturing costs with the exception of fixed overhead, which is not inventoried, but expensed as a period cost. (Year 1 COGM =200,000+ 99,200+80,000.) Normal full-absorption COGM is the sum of actual material and labor, and applied fixed and variable overhead. The amount applied is dependent on the predetermined overhead rates. (Year 1 COGM=200,000+99,200+ 93,000+ 155,000.) The difference between actual and normal overhead is the amount under or overapplied. Normal variable COGM is the same as the above except that the fixed overhead applied is excluded. Actual fixed overhead is expensed as a period cost. (Year 1 COGM =200,000+99,200+ 93,000.) Standard full COGM is the sum of all manufacturing costs at standard. (Year 1 COGM=240,000+96,000+96,000+ 160,000.) Standard variable
0
A Spreadsheet Analysis
349
COGM is the same as the above with the exception of fixed overhead. Actual fixed overhead is expensed as a period cost. (Year 1 COGM =240,000+ 96,000+96,000.) Summarizing, the cost of goods manufactured under the three costing systems would comprise the following: Actual Raw Material Direct Labor Variable Overhead Fixed Overhead (Full Absorption
Normal
Standard
Actual Actual Standard Actual Actual Standard Actual Normal Standard ___-----___---______----___-----____--Actual
Normal
Standard
only)
Because a FIFO inventory flow is assumed, COGS under all methods is the sum of the value of beginning finished goods and the value of the proportion of goods manufactured during the period needed to meet sales. (For example, under actual full costing for Year 1, COGS=[OBeg.Inv,+ 12001 1600 (519,200)J.) To meet sales in Year 2, all beginning inventory and all goods manufactured are needed. (Therefore, Year 2 COGS= 1129,8OOB,p.r,,.+1300/1300 (530,500)].) The same reasoning is used to calculate COGS under each costing method.
Actual to Budget Comparisons
and Production
Variances
Table 3 highlights actual results relative to master and flexible budgets. All of the data necessary for budgetary analysis is included in Tables 1 and 2. Data entered as student input in Table 1, which are forwarded to the new budgetary cell addresses, include actual, flexible and master budget revenues; actual, flexible and master budget variable marketing and administration, actual and budgeted fixed overhead, and actual budgeted marketing and administration. The remaining input required, variable cost of goods sold, is forwarded from Table 2. The flexible variable cost of goods sold is forwarded from the standard variable cost of goods sold of Table 2, and can also be used for the master budget variable cost of goods sold. (Year 1 = 151 12(324,000).) The remaining cells are arithmetically derived from the input provided. These include contribution margin, profit and all volume, manufacturing, marketing and administrative and revenue variances. A useful breakdown of individual unprorated production variances is provided in Table 4. Student input of problem data is required for actual unit price, standard unit price, estimated production hour quantity, actual quantity of inputs for actual production and standard quantity of inputs for actual production. Actual raw material and direct labor unit prices are given. Actual unit variable and fixed overhead prices for Year 1 are derived as $80,000/6,200 and $140,000/6,200, respectively. Standard price per unit, estimated hours of production and actual quantities used are given. Year 1
350
P. Chalos
Table 3. Budget to Actual Year 1
Actual (1200 Units) Revenue \/ar.COGS Var.Mktg.and Adm.
1,500,000 284,400
Contrib. Margin FIX. OH Fix. Mktg. & Adm.
1,140,600 140,000 250,000
Profit
Manufacturing, M&A and Revenue Variances (180,000) 39,600 (9,000)
75,000
10,000 (10,000)
750,600
Flexible Budget (1200 Units)
Volume Variances
1,680,000 324,000
(420,000) 81,000
66,000
16,500
Master Budget (1500 Units) 2,100,000 405,000 82,500
1,290,000 150,000 240,000
1,612,500 150,000 240,000
900,000
1.222,500
Budget to Actual Year 2
Actual (1700 Units) Revenue Var. COGS Var. Mktg. and Adm.
2,635.OOO 460,300
Contrib. Margin Fix. OH Fix. Mktg. & Adm.
2,069,700 l65,OOO 250,000
Profit
1,674,700
Manufacturing, M&A and Revenue Variances
85,000
255,000 (1,300) 8,500 (15,000) (10,000)
Flexible Budget (1700 Units) 2,380.OOO 459,000 93,500
Volume Variances 280,000 (54,000) (tl,ooo)
Master Budget (1500 Units) 2,100,000 405,000 82,500
1,827,500 150,000 240,000
1,612,500 150,000 240,000
1,437,500
1,222,500
standard quantities for raw material and direct labor are 15 x 1,600 and 4 x 1,600 respectively. Cell formulas are used for the variance calculations. Income Statement Reconciliation
The income statements are preprogrammed to be derived from the cost of goods sold data in Table 2 and cell information carried forward from Table 1. The appropriate cost of goods sold figure from Table 2 is carried forward to the relevant income statement, as are marketing and administrative expenses from Table 1. As shown in Table 5, the marketing and administration figure is combined under full absorption, while under variable costing it is segmented. In addition, under variable costing, a fixed overhead line item is added, based on data from Table 1. Under normal costing, a line for over/underapplied overhead is needed. Based on Table 1 data, under full absorption Year 1 this represents the difference between actual and applied overhead, prorated by the goods sold (1,200/1,600 [80,000+~40,000-93,~0-155,0~]). In Year 2, the remaining balance is written off as is all of Year 2 over/under applied overhead ([1,2001 1,600[80,000+140,000-93,000-155,000]-1,300/1,300[85,000+165,00082,000- 137,500]). An alternative treatment would be to simply write off the entire variance in the year of occurrence. Total overhead is used for full
A Spreadsheet
Analysis
351
absorption costing, while variable overhead is used for variable costing. If overhead is underapplied, it is considered an additional cost of goods sold expense and subtracted from revenue. If overapplied, it is added to revenue. Finally, if standard costing is used, variances need to be written off. These represent the proportion of the difference between actual and standard production costs that are in the cost of goods sold. This number is obtained from Table 2 data by subtracting standard from actual cost of goods sold. For example, in Table 2, Year 1, the difference between actual and standard full cost of goods sold would be $54,600. This represents the pro rata share of the units sold to the total units produced and their related production variances ([1,200/1,600[200,000+99,200+80,000+ 140,000] -[240,000+ 96,000+96,000+ 160,000]). Similarly in Year 2, the unfavorable difference of $3 1,300 between actual and standard full cost of goods sold is explained by the 400 units of Year 1 inventory plus all units manufactured and sold in Year 2 ([400/l ,600[72,8001,300/l ,300[49,500]). Under variable costing, the Year 1 difference represents the pro rata share of variable production variances written off in the period as a function of units sold ([1,200/l ,600($52,800) = $39,600]). Year 2 variances represent the reduction in inventory from Year 1 plus Year 2’s unfavorable variable production variances ([400/1,600($52,800)-$14,500=$1,300]). Finally, full absorption and variable income differences are due to the fixed overhead included in ending inventory under full absorption that is expensed as a period charge under variable costing. Under all three cost systems, profit differences are $35,000. Since inventory is increased in Year 1, part of the actual fixed overhead is included in ending inventory under
Table 4.
AP Raw Mat. Dir. Lab. Var. OH Fixed OH
8 16 12.9032 22.5806
SP 10 15 15 25
EQ
Production Variances Year 1 PV AQ SQ 25,000 6,200 6,200 6,200
6,000
24,000 6,400 6,400 6,400
50,000 (6,200) 13,000 10,000
vv
QV (10,000) 3,000 3,000
10,000
Total
Raw Mat.
11 14 15.4545 30
SP 10 15 15 25
EQ
Production Variances Year 2 PV AQ SQ
6,000
18,500 5,500 5,500 5,500
19,500 5,200 5,200 5,200
Total Key: AP SP EQ AQ
40,000 (3,200) 16,000 20,000 72,800
AP
Dir. Lab. Var. OH FixedOH
Total
(18,500) 5,500 (2,500) (15,000)
vv
(20,000)
QV
Total
10,000 (4,500) (4,500)
(8,500) 1,000 (7,000) (35,000) (49,500)
Actual price Standard price Estimated quantity Actual quantity
SO PV vv QV
Standard quantity Price variance Volume variance Quantity variance
P. Chalos
352
Table 5. Income Statements Year 1 Actual-Full
Absorption
Normal-Full
Absorption
Standard-Full
Absorption
Revenue COGS Mktg. & Admin.
$1,500,000 $ 389,400 $ 325,000
Revenue COGS U/O Applied OH Mktg. & Adm.
$
325,000
Mktg. & Adm.
$
325,000
Profit
$
Profit
$
785,600
Profit
$
785,600
785,600
$1,500,000 $ 410,400 (21,000)
Revenue COGS Variances
Normal-Variable
Actual-Variable $1,500,000 $ 284,400
Revenue Var. COGS Var. Mktg. & Adm.
$
75,000
Revenue Var. COGS Var. Mktg. & Adm.
Contrib. Margin Fix. OH Fix. Mktg. & Adm.
$1,140,600 $ 140,000
Profit
Standard-Variable
$1,500,000 $ 294,150 $
U/O Applied OH
$
250,000
Contrib. Margin Fix. OH Fix. Mktg. & Adm.
$
750,600
Profit
$1,500,000 $ ‘444,000 (54,600)
75,000 ($9,750)
$1,140,600 $ 140,000 $ $
Revenue Var. COGS Var. Mktg. & & Adm.
$1,500,000 $ 324,000 $
Variances
75,000 ($39,600)
$1,140,600 $ 140,000
250,000
Contrib. Margin Fix. OH Fix. Mktg. & Adm.
$
250,000
750,600
Profit
$
750,600
Year 2 Actual-Full
Absorption
Normal-Full
Absorption
Revenue COGS Mktg. &Admin.
$2,635,000 $ 660,300 $ 335,000
Revenue COGS U/O Applied OH Mktg. & Adm.
$
Profit
$1,639,700
Profit
$1,639,700
Contrib. Margin Fix. OH Fix. Mktg. & Adm. Profit
335,000
$2,635,000 $ 460,300
$2,635,000 $ 629,000 $ 31,300
Mktg. & Adm.
$
Profit
$1,639,700
$
85,000
$2,089,700 $ 165,000 $
250,000
$1,674,700
Standard-
Revenue Var. COGS Var. Mktg. & Adm.
$2,635,000 $ 461,050
U/O Applied OH
$
Contrib. Margin Fix. OH Fix. Mktg. & Adm.
$2,089,700 $ 165,000 $
Profit
$1,674,700
$
85,000 (750)
250,000
Absorption
Revenue COGS Variances
Normal-Variable
Actual-Variable Revenue Var. COGS Var. Mktg. & Adm.
$2,635,000 $ 637,300 $ 23,000
Standard-Full
335,000
-Variable
Revenue Var. COGS Var. Mktg. & & Adm.
$2,635,000 $ 459,000 $
85,000
Variances
$
1,300
Contrib. Margin Fix. OH Fix. Mktg. & Adm.
$2,089,700 $ 165,000 $
Profit
$1,674,700
250,000
A Spreadsheet
Analysis
353
full absorption ([400/1,600($140,000)=$35,000]). As inventory is decreased in Year 2, part of Year l’s fixed overhead is included in Year 2’s cost of goods sold under full absorption costing. Since the increase in Year 1 is the same as the decrease in Year 2, the opposite effect occurs.
SUMMARY This comprehensive illustration of budgets and variances in conjunction with different cost systems has been found to be very effective in the classroom as an end of course review assignment. Students particularly appreciate the synthesis of cost reporting systems and formats provided by such an assignment. The computational power provided by the spreadsheet enables the student to focus on conceptual inputs. Without it, the problem would be extremely time consuming, requiring repetitive manual input of accounts and arithmetic computations on the part of each student. This is eliminated via a one time instructor input on a spreadsheet template which can be made available to students in a PC laboratory, A major caveat in using the spreadsheet is that the student’s thinking not be replaced by the instructor?. Thus, it is suggested that the student be required to provide journal entries for a subset of the data, using the various cost and inventory accounts represented in the different cost systems. (This is particularly true if the student has not provided any spreadsheet formulas). This exercise reinforces the student’s understanding of the concepts and complements the computational advantages of the spreadsheet. The instructor may extend the problem further by incorporating, for example, cost estimation techniques to justify the overhead allocation basis chosen or include beginning and ending balances for work in process equivalent unit valuation. Students could also be required to perform sensitivity analysis on several of the key parameters such as revenue estimates that can be changed in order to examine the impact upon volume variances associated with the master budget, or production estimates that can be changed to determine the impact upon production volume variances in standard costing and under/over applied overhead in normal costing. Similarly, the price and quantity standards for production inputs can be manipulated. Finally, a different basis of overhead allocation may be used to test the sensitivity of overhead variances to the basis of allocation used. By running the numbers under different assumptions, the student will better appreciate the computational power of the spreadsheet.
of Accounrrng Educatron, Vol. 6, pp. 345-353, in the USA. All rights reserved.
1988 Copyright
Teaching and Educational
0748.5751188 13.00+.00 0 1988 Pergamon Press plc
Note
A SPREADSHEET ANALYSIS OF DIFFERENT COSTING SYSTEMS Peter Chalos
UNIVERSITY OF ILLINOIS-CHICAGO Abstrucf: This note describes a project that uses a spreadsheet to illustrate the conceptual similarities and differences between actual, normal, and standard costing systems, under both variable and absorption reporting formats. Budgeting and variance analysis are also included in the illustration. The purpose of the project is to enhance, via a comprehensive numerical example, the student’s understanding of these three costing systems. A spreadsheet formulation enables the student to perform sensitivity analysis, while eliminating the need for repetitive income statement formatting and calculations.
Cost accounting textbooks usually explain actual, normal, and standard cost accounting systems at various points throughout the text. Each of these cost systems in turn is typically illustrated under either a variable or full absorption reporting format. Seldom is a comprehensive illustration given which reconciles the similarities and differences of the three systems. Once all cost systems have been discussed, a comprehensive illustration and review of all systems and reporting formats would be beneficial. This would highlight system and format similarities and differences for the student. Such an illustration is ideally suited to spreadsheet analysis. This note reports on a project designed to provide a comprehensive illustration of budgets and production variances in conjunction with different cost systems and reporting formats. A spreadsheet formulation of the problem is provided which enables the instructor to supplement traditional calculations and account illustrations with a sensitivity analysis of the problem’s parameters. I There are several advantages associated with the spreadsheet analysis of different costing systems. First, the student is introduced to a commonplace technology with which she/he will soon be working. Second, given the breadth and computational complexity of the problem, substantial time savings may be realized. Finally, and perhaps most importantly, the student
tUnless the student is very conversant with actual, normal and standard cost systems, it is suggested that account cost flows, either manual or electronic, be required in addition to the suggested spreadsheet analysis. This reinforces the student’s basic conception of the problem and the issues at hand. Classroom experience to date-with the project also suggests that students with previous spreadsheet experience should be required to provide their own cell formulas. For the inexperienced, this might be unduly burdensome and the instructor may wish to include all cell formulations. 345
P. Chalos
346
is able to perform sensitivity analyses upon project cost and revenue data, which would otherwise not be feasible. This note will outline the project information provided and then sequentially illustrate the cost and revenue input required of the student, the cost of goods sold worksheet, a comparison of actual results to master and flexible budgets, a production variance analysis, and the income statements and reconciliation of net income under all cost systems and reporting formats. Two spreadsheets, one for year one and the other for year two, will be presented. In year one, production will exceed sales, building up inventory. In year two, production will be less than sales, decreasing inventory.
PROJECT
DESCRIPTION
Problem Data The following are standard costs per television for Acme Television Inc. The standard costs remain the same for Years 1 and 2. Acme Co. Standard Cost per Television
Material Labor Variable OH Fixed OH Total Standard
Quantity
Price/Unit
15 lbs. 4 hrs.
$lO/lb. $15/hr.
Cost per Television
Total per Television $150 $ 60 $ 60 $100 $370
Estimated production and sales is 1,500 televisions each year. Estimated sales price is $1,400 a unit each year. Overhead is based on direct labor hours, where OH=$150,000+$15 DLH. Estimated marketing and administrative expenses are $240,000 fixed and $55 a unit variable. Listed below are actuaI results of Operations for Years 1 and 2: Year 2*
Year 1 Production Material Labor Variable OH Fixed OH Variable Marketing & Administration Fixed Marketing & Administration Sales
*Assume
FIFO inventory.
1,600 units 25,000 lbs. x $8 6,200 hrs. ~$16 80,000 140,000 75,000 250,000 1,200 units x $1,250
1,300 units 18,500 lbs. x$11 5,500 hrs. ~$14 85,000 165,000 85,000 250,000 1,700 unitsx$1,550
A Spreadsheet
Analysis
347
Problem Input and Cost of Goods Sold Total revenue and marketing and administrative expenses are entered by the student based upon the given data. Product costs should be calculated as delineated in Table 1 and entered in the template provided by the instructor. Table 2 requires student input for beginning finished goods, in both dollars and units and unit data for cost of goods manufactured and cost of
Table 1. Revenue and costs Year 1 A 6 C D E F G H
I J
Revenue-F Est Revenue-B Est Revenue-Act Material Cost-Act Labor Cost-Act Material Cost-Std Labor Cost-Std Var Mktg & Admin-Act Fix Mktg & Admin-Act Var Mktg & Admin-F Est
$1,680,000 $2,100,000 $1,500,000 $ 200,000 $ 99,200 $ 240,000 $ 96,000 $ 75,000 $ 250,000 $ 66,000
Var Mktg & Admin-B Est Fix Mktg & Admin-Est Var OH-Act Fix OH-Act Var OH-App Fix OH-App Var OH-Std Fix OH-Std FX OH-B
$ 82,500 $240,000 $ 80,000 $140,000 $ 93,000 $155,000 $ 96,000 $160,000 $150,000
Var Mktg & Admin-B Est Fix Mktg & Admin-Est Var OH-Act Fix OH-Act Var OH-App Fix OH-App Var OH-Std Fix OH-Std Fix OH-B
$ 82,500 $240,000 $ 85,000 $165,000 $ 82,500 $137,500 $ 78,000 $130,000 $150,000
Year 2 A 6 C D E F G H
I J
Revenue-F Est Revenue-B Est Revenue-Act Material Cost-Act Labor Cost-Act Material Cost-Std Labor Cost-Std Var Mktg & Admin-Act Fix Mktg & Admin-Act Var Mktg & Admin-F Est
$2,380,000 $2,100,000 $2,635,000 $ 203,500 $ 77,000 $ 195,000 $ 78,000 $ 85,000 $ 250,000 6 93,500
Kev: A ’ Revenue-F Est: Units sold x std price per unit B Revenue-B Est: Units budgeted x std price per unit C Revenue Act: Units sold x actual price per unit D Material-Act: Pounds used x price per pound E Labor-Act: Hours used x price per hour F Material-Std: Std pounds per unit x std price per pound x actual units prod G Labor-std: Std hours per unit x std price per hour x actual units prod H Variable Mktg & Admin-Act: Entered as given I Fixed Mktg & Admin-Act: Entered as given Variable Mktg & Admin-F Est: Units sold budgeted x budgeted cost unit J K Variable Mktg & Admin-B Est: Units sold actual x budgeted cost unit Fixed Mktg & Admin-Est: Entered as given L M Variable OH-Actual: Entered as given N Fixed OH-Actual: Entered as given 0 Variable OH-Applied: Variable OH rate x actual direct labor hours Fixed OH-Applied: Fixed OH rate x actual direct labor hours Variable OH-Std: Variable OH rate x standard direct labor hours : R Fixed OH-Std: Fixed OH rate x standard direct labor hours S Fixed OH-Budgeted: Entered as given
P. Chalos
348
Table 2. COGS worksheet Year Units
Act. Full
Act. Var.
1
Norm. Full
Norm. Var.
Std. Full
Std. Var
Beg. Fin. Goods COG Manufactured Avail. for Sale COGS End. Fin. Goods
O$
O$
O$
0
$
O$
O$
0
1,600 1,600 1,200
$519,200 $519,200 $398,400
$379,200 $379,200 $284,400
$547,200 $547,200 $410,400
$392,200 $392,200 $294,150
$592,000 $592,000 $444,000
$432,000 $432,000 $324,000
400
$129,800
$ 94,800
$136,800
$ 98,050
$148,000
$108,000
Std. Full
Std. Var.
Year 2 Units Beg. Fin. Goods COG Manufactured Avail. for Sale COGS End. Fin. Goods
Act. Full
Act. Var
400
$129,800
$ 94,800
$136,800
$ 98,050
$148,000
$108,000
1,300 1,700 1,700
$530,500 $660,300 $660,300
$365,500 $460,300 $460,300
$500,500 $637,300 $637,300
$363,000 $461,050 $461,050
$481,000 $629,000 $629,000
$351,000 $459,000 $459,000
O$
O$
O$
Norm. Full
0
Norm. Var.
$
O$
O$
goods sold. (Because it is assumed that Year 1 is the first year Acme Inc. is in business, there is no beginning finished goods.) For the cost of goods manufactured, students are required to input the units produced. This number is given as part of the actual results of operations data. Formulas are used for the cost of goods manufactured cell in each costing method. Actual full-absorption COGM is the sum of all actual manufacturing costs for the period, provided as student input in Table 1. This includes material, labor, and both variable and fixed overhead. (Year 1 COGM= 200,000+99,200+80,000+ 140,000.) Actual variable COGM is the sum of all actual manufacturing costs with the exception of fixed overhead, which is not inventoried, but expensed as a period cost. (Year 1 COGM =200,000+ 99,200+80,000.) Normal full-absorption COGM is the sum of actual material and labor, and applied fixed and variable overhead. The amount applied is dependent on the predetermined overhead rates. (Year 1 COGM=200,000+99,200+ 93,000+ 155,000.) The difference between actual and normal overhead is the amount under or overapplied. Normal variable COGM is the same as the above except that the fixed overhead applied is excluded. Actual fixed overhead is expensed as a period cost. (Year 1 COGM =200,000+99,200+ 93,000.) Standard full COGM is the sum of all manufacturing costs at standard. (Year 1 COGM=240,000+96,000+96,000+ 160,000.) Standard variable
0
A Spreadsheet Analysis
349
COGM is the same as the above with the exception of fixed overhead. Actual fixed overhead is expensed as a period cost. (Year 1 COGM =240,000+ 96,000+96,000.) Summarizing, the cost of goods manufactured under the three costing systems would comprise the following: Actual Raw Material Direct Labor Variable Overhead Fixed Overhead (Full Absorption
Normal
Standard
Actual Actual Standard Actual Actual Standard Actual Normal Standard ___-----___---______----___-----____--Actual
Normal
Standard
only)
Because a FIFO inventory flow is assumed, COGS under all methods is the sum of the value of beginning finished goods and the value of the proportion of goods manufactured during the period needed to meet sales. (For example, under actual full costing for Year 1, COGS=[OBeg.Inv,+ 12001 1600 (519,200)J.) To meet sales in Year 2, all beginning inventory and all goods manufactured are needed. (Therefore, Year 2 COGS= 1129,8OOB,p.r,,.+1300/1300 (530,500)].) The same reasoning is used to calculate COGS under each costing method.
Actual to Budget Comparisons
and Production
Variances
Table 3 highlights actual results relative to master and flexible budgets. All of the data necessary for budgetary analysis is included in Tables 1 and 2. Data entered as student input in Table 1, which are forwarded to the new budgetary cell addresses, include actual, flexible and master budget revenues; actual, flexible and master budget variable marketing and administration, actual and budgeted fixed overhead, and actual budgeted marketing and administration. The remaining input required, variable cost of goods sold, is forwarded from Table 2. The flexible variable cost of goods sold is forwarded from the standard variable cost of goods sold of Table 2, and can also be used for the master budget variable cost of goods sold. (Year 1 = 151 12(324,000).) The remaining cells are arithmetically derived from the input provided. These include contribution margin, profit and all volume, manufacturing, marketing and administrative and revenue variances. A useful breakdown of individual unprorated production variances is provided in Table 4. Student input of problem data is required for actual unit price, standard unit price, estimated production hour quantity, actual quantity of inputs for actual production and standard quantity of inputs for actual production. Actual raw material and direct labor unit prices are given. Actual unit variable and fixed overhead prices for Year 1 are derived as $80,000/6,200 and $140,000/6,200, respectively. Standard price per unit, estimated hours of production and actual quantities used are given. Year 1
350
P. Chalos
Table 3. Budget to Actual Year 1
Actual (1200 Units) Revenue \/ar.COGS Var.Mktg.and Adm.
1,500,000 284,400
Contrib. Margin FIX. OH Fix. Mktg. & Adm.
1,140,600 140,000 250,000
Profit
Manufacturing, M&A and Revenue Variances (180,000) 39,600 (9,000)
75,000
10,000 (10,000)
750,600
Flexible Budget (1200 Units)
Volume Variances
1,680,000 324,000
(420,000) 81,000
66,000
16,500
Master Budget (1500 Units) 2,100,000 405,000 82,500
1,290,000 150,000 240,000
1,612,500 150,000 240,000
900,000
1.222,500
Budget to Actual Year 2
Actual (1700 Units) Revenue Var. COGS Var. Mktg. and Adm.
2,635.OOO 460,300
Contrib. Margin Fix. OH Fix. Mktg. & Adm.
2,069,700 l65,OOO 250,000
Profit
1,674,700
Manufacturing, M&A and Revenue Variances
85,000
255,000 (1,300) 8,500 (15,000) (10,000)
Flexible Budget (1700 Units) 2,380.OOO 459,000 93,500
Volume Variances 280,000 (54,000) (tl,ooo)
Master Budget (1500 Units) 2,100,000 405,000 82,500
1,827,500 150,000 240,000
1,612,500 150,000 240,000
1,437,500
1,222,500
standard quantities for raw material and direct labor are 15 x 1,600 and 4 x 1,600 respectively. Cell formulas are used for the variance calculations. Income Statement Reconciliation
The income statements are preprogrammed to be derived from the cost of goods sold data in Table 2 and cell information carried forward from Table 1. The appropriate cost of goods sold figure from Table 2 is carried forward to the relevant income statement, as are marketing and administrative expenses from Table 1. As shown in Table 5, the marketing and administration figure is combined under full absorption, while under variable costing it is segmented. In addition, under variable costing, a fixed overhead line item is added, based on data from Table 1. Under normal costing, a line for over/underapplied overhead is needed. Based on Table 1 data, under full absorption Year 1 this represents the difference between actual and applied overhead, prorated by the goods sold (1,200/1,600 [80,000+~40,000-93,~0-155,0~]). In Year 2, the remaining balance is written off as is all of Year 2 over/under applied overhead ([1,2001 1,600[80,000+140,000-93,000-155,000]-1,300/1,300[85,000+165,00082,000- 137,500]). An alternative treatment would be to simply write off the entire variance in the year of occurrence. Total overhead is used for full
A Spreadsheet
Analysis
351
absorption costing, while variable overhead is used for variable costing. If overhead is underapplied, it is considered an additional cost of goods sold expense and subtracted from revenue. If overapplied, it is added to revenue. Finally, if standard costing is used, variances need to be written off. These represent the proportion of the difference between actual and standard production costs that are in the cost of goods sold. This number is obtained from Table 2 data by subtracting standard from actual cost of goods sold. For example, in Table 2, Year 1, the difference between actual and standard full cost of goods sold would be $54,600. This represents the pro rata share of the units sold to the total units produced and their related production variances ([1,200/1,600[200,000+99,200+80,000+ 140,000] -[240,000+ 96,000+96,000+ 160,000]). Similarly in Year 2, the unfavorable difference of $3 1,300 between actual and standard full cost of goods sold is explained by the 400 units of Year 1 inventory plus all units manufactured and sold in Year 2 ([400/l ,600[72,8001,300/l ,300[49,500]). Under variable costing, the Year 1 difference represents the pro rata share of variable production variances written off in the period as a function of units sold ([1,200/l ,600($52,800) = $39,600]). Year 2 variances represent the reduction in inventory from Year 1 plus Year 2’s unfavorable variable production variances ([400/1,600($52,800)-$14,500=$1,300]). Finally, full absorption and variable income differences are due to the fixed overhead included in ending inventory under full absorption that is expensed as a period charge under variable costing. Under all three cost systems, profit differences are $35,000. Since inventory is increased in Year 1, part of the actual fixed overhead is included in ending inventory under
Table 4.
AP Raw Mat. Dir. Lab. Var. OH Fixed OH
8 16 12.9032 22.5806
SP 10 15 15 25
EQ
Production Variances Year 1 PV AQ SQ 25,000 6,200 6,200 6,200
6,000
24,000 6,400 6,400 6,400
50,000 (6,200) 13,000 10,000
vv
QV (10,000) 3,000 3,000
10,000
Total
Raw Mat.
11 14 15.4545 30
SP 10 15 15 25
EQ
Production Variances Year 2 PV AQ SQ
6,000
18,500 5,500 5,500 5,500
19,500 5,200 5,200 5,200
Total Key: AP SP EQ AQ
40,000 (3,200) 16,000 20,000 72,800
AP
Dir. Lab. Var. OH FixedOH
Total
(18,500) 5,500 (2,500) (15,000)
vv
(20,000)
QV
Total
10,000 (4,500) (4,500)
(8,500) 1,000 (7,000) (35,000) (49,500)
Actual price Standard price Estimated quantity Actual quantity
SO PV vv QV
Standard quantity Price variance Volume variance Quantity variance
P. Chalos
352
Table 5. Income Statements Year 1 Actual-Full
Absorption
Normal-Full
Absorption
Standard-Full
Absorption
Revenue COGS Mktg. & Admin.
$1,500,000 $ 389,400 $ 325,000
Revenue COGS U/O Applied OH Mktg. & Adm.
$
325,000
Mktg. & Adm.
$
325,000
Profit
$
Profit
$
785,600
Profit
$
785,600
785,600
$1,500,000 $ 410,400 (21,000)
Revenue COGS Variances
Normal-Variable
Actual-Variable $1,500,000 $ 284,400
Revenue Var. COGS Var. Mktg. & Adm.
$
75,000
Revenue Var. COGS Var. Mktg. & Adm.
Contrib. Margin Fix. OH Fix. Mktg. & Adm.
$1,140,600 $ 140,000
Profit
Standard-Variable
$1,500,000 $ 294,150 $
U/O Applied OH
$
250,000
Contrib. Margin Fix. OH Fix. Mktg. & Adm.
$
750,600
Profit
$1,500,000 $ ‘444,000 (54,600)
75,000 ($9,750)
$1,140,600 $ 140,000 $ $
Revenue Var. COGS Var. Mktg. & & Adm.
$1,500,000 $ 324,000 $
Variances
75,000 ($39,600)
$1,140,600 $ 140,000
250,000
Contrib. Margin Fix. OH Fix. Mktg. & Adm.
$
250,000
750,600
Profit
$
750,600
Year 2 Actual-Full
Absorption
Normal-Full
Absorption
Revenue COGS Mktg. &Admin.
$2,635,000 $ 660,300 $ 335,000
Revenue COGS U/O Applied OH Mktg. & Adm.
$
Profit
$1,639,700
Profit
$1,639,700
Contrib. Margin Fix. OH Fix. Mktg. & Adm. Profit
335,000
$2,635,000 $ 460,300
$2,635,000 $ 629,000 $ 31,300
Mktg. & Adm.
$
Profit
$1,639,700
$
85,000
$2,089,700 $ 165,000 $
250,000
$1,674,700
Standard-
Revenue Var. COGS Var. Mktg. & Adm.
$2,635,000 $ 461,050
U/O Applied OH
$
Contrib. Margin Fix. OH Fix. Mktg. & Adm.
$2,089,700 $ 165,000 $
Profit
$1,674,700
$
85,000 (750)
250,000
Absorption
Revenue COGS Variances
Normal-Variable
Actual-Variable Revenue Var. COGS Var. Mktg. & Adm.
$2,635,000 $ 637,300 $ 23,000
Standard-Full
335,000
-Variable
Revenue Var. COGS Var. Mktg. & & Adm.
$2,635,000 $ 459,000 $
85,000
Variances
$
1,300
Contrib. Margin Fix. OH Fix. Mktg. & Adm.
$2,089,700 $ 165,000 $
Profit
$1,674,700
250,000
A Spreadsheet
Analysis
353
full absorption ([400/1,600($140,000)=$35,000]). As inventory is decreased in Year 2, part of Year l’s fixed overhead is included in Year 2’s cost of goods sold under full absorption costing. Since the increase in Year 1 is the same as the decrease in Year 2, the opposite effect occurs.
SUMMARY This comprehensive illustration of budgets and variances in conjunction with different cost systems has been found to be very effective in the classroom as an end of course review assignment. Students particularly appreciate the synthesis of cost reporting systems and formats provided by such an assignment. The computational power provided by the spreadsheet enables the student to focus on conceptual inputs. Without it, the problem would be extremely time consuming, requiring repetitive manual input of accounts and arithmetic computations on the part of each student. This is eliminated via a one time instructor input on a spreadsheet template which can be made available to students in a PC laboratory, A major caveat in using the spreadsheet is that the student’s thinking not be replaced by the instructor?. Thus, it is suggested that the student be required to provide journal entries for a subset of the data, using the various cost and inventory accounts represented in the different cost systems. (This is particularly true if the student has not provided any spreadsheet formulas). This exercise reinforces the student’s understanding of the concepts and complements the computational advantages of the spreadsheet. The instructor may extend the problem further by incorporating, for example, cost estimation techniques to justify the overhead allocation basis chosen or include beginning and ending balances for work in process equivalent unit valuation. Students could also be required to perform sensitivity analysis on several of the key parameters such as revenue estimates that can be changed in order to examine the impact upon volume variances associated with the master budget, or production estimates that can be changed to determine the impact upon production volume variances in standard costing and under/over applied overhead in normal costing. Similarly, the price and quantity standards for production inputs can be manipulated. Finally, a different basis of overhead allocation may be used to test the sensitivity of overhead variances to the basis of allocation used. By running the numbers under different assumptions, the student will better appreciate the computational power of the spreadsheet.