USA: +1(669)289-8683
USA: +1(669)289-8683

Accounting 220-MEG

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

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.
Get My Paper

C

h 2

#1 E x

cel

b

le>

T

ype

s

of Costs & Expenses Excel 1 N

atural Cost or Expense Expense

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.
Get My Paper
by

the type of spending F

unctional cost or Expense Expense by function, department or organizational heirarchy Fixed

or

V

ariable

Cost or Expense Varies with sales or production volume or does not P

roduct or

Period

Cost or Expense Part of product cost [

Co

G S

/

I

nventoriable] or not D

irect 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 cost

s 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

operating

CoGS distribution Sales A

dmin Prod

uct

ion W

arehouse Total

s: Salary Expense 25,

0

00 1

7,000 36,000 9,000 87,000 Wage Expense 16,000

9,000

58,000 1

2,500 95,500 Product

ion Direct labor – 0

– 0

1

15,000

– 0 115,000
Fringe benefit expense 1

3,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 B

uilding 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 M

iscellaneous expense

1,400

1,100 3,950 2,

160 8,610 Total

85,100 48,375 3

92

,600 52,510 578,585 Sales Admin Production Warehouse
J

ob 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 R

aw 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

Excel 1

Product cost flows

Manufacturing company Raw materials WIP FG CoGS

set:

GAAP

Fixed

100,000

A

900

:

11

0

Sales Sales

Buy materials + 20,000

B

CoGS CoGS Available for use Raw materials = 120,000

C

.00

Sell.Exp

55,000

D

.00

Admin.Exp

65,000

E

.00

Sell.Exp Sell.Exp Sell.Exp

=4000+

sales

G

.00

Admin.Exp Admin.Exp Beginning WIP + 77,000

H

N/A N/A N/A

Issued to WIP from Raw material inventory + 65,000

I Advertising expense

.00

Sell.Exp Sell.Exp Direct labor + 21,000

J

each CoGS CoGS Applied overhead [Indirect costs] + 42,000

.00

Admin.Exp Admin.Exp

205,000

L

.00

Sell.Exp Sell.Exp Ending WIP Inventory – 64,000

M

.00

Admin.Exp Admin.Exp COG manufactured; [sent to FG] = 141,000

N

.00

Sell.Exp Sell.Exp

O

.00

Labor

Beginning FG + 214,000

P

.00

CoGS CoGS

To FG from WIP; CoG Manufactured + 141,000

.00

CoGS CoGS Mfg. overhead CoG Available for Sale = 355,000

R

.00

CoGS CoGS Mfg. overhead Ending FG inventory – 191,000

S

.00

Admin.Exp Admin.Exp

T

.00

CoGS CoGS Mfg. overhead CoGS = 164,000

CoGS CoGS Mfg. overhead

V

.00

CoGS CoGS Summary Beginning Additions Ending

W

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

$ 110.00

‘+’ ‘+’ ‘-‘ ‘=’

B Purchased Materials Cost each $ 62.00

$ 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

$ 4,000

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 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 s

GAAP

Variable

Sales $ 99,000 Sales $ 99,000

$ 66,750

Cost of Goods Sold

x $ 55,800 $ 61,425 $ 61,425

s

$ 4,455 $ 1,575

$ 990 $ 2,650 $ 66,870

Selling Expense

$ 66,870

Administrative Expense

$ – 0

$ 24,795 $ 800

$ – 0

Manufacturing

$ 400

Selling Expense

$ 200

Administrative Expense

$ 24,675

Net Operating Income $ 7,455 0
Income statements: Chapter 2 Excel 2
CoGS = Product cost //

GAAP
Month: July Var. Income Statement Comment
Data Manufacturing business Variable Beginning Raw materials +
Sold units sell price

each $ 0.0 Materials
Purchased Materials Cost each $ 62.00
sales rent per month $ 3,000 Sell.Exp Ending Raw materials –
Insurance costs month: $ 1,200 Admin.Exp RW used for Production =
Sales salaries exp: $ 4,000 Commission on sales 4.50

 % Y 4.5% X
Admin salary expense $ 2,800
Purchase new servers $ 11,000.00 N/A Capital expenditure
$ 950
Product brochures attached to each sold item $ 1.75
K Communication expense $ 2,450 Total Manufacturing costs;WIP for Period =
Depreciation expense: Sales Office $ 1,900
Depreciation expense: general & admin $ 1,800
Shipping Warehousing rent $ 1,050
Factory direct labor $ 2,650 Labor Direct Labor
Component warehouse $ 1,175 Mfg. overhead
Q Factory Mamagement $ 2,290
Fringe benefits for direct labor $ 800
Fringe benefits for Admin. $ 200
Process utilitiy cost $ 400
U Solder & solvents $ 200.00
Derperciation factory Bldg. & eqpmnt. $ 1,860
Variable Administrative expenses 1% of sales

