cost accounting \ excel

Using Data Analytics to Analyze Data
DA3.2 Data analytics can be used to identify trends in costs over time.
Problem
Estimating production quantities and sales quantities are difficult tasks as a
company works to manage its inventory of both finished goods and raw
materials. If successful on those fronts, these companies still need to
estimate the costs for these resources each period.
Miguel, Inc. produces and sells adhesive phone wallets for
smartphones. The product is standardized with the only variation being the
color of which 16 shades are available. Because the wallets are adhesive,
customers typically buy a new wallet when they upgrade to a new
smartphone model. As such, the company expects to see an increase in sales
as users new phone models are released. In the past, the production
manage, Joe, has been efficient at balancing the quantity manufactured and
inventory levels. Joe is arguing that the company’s direct costs increase over
time. You have been asked by the CEO to determine if Joe is correct, and to
help estimate the manufacturing costs that support the phone wallet
production. Data containing the daily unit costs of direct materials and direct
labor used in producing the wallets for the first three months of the year are
shown here.
Unit Direct
Unit Direct
Date Material Cost Labor Cost
1/1 $
2.72 $
13.33
1/2
2.74
13.35
1/3
2.67
13.61
1/4
2.74
13.35
1/5
2.74
13.35
1/6
2.74
13.35
1/7
2.72
13.32
1/8
2.72
13.61
1/9
2.67
13.75
1/10
2.72
13.32
1/11
2.67
13.75
1/12
2.67
13.75
1/13
2.72
13.32
1/14
2.72
13.33
1/15
2.74
13.35
1/16
2.67
13.75
1/17
2.67
13.61
1/18
2.67
13.61
1/19
2.72
13.32
1/20
2.72
13.32
1/21
2.74
13.33
Units
Manufactured
3,733
3,548
3,960
3,615
3,608
3,561
3,743
3,863
4,003
3,756
4,092
4,051
3,818
3,685
3,577
4,090
3,947
3,914
3,748
3,777
3,622
1/22
1/23
1/24
1/25
1/26
1/27
1/28
1/29
1/30
1/31
2/1
2/2
2/3
2/4
2/5
2/6
2/7
2/8
2/9
2/10
2/11
2/12
2/13
2/14
2/15
2/16
2/17
2/18
2/19
2/20
2/21
2/22
2/23
2/24
2/25
2/26
2/27
2/28
3/1
3/2
3/3
3/4
3/5
3/6
3/7
3/8
3/9
2.67
2.67
2.72
2.74
2.72
2.67
2.72
2.72
2.74
2.72
2.67
2.72
2.67
2.67
2.74
2.67
2.67
2.72
2.72
2.67
2.67
2.74
2.74
2.72
2.67
2.74
2.67
2.72
2.74
2.72
2.74
2.72
2.72
2.67
2.74
2.67
2.72
2.72
2.72
2.67
2.67
2.74
2.72
2.72
2.72
2.72
2.74
13.61
13.75
13.32
13.35
13.32
13.61
13.33
13.32
13.35
13.61
13.75
13.33
13.75
13.61
13.33
13.61
13.75
13.32
13.61
13.75
13.75
13.35
13.35
13.32
13.75
13.35
13.75
13.32
13.35
13.33
13.35
13.32
13.32
13.75
13.35
13.61
13.61
13.61
13.32
13.61
13.61
13.35
13.32
13.61
13.32
13.33
13.35
3,934
4,066
3,787
3,597
3,842
3,937
3,693
3,746
3,596
3,874
4,021
3,709
4,061
3,947
3,670
3,910
4,045
3,832
3,889
4,045
4,042
3,539
3,531
3,788
4,082
3,608
4,028
3,800
3,571
3,739
3,531
3,815
3,780
4,064
3,581
3,945
3,878
3,896
3,804
3,964
3,949
3,555
3,812
3,868
3,741
3,693
3,529
3/10
3/11
3/12
3/13
3/14
3/15
3/16
3/17
3/18
3/19
3/20
3/21
3/22
3/23
3/24
3/25
3/26
3/27
3/28
3/29
3/30
3/31
2.72
2.74
2.67
2.72
2.72
2.67
2.67
2.72
2.72
2.74
2.74
2.67
2.74
2.74
2.74
2.74
2.74
2.67
2.67
2.67
2.67
2.72
13.32
13.35
13.61
13.33
13.32
13.75
13.75
13.32
13.33
13.35
13.33
13.75
13.35
13.33
13.35
13.35
13.33
13.75
13.75
13.75
13.75
13.32
3,753
3,584
3,905
3,675
3,819
4,058
4,052
3,759
3,691
3,613
3,645
4,032
3,581
3,666
3,548
3,556
3,665
4,067
4,021
4,046
3,995
3,811
Instructions
There are three parts to this problem. Use Excel to perform the
following.
a.
Create a pivot chart for direct materials with the number of units
manufactured as the row data, each respective unit direct material
cost in the columns, and the count of units manufacturing as the
values.
b.
Create a second pivot chart for direct labor with the number of
units manufactured as the row data, each respective unit direct
labor cost in the columns, and the count of units manufacturing as
the values.
c.
Address Joe’s assumption about how he thinks cost behave. What
do you observe when examining the data in the two pivot charts?
You expect that the production level will be around 3,820 units
every day during the second quarter of the year. What unit cost do
you expect to be incurred for each of the prime costs at this
volume?
a. Pivot chart of direct materials
c. Response to part c
Unit direct materials cost at 3,820 units
Unit direct labor cost at 3,820 units
b. Pivot chart of direct labor
Using Data Analytics to Analyze Data
DA3.3 Data analytics can be used to analyze cost behavior and estimate future costs.
Problem
Manufacturing companies incur significant costs in the production of their products. They recognize that some of these
costs vary directly with the volume of units produced, while other costs remain fixed regardless of volume. Estimating
their manufacturing costs for future periods requires a strong understanding of these relationships. The management
team at Miguel, Inc. has asked you to assist in evaluating costs of its adhesive phone wallets for smartphones. Data
containing the daily unit production costs incurred in producing the wallets for the first three months of the year are
shown here.
Daily Production Data for the Phone Wallets
Date
Units
Sold
Sales
Revenue
Unit Direct
Material Cost
1/1
1/2
1/3
1/4
1/5
1/6
1/7
1/8
1/9
1/10
1/11
1/12
1/13
1/14
1/15
1/16
1/17
1/18
1/19
1/20
1/21
1/22
1/23
1/24
1/25
1/26
1/27
1/28
1/29
1/30
3,036 $ 133,584 $
3,157
138,908
4,080
179,520
3,406
149,864
4,078
179,432
4,252
187,088
4,377
192,588
4,640
204,160
3,728
164,032
4,962
218,328
4,675
205,700
3,594
158,136
4,689
206,316
3,053
134,332
4,066
178,904
4,432
195,008
3,310
145,640
4,212
185,328
4,148
182,512
3,346
147,224
4,798
211,112
4,588
201,872
3,586
157,784
4,049
178,156
3,809
167,596
3,815
167,860
4,529
199,276
3,871
170,324
3,991
175,604
3,851
169,444
Unit Direct
Labor Cost
2.72 $
2.74
2.67
2.74
2.74
2.74
2.72
2.72
2.67
2.72
2.67
2.67
2.72
2.72
2.74
2.67
2.67
2.67
2.72
2.72
2.74
2.67
2.67
2.72
2.74
2.72
2.67
2.72
2.72
2.74
Unit Variable
Overhead
13.33 $
13.35
13.61
13.35
13.35
13.35
13.32
13.61
13.75
13.32
13.75
13.75
13.32
13.33
13.35
13.75
13.61
13.61
13.32
13.32
13.33
13.61
13.75
13.32
13.35
13.32
13.61
13.33
13.32
13.35
Assigned Unit
Fixed
Units
Overhead
Manufactured
4.55 $
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
3,733
3,548
3,960
3,615
3,608
3,561
3,743
3,863
4,003
3,756
4,092
4,051
3,818
3,685
3,577
4,090
3,947
3,914
3,748
3,777
3,622
3,934
4,066
3,787
3,597
3,842
3,937
3,693
3,746
3,596
1/31
2/1
2/2
2/3
2/4
2/5
2/6
2/7
2/8
2/9
2/10
2/11
2/12
2/13
2/14
2/15
2/16
2/17
2/18
2/19
2/20
2/21
2/22
2/23
2/24
2/25
2/26
2/27
2/28
3/1
3/2
3/3
3/4
3/5
3/6
3/7
3/8
3/9
3/10
3/11
3/12
3/13
3/14
3/15
3/16
3/17
3/18
4,134
4,480
4,019
4,860
4,029
3,786
3,325
4,844
4,290
3,561
3,509
3,017
3,288
3,735
3,595
4,269
3,678
3,192
3,715
3,960
3,693
3,091
3,059
4,133
3,745
3,528
3,723
3,024
3,319
4,101
3,062
4,037
4,027
3,108
3,745
4,034
3,562
3,921
3,629
3,860
3,734
3,354
3,823
3,704
3,659
3,417
4,021
181,896
197,120
176,836
213,840
177,276
166,584
146,300
213,136
188,760
156,684
154,396
132,748
144,672
164,340
158,180
187,836
161,832
140,448
163,460
174,240
162,492
136,004
134,596
181,852
164,780
155,232
163,812
133,056
146,036
180,444
134,728
177,628
177,188
136,752
164,780
177,496
156,728
172,524
159,676
169,840
164,296
147,576
168,212
162,976
160,996
150,348
176,924
2.72
2.67
2.72
2.67
2.67
2.74
2.67
2.67
2.72
2.72
2.67
2.67
2.74
2.74
2.72
2.67
2.74
2.67
2.72
2.74
2.72
2.74
2.72
2.72
2.67
2.74
2.67
2.72
2.72
2.72
2.67
2.67
2.74
2.72
2.72
2.72
2.72
2.74
2.72
2.74
2.67
2.72
2.72
2.67
2.67
2.72
2.72
13.61
13.75
13.33
13.75
13.61
13.33
13.61
13.75
13.32
13.61
13.75
13.75
13.35
13.35
13.32
13.75
13.35
13.75
13.32
13.35
13.33
13.35
13.32
13.32
13.75
13.35
13.61
13.61
13.61
13.32
13.61
13.61
13.35
13.32
13.61
13.32
13.33
13.35
13.32
13.35
13.61
13.33
13.32
13.75
13.75
13.32
13.33
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
3,874
4,021
3,709
4,061
3,947
3,670
3,910
4,045
3,832
3,889
4,045
4,042
3,539
3,531
3,788
4,082
3,608
4,028
3,800
3,571
3,739
3,531
3,815
3,780
4,064
3,581
3,945
3,878
3,896
3,804
3,964
3,949
3,555
3,812
3,868
3,741
3,693
3,529
3,753
3,584
3,905
3,675
3,819
4,058
4,052
3,759
3,691
3/19
3/20
3/21
3/22
3/23
3/24
3/25
3/26
3/27
3/28
3/29
3/30
3/31
3,052
3,988
3,129
3,964
3,552
3,890
3,921
3,860
3,249
3,921
3,545
3,345
4,156
134,288
175,472
137,676
174,416
156,288
171,160
172,524
169,840
142,956
172,524
155,980
147,180
182,864
2.74
2.74
2.67
2.74
2.74
2.74
2.74
2.74
2.67
2.67
2.67
2.67
2.72
13.35
13.33
13.75
13.35
13.33
13.35
13.35
13.33
13.75
13.75
13.75
13.75
13.32
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
4.55
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
11.45
3,613
3,645
4,032
3,581
3,666
3,548
3,556
3,665
4,067
4,021
4,046
3,995
3,811
Instructions
There are three parts to this problem. Use Excel to perform the following,
a.
Use the high-low method to determine the equation of the manufacturing cost line for Miguel, Inc. for the
three-month period.
b. Create a simple regression model in Excel based on the date provided to determine the equation of the
manufacturing cost line for Miguel, Inc. for the three-month period.
c.
Estimate daily production costs for April 1 using both the high-low method and the regression model. Actual
manufacturing costs incurred on April 1 are provided here.
Units manufactured
3,897
Total cost
$ 296,512
Compare the two estimated costs and the actual cost. Identify which is the most reliable approach to
estimating costs and explain why.
ecognize that some of these
dless of volume. Estimating
onships. The management
s for smartphones. Data
ee months of the year are
Student Work Area
Total Cost
$ 289,212
280,704
300,593
282,915
287,314
281,636
292,432
300,038
300,779
293,692
308,797
297,546
296,185
291,502
284,901
297,612
295,018
296,815
290,137
295,401
282,003
299,126
298,460
294,542
282,694
296,707
291,879
287,969
291,713
279,979
a. High-low method cost line equation for part a
Units
Cost
High data point
Low data point
Unit variable cost
Total fixed costs
Cost line
b. Regression method cost line equation for part b
Cost line
299,450
306,828
291,042
302,830
299,012
285,829
300,694
305,130
292,336
295,483
295,906
302,913
278,535
280,345
289,626
306,943
282,665
299,294
288,157
284,647
291,202
284,158
288,127
288,226
296,377
283,952
295,676
289,818
293,857
291,617
293,493
299,231
281,819
290,937
294,159
288,179
283,728
284,800
289,027
286,877
301,986
287,161
296,813
300,985
300,173
293,571
293,982
c. Cost comparison for part c
Estimated cost using high-low method
Estimated cost using the regression model
Actual cost
289,392
292,121
305,224
286,053
284,926
280,295
280,813
290,140
308,535
305,049
299,777
296,797
296,924
e for Miguel, Inc. for the
e the equation of the
e regression model. Actual
eliable approach to