Please review the attachment and complete the spreadsheet thats found in the chap 2 HW tab. Please submit back to me no late than 4:00 EST. Thank you kindly
C
h 2
cel
le> ype of Costs & Expenses
atural Cost or Expense
the type of spending
unctional cost or Expense
or ariable
Cost or Expense
roduct or Cost or Expense
/ nventoriable] or not
irect or Indirect Cost or Expense
s
]
]
operating dmin
uct
ion
arehouse
s:
00
9,000 ion Direct labor
– 0 – 0 115,000 7,000 – 0 – 0 – 0 7,000 400 200 uilding rent expense
12,500 400 600 – 0 – 0 300 1,900 3,200 – 0 – 0 200 – 0 – 0 200 iscellaneous expense
1,400 ,600
ob
3,000 – 0 15,000 2,200 850 0 company
aw materials
CoGS Raw materials +
use Raw materials =
Raw materials –
65,000 Direct cost 21,000 Conversion cost Conversion cost 141,000 Beginning Ending 100,000 20,000 55,000 164,000 ‘+’ Excel 1 Product cost flows Manufacturing company Raw materials WIP FG CoGS set:
GAAP Fixed 100,000 900 :
11 0
Sales Sales Buy materials + 20,000 CoGS CoGS Available for use Raw materials = 120,000 .00
Sell.Exp 55,000 .00
Admin.Exp 65,000 .00
Sell.Exp Sell.Exp Sell.Exp =4000+ sales
.00
Admin.Exp Admin.Exp Beginning WIP + 77,000 N/A N/A N/A Issued to WIP from Raw material inventory + 65,000 .00
Sell.Exp Sell.Exp Direct labor + 21,000 each CoGS CoGS Applied overhead [Indirect costs] + 42,000 .00
Admin.Exp Admin.Exp 205,000 .00
Sell.Exp Sell.Exp Ending WIP Inventory – 64,000 .00
Admin.Exp Admin.Exp COG manufactured; [sent to FG] = 141,000 .00
Sell.Exp Sell.Exp .00
Labor Beginning FG + 214,000 .00
CoGS CoGS To FG from WIP; CoG Manufactured + 141,000 .00
CoGS CoGS Mfg. overhead CoG Available for Sale = 355,000 .00
CoGS CoGS Mfg. overhead Ending FG inventory – 191,000 .00
Admin.Exp Admin.Exp .00
CoGS CoGS Mfg. overhead CoGS = 164,000 CoGS CoGS Mfg. overhead .00
CoGS CoGS Summary Beginning Additions Ending RW 100,000 20,000 55,000 WIP 77,000 21,000 64,000 $ 110.00 ‘+’ ‘+’ ‘-‘ ‘=’ $ 55,800 x $ 4,000 s $ 1,575 x $ 990 s Variable $ 66,750 Cost of Goods Sold x $ 55,800 $ 61,425 $ 61,425 s
$ 4,455 $ 1,575 $ 990 $ 2,650 $ 66,870 $ 66,870 $ – 0 $ 24,795 $ 800 $ – 0 Manufacturing $ 400 $ 200 $ 24,675 -Lo Method
for + ed
Y
= X
= low
$ = hi
:Min [Lo] – Max {Hi]
2
$ 21,860 Hi $ 21,860 $ 9,830 $ 9,830 bX = a $ 21,860 Low fixed = a + bX
(array1, array2) function returns the Square of the Product-Moment Correlation Coefficient between two arrays of data.
Modeled $ Difference 92 RSQ = b “
= a std deviation 543 Y Model Pearson $ 15,483 X 0.916762505 //HCT
Hi-Low Regression Regression $ 10,113 $ 10,568 4.5% $ 10,932 8.1% 4.5% 8.1% $ 12,691 $ 11,085 %
$ 11,467 9.6% $ 10,905 $ 11,765 $ 12,170 11.6% 7.9% 11.6% $ 12,949 $ 12,608 $ 13,041 0.7% 0.7% $ 15,334 $ 15,017 $ 15,531 1.3% 1.3% $ 21,455 $ 19,795 $ 20,469 4.6% $ 21,270 $ 21,860 $ 22,604 6.3% 2.8% 6.3% $ 19,930 $ 21,267 $ 21,991 10.3% 6.7% 10.3% $ 21,860 $ 18,952 $ 19,598 10.3% -10.3% $ 18,383 $ 15,668 $ 16,204 11.9% $ 9,830 $ 9,830 $ 10,170 3.5% 0.0% 3.5% $ 11,081 $ 11,237 $ 11,624 4.9% 1.4% 4.9% 0.9% Average absolute error 160 = mean of X
= mean of Y
3
12.7 0.0 Pearson product moment The variability of Y attributable to the variability of X Parson done by “hand” using formula above ACC220 #1 Actual = Y Data Data 5800 Data Data Data Data units 4.50% #1 Difference Difference $s %
T
s
Excel 1
N
Expense
by
F
Expense by function, department or organizational heirarchy
Fixed
V
Varies with sales or production volume or does not
P
Period
Part of product cost [
Co
G
S
I
D
Direct: tracable to a cost object [such as a product or capital project]; Indirect: not tracable may be assignable
Conversion cost
Direct labor & manufacturing overhead [sometimes only variable
O
H
Prime costs
Direct materials & direct labor [sometimes varaible fringes on D
L
Common cost
Cost on support to a group of cost objects but not tracable
Tracable costs
Cost directly tracable to cost object
Natural Expenses
Department or Function
operating
CoGS
distribution
Sales
A
Prod
W
Total
Salary Expense
25,
0
1
7,000
36,000
9,000
87,000
Wage Expense
16,000
58,000
1
2,500
95,500
Product
– 0
1
15,000
Fringe benefit expense
1
3,000
8,800
74,000
6,
400
102,
200
Commission expense
Advertising expense
9,500
750
10,
850
B
3,200
3,
600
4,800
24,100
Equipment lease expense
2,
300
6,
900
2,230
11,830
Office supply expense
1,900
1,400
2,300
6,200
Contract labor Expense
34,000
3,320
37,320
Travel/entertainment exp.
3,600
450
2,000
6,
350
Professional services
2,150
7,
250
Bank charges/fees
Depreciation expense
2,200
1,975
44,000
11,000
59,175
M
1,100
3,950
2,
160
8,610
Total
85,100
48,375
3
92
52,510
578,585
Sales Admin Production Warehouse
J
Job AB1
Job AB2
Job AB3
Totals
Salary Expense
12,000
Wage Expense 2,300 6,200 – 0
8,500
Production Direct labor
21,000
23,000
66,000
110,000
Fringe benefit expense
6,500
9,100
17,800
Commission expense 2,500 – 0
1,300
3,800
Advertising expense – 0 – 0 – 0 – 0
Building rent expense – 0 – 0 – 0 – 0
Equipment lease expense – 0 – 0 2,150 2,150
Office supply expense – 0 – 0 – 0 – 0
Contract labor Expense
1,500
2,650
11,500
15,650
Travel/entertainment exp. 250 350 600
1,200
Professional services
1,110
1,950
3,910
Bank charges/fees – 0 – 0 – 0 – 0
Depreciation expense – 0 – 0 – 0 – 0
Miscellaneous expense
875
625
325
1,825
Totals:
48,035
45,775
86,025
179,835
Credit
Accounts Payable
0
0
Product cost flows
Manufacturing
R
WIP
FG
Beginning
100,000
Buy materials +
20,000
Available
for
120,000
Ending
55,000
RM used for Production =
65,000
Beginning WIP +
77,000
Issued to WIP from Raw material inventory +
Direct cost
Direct labor +
Applied overhead [Indirect costs] +
42,000
Total Manufacturing costs; WIP for Period =
205,000
Ending WIP Inventory –
64,000
COG manufactured; [sent to FG] =
141,000
Beginning FG +
214,000
To FG from WIP; CoG Manufactured +
CoG Available for Sale =
355,000
Ending FG inventory –
191,000
CoGS =
164,000
Summary
Additions
RW
WIP 77,000 21,000 64,000
FG 214,000 42,000 191,000
391,000
83,000
310,000
‘+’
‘-‘
‘=’
Ch. 2A Excel
Income statements: Chapter 2
Excel 2
CoGS = Product cost //
GAAP
Month: July
Var. Income Statement
Comment
Data
Manufacturing business
Variable
Beginning Raw materials +
A
Sold
units
sell price
each
$
0.0
Materials
B
Purchased Materials Cost each
$ 62.00
C
sales rent per month
$ 3,000
Sell.Exp
Ending Raw materials –
D
Insurance costs month:
$ 1,200
Admin.Exp
RW used for Production =
E
Sales salaries exp:
$ 4,000
Commission on sales
4.50
%
Y
4.5%
X
G
Admin salary expense
$ 2,800
H
Purchase new servers
$ 11,000.00
N/A
Capital expenditure
I Advertising expense
$ 950
J
Product brochures attached to each sold item
$ 1.75
K
Communication expense
$ 2,450
Total Manufacturing costs;WIP for Period =
L
Depreciation expense: Sales Office
$ 1,900
M
Depreciation expense: general & admin
$ 1,800
N
Shipping Warehousing rent
$ 1,050
O
Factory direct labor
$ 2,650
Labor
Direct Labor
P
Component warehouse
$ 1,175
Mfg. overhead
Q
Factory Mamagement
$ 2,290
R
Fringe benefits for direct labor
$ 800
S
Fringe benefits for Admin.
$ 200
T
Process utilitiy cost
$ 400
U
Solder & solvents
$ 200.00
V
Derperciation factory Bldg. & eqpmnt.
$ 1,860
W
Variable Administrative expenses
1%
of sales
$s
Month: July Var. Income Statement Comment FG 214,000 42,000 191,000
Data
set: Manufacturing business Product Period Fixed Variable 391,000 83,000 310,000 164,000
A Sold 900 units
sell price each:
Revenue
$ 99,000
B Purchased Materials Cost each $ 62.00
$ 55,800
C sales rent per month $ 3,000.00 $ 3,000 $ 3,000
D Insurance costs month: $ 1,200.00 $ 1,200 $ 1,200
E Sales salaries exp: $ 4,000.00
+ Commission on sales
4.50%
$ 8,455
$ 4,455
G Admin salary expense $ 2,800.00 $ 2,800 $ 2,800
H Purchase new servers $ 11,000.00 N/A N/A N/A N/A Capital expenditure
I Advertising expense $ 950.00 $ 950 $ 950
J Product brochures attached to each sold item $ 1.75 each
$ 1,575
K Communication expense $ 2,450.00 $ 2,450 $ 2,450
L Depreciation expense: Sales Office $ 1,900.00 $ 1,900 $ 1,900
M Depreciation expense: general & admin $ 1,800.00 $ 1,800 $ 1,800
N Shipping Warehousing rent $ 1,050.00 $ 1,050 $ 1,050
O Factory direct labor $ 2,650.00 $ 2,650 $ 2,650 x
P Component warehouse $ 1,175.00 $ 1,175 $ 1,175
Q Factory Mamagement $ 2,290.00 $ 2,290 $ 2,290
R Fringe benefits for direct labor $ 800.00 $ 800 $ 800 x
S Fringe benefits for Admin. $ 200.00 $ 200 $ 200
T Process utilitiy cost $ 400.00 $ 400 $ 400 x
U Solder & solvents $ 200.00 $ 200 $ 200 x
V Derperciation factory Bldg. & eqpmnt. $ 1,860.00 $ 1,860 $ 1,860
W Variable Administrative expenses 1% of sales $s
$ 990
Totalsè
$ 66,750
$ 24,795
$ 24,675
$ 66,870
←Totals
No change in FG/WIP inventory
Contribution format
GAAP
Managerial Accounting
Sales $ 99,000 Sales $ 99,000
Cost of Goods Sold
Variable Costs & Expenses
Gross Margin [Gross Profit]
$ 32,250
$ 61,425
Variable
Selling Expense
$ 5,445
Selling &
Administrative Expense
Variable Admin. Expenses
Selling Expense
$ 15,355
Total Var. Costs & Expenses
$ – 0
Administrative Expense
$ 9,440
Contribution Margin
$ 32,130
32.5%
Total Sales & Administrative Expense
Fixed Expenses
Net Operating Income
$ 7,455
$ 5,325
Selling Expense
$ 10,900
Administrative Expense
$ 8,450
Total Sales & Admin. Expense
Net Operating Income $ 7,455 0
check
Income is the same without consideration of ending inventory changes
IF ending WIP nd/or FG changes then GAAP is NOT equal to Variable format
Ch.2 B Excel
Chapter 2
Regression
Hi
Y
= a
bX
Excel 3
Compute “b” first then compute “a”
A B C
Model
A6
Actual
Actual = Y
using hi – low
Excel
Min
A7
Units
Excel
Max
Divide ∆ $s by
A8
2310
$ 10,113
$ 10,568
A9
2453
$ 12,691
$ 11,085
Difference
A10
2641
$ 10,905
$ 11,765
∆ Units
∆$s
A11
2874
$ 12,949
$ 12,608
3326
$ 12,030
∆ $ & ∆ units
A12
3540
$ 15,334
$ 15,017
A13
4861
$ 21,455
$ 19,795
Imputed variable per unit
∆ $ / ∆ units
= b
A14
543
$ 21,270
$ 21,860
$ 3.617
= b the variable cost per unit
A15
5268
$ 19,930
$ 21,267
A16
4628
$ 18,952
Formula
Low
A17
3720
$ 18,383
$ 15,668
Y = 3.617X + 2212.598
Spent
$ 9,830
A18
2106
$ 19,648
$ 7,617
A19
2495
$ 11,081
$ 11,237
after you have computed “b” then compute “a’
$ 2,212
$ 2,213
x = Qty
Varible-bX
Min 2106 $ 9,830 Hi
$ 7,617.40
$ 2,212.60
fixed
Max
5432
$ 19,647.54
$ 2,212.46
Regression
Regression Method for
Y
= a
Excel 4
The
RSQ
Pearson
A B C D E F
A6 Actual Actual
Modeled
A7 Units $ $
% accuracy
A8 2310 $ 10,113
$ 10,932
8.1%
819
A9 2453 $ 12,691
$ 11,467
–
9.6%
(1,224)
A10 2641 $ 10,905
$ 12,170
11.6%
1,265
Can use excel wizard for help with formula
A11 2874 $ 12,949
$ 13,041
0.7%
0.916762505
RSQ = Parson squared, Excel = “+ RSQ”
A12 3540 $ 15,334
$ 15,531
1.3%
197
A13 4861 $ 21,455
$ 20,469
–
4.6%
(986)
3.7385
Slope
Excel “+
slope
for computations by “hand” see cell A65
A14 5432 $ 21,270
$ 22,604
6.3%
1,334
$ 2,296
Intercept
Excel “+ intercept”
for computations by “hand” see cell A91
A15 5268 $ 19,930
$ 21,991
10.3%
2,061
A16 4628 $ 21,860
$ 19,598
-10.3%
(2,262)
=+SLOPE(C29:C40,B29:B40)
A17 3720 $ 18,383
$ 16,204
–
11.9%
(2,179)
=+INTERCEPT(C29:C40,B29:B40)
A18 2106 $ 9,830
$ 10,170
3.5%
340
std deviation
A19 2495 $ 11,081
$ 11,624
4.9%
4799
X Y
sum►►
(0)
4595
0.9574771564
=+G41/G40
3527.3
$ 15,483
0.9%
1229
P2 = RSQ
=+I41^2
mean
regression error
Ch.2
ACC220
Hi-Low
Excel 5
Actual Actual Modeled % Modeled %
Net
Error
Units $ $s Error $s Error Hi-Low Regression
2,310
2,453
12.7
-12.7%
-9.6%
2,641
7.9%
2,874
2.6%
-2.6%
3,540
2.1%
-2.1%
4,861
7.7%
-7.7%
-4.6%
5,432
2.8%
5,268
6.7%
4,628
13.3%
-13.3%
3,720
14.8%
-14.8%
-11.9%
2,106
0.0%
2,495
1.4%
6.4%
6.9%
-2.5%
Average
average error
absolute error
RSQ X Y Prod
X2
Y2
by
1,217
5,370
6,537,587
1,481,900
28,841,375
Hand
1,074
2,792
2,999,986
1,154,192
7,797,591
886
4,578
4,058,003
785,587
20,961,899
653
2,534
1,655,819
426,844
6,423,268
(13)
149
(1,893)
22,325
(1,334)
(5,972)
7,964,102
1,778,667
35,659,808
(1,905)
(5,787)
11,021,512
3,627,755
33,484,547
(1,741)
(4,447)
7,740,019
3,029,920
19,772,103
(1,101)
(6,377)
7,018,493
1,211,467
40,660,815
RSQ = Pearson2 = correlation coefficient2
(193)
(2,900)
558,653
37,120
8,407,584
1,421
5,653
8,035,390
2,020,188
31,961,120
RSQ
1,032
4,402
4,544,761
1,065,712
19,381,273
by
Sum of products
62,132,433
16,619,515
253,373,707
Sums
Hand
4,077
15,918
SqRoot of sum
64,891,818
Products of Square roots
0.9575
Pearson [P] = r
0.9168
Pearson2 = RSQ
Slope By “Hand”
b = slope
_
X
_
Y X Y
X – mean
Y – mean
X x Y
∆X2
2310 $ 10,113
-1217.3
$ (5,370)
6537587
1481900.44444444
2453 $ 12,691
(18.03)
-1074.3
$ (2,792)
2999986
1154192.11111111
2641 $ 10,905
9.50
-886.3
$ (4,578)
40
5800
785586.777777778
2874 $ 12,949
(8.77)
-653.3
$ (2,534)
1655819
426844.444444445
3540 $ 15,334
(3.58)
$ (149)
-1893
160.4444444444
4861 $ 21,455
(4.63)
1333.7
$ 5,972
7964102
1778666.77777778
5432 $ 21,270
0.32
1904.7
$ 5,787
11021512
3627755.11111111
5268 $ 19,930
(8.17)
1740.7
$ 4,447
7740019
3029920.44444444
4628 $ 21,860
3.02
1100.7
$ 6,377
7018493
1211467.11111111
3720 $ 18,383
(3.83)
192.7
$ 2,900
558653
37120.4444444444
2106 $ 9,830
(5.30)
-1421.3
$ (5,653)
8035390
2020188.44444444
2495 $ 11,081
(3.22)
-1032.3
$ (4,402)
4544761
1065712.11111111
X Y
-0.0
62132433.3333333
16619514.6666667
3527.3 $ 15,483
$ (4)
mean 3.7385 slope
Intercept By “Hand”
Y = a + bX
after you have the slope then
intercept = [Mean of Y] – [mean of X]*[slope]
_
Y $ 15,483
_
X 3527.3
Slope 3.7385
Intercept
2,296.4
correlation coefficient
Assumes
Normal
Distribution
Sum of the
products of the
difference from the
mean for x and y
divided by the
square root of the
product of the sum
the squares of the
difference from the
mean for x time
same sum for Y
Ch.2 HW
Chapter 2 HW
Hi-Lo Method
A B C
Actual = X
Units $
B8
5500
$ 21,000
B9
5600
$ 21,200
B10
$ 21,600
B11
6400
$ 22,700
B12
5400
$ 19,900
B13
5900
$ 22,000
a] Compute a, b, of Y=a + bx
b] Compute projected spending @
5750
c] prepare chart showing difference from formula determine & each actual value
d] With regression what would be the slope and intercept for Y = a + bx
Fixed/Variable Income Statement
#2
Product Period
Amount
Product Materials
$950,000
Direct Labor spending per unit
$0.70
Variable MOH per product unit
$2.25
Fixed Sellng
$165,500
Variable Selling per unit
of Sales $s
Adminstrative
$149,000
Fixed Manufacturing OH
$218,000
Sales Quantity
105,000
Sales price per unit
$20.00
[a]
Complete chart above
[b]
Create Variable Format AND GAAP format income statements
b
Chart c]
Actual X
Actusl $
Modeled Y
B8 5500 $ 21,000 $ – 0
B9 5600 $ 21,200 $ – 0
B10 5800 $ 21,600 $ – 0
B11 6400 $ 22,700 $ – 0
B12 5400 $ 19,900 $ – 0
B13 5900 $ 22,000 $ – 0