$s
sell price each: Revenue $ 99,000
$ 55,800
+ Commission on sales 4.50% $ 8,455 $ 4,455
$ 1,575
$ 990
Totalsè $ 66,750 $ 24,795 $ 24,675 $ 66,870 ←Totals
No change in FG/WIP inventory Contribution format
Managerial Accounting
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
$ 15,355 Total Var. Costs & Expenses $ – 0
$ 9,440 Contribution Margin $ 32,130 32.5%
Total Sales & Administrative Expense
Fixed Expenses
Net Operating Income $ 7,455 $ 5,325
$ 10,900
$ 8,450
Total Sales & Admin. Expense
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

-Lo Method

for

+

A B C

ed

Y

= X

= low

$

= hi

:Min [Lo] – Max {Hi]

2

$ 21,860

Hi

$ 21,860

$ 9,830 $ 9,830 bX

Min 2106 $ 9,830 Hi

= a

Max

$ 21,860 Low

fixed = a

Regression

+ bX

(array1, array2) function returns the Square of the

Product-Moment Correlation Coefficient between two arrays of data.

A B C D E F
A6 Actual Actual

Modeled $

A7 Units $ $

Difference

A8 2310 $ 10,113

A9 2453 $ 12,691

A10 2641 $ 10,905

A11 2874 $ 12,949

92

RSQ

A12 3540 $ 15,334

A13 4861 $ 21,455

= b

“

A14 5432 $ 21,270

= a

A15 5268 $ 19,930

A16 4628 $ 21,860

A17 3720 $ 18,383

A18 2106 $ 9,830

std deviation

A19 2495 $ 11,081

543

Y

X Y

Model

Pearson

$ 15,483

X 0.916762505

//HCT

Hi-Low Regression Regression

Actual Actual Modeled % Modeled %

Units $ $s Error $s Error Hi-Low 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

RSQ X Y Prod

by

160

RSQ

by

Hand

= mean of X

= mean of Y

X Y

2310 $ 10,113

2453 $ 12,691

2641 $ 10,905

3

2874 $ 12,949

3540 $ 15,334

12.7

4861 $ 21,455

5432 $ 21,270

5268 $ 19,930

4628 $ 21,860

3720 $ 18,383

2106 $ 9,830

2495 $ 11,081

X Y

0.0

3527.3 $ 15,483

mean 3.7385 slope
_
Y $ 15,483
_
X 3527.3
Slope 3.7385
Intercept

Chapter 2

Regression
Hi Y

= a bX Excel 3
Compute “b” first then compute “a”
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
$ 7,617.40 $ 2,212.60 fixed
5432 $ 19,647.54 $ 2,212.46
Regression Method for Y

 = a Excel 4
The

RSQ Pearson
Modeled
% accuracy
$ 10,932 8.1% 819
$ 11,467 –

9.6% (1,224)
$ 12,170 11.6% 1,265 Can use excel wizard for help with formula
$ 13,041 0.7% 0.916762505 RSQ = Parson squared, Excel = “+ RSQ”
$ 15,531 1.3% 197
$ 20,469 –

4.6% (986) 3.7385 Slope Excel “+

slope for computations by “hand” see cell A65
$ 22,604 6.3% 1,334 $ 2,296 Intercept Excel “+ intercept” for computations by “hand” see cell A91
$ 21,991 10.3% 2,061
$ 19,598 -10.3% (2,262) =+SLOPE(C29:C40,B29:B40)
$ 16,204 –

11.9% (2,179) =+INTERCEPT(C29:C40,B29:B40)
$ 10,170 3.5% 340 std deviation
$ 11,624 4.9% 4799
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
Net

Error
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
X2 Y2
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
1,032 4,402 4,544,761 1,065,712 19,381,273
Sum of products 62,132,433 16,619,515 253,373,707 Sums
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 – mean Y – mean X x Y ∆X2
-1217.3 $ (5,370) 6537587 1481900.44444444
(18.03) -1074.3 $ (2,792) 2999986 1154192.11111111
9.50 -886.3 $ (4,578) 40

 5800 785586.777777778
(8.77) -653.3 $ (2,534) 1655819 426844.444444445
(3.58) $ (149) -1893 160.4444444444
(4.63) 1333.7 $ 5,972 7964102 1778666.77777778
0.32 1904.7 $ 5,787 11021512 3627755.11111111
(8.17) 1740.7 $ 4,447 7740019 3029920.44444444
3.02 1100.7 $ 6,377 7018493 1211467.11111111
(3.83) 192.7 $ 2,900 558653 37120.4444444444
(5.30) -1421.3 $ (5,653) 8035390 2020188.44444444
(3.22) -1032.3 $ (4,402) 4544761 1065712.11111111
-0.0 62132433.3333333 16619514.6666667
$ (4)
Intercept By “Hand” Y = a + bX
after you have the slope then
intercept = [Mean of Y] – [mean of X]*[slope]
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

ACC220

#1

A B C

Actual = Y

Units $

Data

Data

5800

Data

Data

Data

Data

units

Product Period

4.50%

b

#1 Difference Difference

$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
Chapter 2 HW
Hi-Lo Method
Actual = X
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
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
Chart c]
Actual X Actusl $ Modeled Y

Still stressed with your coursework?
Get quality coursework help from an expert!
Order now