Accounting 220-MEG

profilepulzx2d4
CopyofCh.12ExcelwHWCanvas.xlsx
Home>Business & Finance homework help>Accounting homework help>Accounting 220-MEG

Ch 2 #1 Excel

Types of Costs & Expenses Excel 1
Natural Cost or Expense Expense by the type of spending
Functional cost or Expense Expense by function, department or organizational heirarchy
Fixed or Variable Cost or Expense Varies with sales or production volume or does not
Product or Period Cost or Expense Part of product cost [CoGS / Inventoriable] or not
Direct or Indirect Cost or Expense Direct: tracable to a cost object [such as a product or capital project]; Indirect: not tracable may be assignable
Conversion costs Direct labor & manufacturing overhead [sometimes only variable OH]
Prime costs Direct materials & direct labor [sometimes varaible fringes on DL]
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 operating CoGS distribution
Sales Admin Production Warehouse Totals:
Salary Expense 25,000 17,000 36,000 9,000 87,000
Wage Expense 16,000 9,000 58,000 12,500 95,500
Production Direct labor - 0 - 0 115,000 - 0 115,000
Fringe benefit expense 13,000 8,800 74,000 6,400 102,200
Commission expense 7,000 - 0 - 0 - 0 7,000
Advertising expense 9,500 400 750 200 10,850
Building rent expense 3,200 3,600 12,500 4,800 24,100
Equipment lease expense 400 2,300 6,900 2,230 11,830
Office supply expense 1,900 1,400 2,300 600 6,200
Contract labor Expense - 0 - 0 34,000 3,320 37,320
Travel/entertainment exp. 3,600 450 2,000 300 6,350
Professional services 1,900 2,150 3,200 - 0 7,250
Bank charges/fees - 0 200 - 0 - 0 200
Depreciation expense 2,200 1,975 44,000 11,000 59,175
Miscellaneous expense 1,400 1,100 3,950 2,160 8,610
Total 85,100 48,375 392,600 52,510 578,585
Sales Admin Production Warehouse
Job
Job AB1 Job AB2 Job AB3 Totals
Salary Expense 12,000 3,000 - 0 15,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 2,200 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 850 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
0
Product cost flows
Manufacturing company Raw materials WIP FG CoGS
Beginning Raw materials + 100,000
Buy materials + 20,000
Available for use Raw materials = 120,000
Ending Raw materials - 55,000
RM used for Production = 65,000
Beginning WIP + 77,000
Issued to WIP from Raw material inventory + Direct cost 65,000
Direct labor + Direct cost 21,000 Conversion cost
Applied overhead [Indirect costs] + 42,000 Conversion cost
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 + 141,000
CoG Available for Sale = 355,000
Ending FG inventory - 191,000
CoGS = 164,000
Summary Beginning Additions Ending
RW 100,000 20,000 55,000
WIP 77,000 21,000 64,000
FG 214,000 42,000 191,000
391,000 83,000 310,000 164,000
'+' '+' '-' '='

Ch. 2A Excel

Income statements: Chapter 2 Excel 2 Excel 1
CoGS = Product cost // GAAP Product cost flows
Month: July Var. Income Statement Comment Manufacturing company Raw materials WIP FG CoGS
Data set: Manufacturing business GAAP Variable Fixed Beginning Raw materials + 100,000
A Sold 900 units sell price each: $ 110.00 Sales Sales Materials Buy materials + 20,000
B Purchased Materials Cost each $ 62.00 CoGS CoGS Available for use Raw materials = 120,000
C sales rent per month $ 3,000.00 Sell.Exp Sell.Exp Ending Raw materials - 55,000
D Insurance costs month: $ 1,200.00 Admin.Exp Admin.Exp RW used for Production = 65,000
E Sales salaries exp: $ 4,000.00 Commission on sales 4.50% Sell.Exp Sell.Exp Sell.Exp Y=4000+4.5%Xsales
G Admin salary expense $ 2,800.00 Admin.Exp Admin.Exp Beginning WIP + 77,000
H Purchase new servers $ 11,000.00 N/A N/A N/A N/A Capital expenditure Issued to WIP from Raw material inventory + 65,000
I Advertising expense $ 950.00 Sell.Exp Sell.Exp Direct labor + 21,000
J Product brochures attached to each sold item $ 1.75 each CoGS CoGS Applied overhead [Indirect costs] + 42,000
K Communication expense $ 2,450.00 Admin.Exp Admin.Exp Total Manufacturing costs;WIP for Period = 205,000
L Depreciation expense: Sales Office $ 1,900.00 Sell.Exp Sell.Exp Ending WIP Inventory - 64,000
M Depreciation expense: general & admin $ 1,800.00 Admin.Exp Admin.Exp COG manufactured; [sent to FG] = 141,000
N Shipping Warehousing rent $ 1,050.00 Sell.Exp Sell.Exp
O Factory direct labor $ 2,650.00 Labor Labor Direct Labor Beginning FG + 214,000
P Component warehouse $ 1,175.00 CoGS CoGS Mfg. overhead To FG from WIP; CoG Manufactured + 141,000
Q Factory Mamagement $ 2,290.00 CoGS CoGS Mfg. overhead CoG Available for Sale = 355,000
R Fringe benefits for direct labor $ 800.00 CoGS CoGS Mfg. overhead Ending FG inventory - 191,000
S Fringe benefits for Admin. $ 200.00 Admin.Exp Admin.Exp
T Process utilitiy cost $ 400.00 CoGS CoGS Mfg. overhead CoGS = 164,000
U Solder & solvents $ 200.00 CoGS CoGS Mfg. overhead
V Derperciation factory Bldg. & eqpmnt. $ 1,860.00 CoGS CoGS Summary Beginning Additions Ending
W Variable Administrative expenses 1% of sales $s RW 100,000 20,000 55,000
WIP 77,000 21,000 64,000
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: $ 110.00 Revenue $ 99,000 '+' '+' '-' '='
B Purchased Materials Cost each $ 62.00 $ 55,800 $ 55,800 x
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,000 $ 4,455 s
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 $ 1,575 x
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 $ 990 s
Totalsè $ 66,750 $ 24,795 $ 24,675 $ 66,870 ←Totals
No change in FG/WIP inventory Contribution format
GAAP Managerial Accounting Variable
Sales $ 99,000 Sales $ 99,000
Cost of Goods Sold $ 66,750 Variable Costs & Expenses
Gross Margin [Gross Profit] $ 32,250 Cost of Goods Sold $ 61,425 x $ 55,800 $ 61,425 $ 61,425
Variable Selling Expenses $ 4,455 $ 1,575 $ 5,445
Selling & Administrative Expense Variable Admin. Expenses $ 990 $ 2,650 $ 66,870
Selling Expense $ 15,355 Total Var. Costs & Expenses $ 66,870 $ - 0
Administrative Expense $ 9,440 Contribution Margin $ 32,130 32.5% $ - 0
Total Sales & Administrative Expense $ 24,795 $ 800
Fixed Expenses $ - 0
Net Operating Income $ 7,455 Manufacturing $ 5,325 $ 400
Selling Expense $ 10,900 $ 200
Administrative Expense $ 8,450
Total Sales & Admin. Expense $ 24,675
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-Lo Method for Y = a + bX Excel 3
Compute "b" first then compute "a"
A B C Modeled Y
A6 Actual = X Actual = Y using hi - low Excel Min = low
A7 Units $ Excel Max = hi Divide ∆ $s by
A8 2310 $ 10,113 $ 10,568
A9 2453 $ 12,691 $ 11,085 Difference:Min [Lo] - Max {Hi]
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 5432 $ 21,270 $ 21,860 $ 3.617 = b the variable cost per unit
A15 5268 $ 19,930 $ 21,267
A16 4628 $ 21,860 $ 18,952 Formula Hi Low
A17 3720 $ 18,383 $ 15,668 Y = 3.617X + 2212.598 Spent $ 21,860 $ 9,830
A18 2106 $ 9,830 $ 9,830 bX $ 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 = a
Max 5432 $ 21,860 Low $ 19,647.54 $ 2,212.46 fixed = a
Regression Regression Method for Y = a + bX Excel 4
The RSQ(array1, array2) function returns the Square of the Pearson Product-Moment Correlation Coefficient between two arrays of data.
A B C D E F
A6 Actual Actual Modeled Modeled $
A7 Units $ $ % accuracy Difference
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% 92 0.916762505 RSQ 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 = b Excel "+ slope" for computations by "hand" see cell A65
A14 5432 $ 21,270 $ 22,604 6.3% 1,334 $ 2,296 Intercept = a 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 std deviation
A19 2495 $ 11,081 $ 11,624 4.9% 543 4799 Y
X Y sum►► (0) 4595 Model 0.9574771564 Pearson =+G41/G40
3527.3 $ 15,483 $ 15,483 0.9% 1229 X 0.916762505 P2 = RSQ =+I41^2
mean regression error
Ch.2 ACC220//HCT Hi-Low Hi-Low Regression Regression Excel 5
Actual Actual Modeled % Modeled % Net Error
Units $ $s Error $s Error Hi-Low Regression
2,310 $ 10,113 $ 10,568 4.5% $ 10,932 8.1% 4.5% 8.1%
2,453 $ 12,691 $ 11,085 12.7% $ 11,467 9.6% -12.7% -9.6%
2,641 $ 10,905 $ 11,765 7.9% $ 12,170 11.6% 7.9% 11.6%
2,874 $ 12,949 $ 12,608 2.6% $ 13,041 0.7% -2.6% 0.7%
3,540 $ 15,334 $ 15,017 2.1% $ 15,531 1.3% -2.1% 1.3%
4,861 $ 21,455 $ 19,795 7.7% $ 20,469 4.6% -7.7% -4.6%
5,432 $ 21,270 $ 21,860 2.8% $ 22,604 6.3% 2.8% 6.3%
5,268 $ 19,930 $ 21,267 6.7% $ 21,991 10.3% 6.7% 10.3%
4,628 $ 21,860 $ 18,952 13.3% $ 19,598 10.3% -13.3% -10.3%
3,720 $ 18,383 $ 15,668 14.8% $ 16,204 11.9% -14.8% -11.9%
2,106 $ 9,830 $ 9,830 0.0% $ 10,170 3.5% 0.0% 3.5%
2,495 $ 11,081 $ 11,237 1.4% $ 11,624 4.9% 1.4% 4.9%
6.4% 6.9% -2.5% 0.9%
Average Average average error
absolute 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) 160 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 = mean of X _ Y = mean of 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) 4058003 785586.777777778
2874 $ 12,949 (8.77) -653.3 $ (2,534) 1655819 426844.444444445
3540 $ 15,334 (3.58) 12.7 $ (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 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

Pearson product moment 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

The variability of Y attributable to the variability of X

Parson done by "hand" using formula above

Ch.2 HW

Chapter 2 HW ACC220
Hi-Lo Method #1
A B C
Actual = X Actual = Y
Units $
B8 5500 $ 21,000 Data
B9 5600 $ 21,200 Data
B10 5800 $ 21,600 Data
B11 6400 $ 22,700 Data
B12 5400 $ 19,900 Data
B13 5900 $ 22,000 Data
a] Compute a, b, of Y=a + bx
b] Compute projected spending @ 5750 units
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 4.50% 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] #1 Difference Difference
Actual X Actusl $ Modeled Y $s %
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