vii
Using Excel in the Classroom
Excel
®
is essential in today’s business envi-
ronment, and Lanen,6e integrates Excel
where appropriate in the text. Several exer-
cises and problems in each chapter can be
solved using Excel spreadsheets tem-
plates.An Excel logo appears in the text
next to these problems. Additionally, com-
mencing with the sixth edition many of
these exercises are now algorithmically gen-
erated and assignable in Connect, with scor-
ing of select inputs for gradebook inclusion.
NEW! Excel Simulationsare auto-graded
in Connect and allow students to practice
their Excel skills, such as basic formulas
and formatting, within the context of
accounting in a simulated Excel environ-
ment. When enabled by the instructor, these
questions feature animated, narrated Help
and Show Me tutorials.
“Strong end of chapter and test bank materials. Strong
inclusion of Excel in the chapters”
—Michael Flores,
Wichita State University
Integrative Cases
Cases can generate classroom discussion
or be the basis for good team projects.
These integrative cases, which rely on
cost accounting principles from pr evious
chapters as well as the current chapter,
ask students to apply the different
techniques they have learned to a
realistic situation.
224 Part II Cost Analysis and Estimation
Overhead and administrative costs are not affected by producing this tool.
The current purchase price is $100,000 per unit, so the report recommends that Krylon con-
tinue to purchase the product from CO., Inc.
Required
Assume that Krylon could experience labor-cost improvements on the tool production consistent
with an 80 percent learning curve. Should Krylon produce or purchase its annual requirement of
eight tools? Explain your answer. (Note that the 80 percent learning rate coefficient is −0.3219.)
(CMA adapted)
INTEGRATIVE CASE
5-72. Cost Estimation, CVP Analysis, and Decision Making
Luke Corporation produces a variety of products, each within their own division. Last year, the
managers at Luke developed and began marketing a new chewing gum, Bubbs, to sell in vending
machines. The product, which sells for $5.25 per case, has not had the market success that manag-
ers expected, and the company is considering dropping Bubbs.
The product-line income statement for the past 12 months follows.
Revenue ........................... $14,682,150
Costs
Manufacturing costs............... $14,440,395
Allocated corporate costs (@5%) .... 734,108 15,174,503
Product-line margin ................. $ (492,353)
Allowance for tax (@20%) ............ 98,470
Product-line profit (loss).............. $ (393,883
)
All products at Luke receive an allocation of corporate overhead costs, which is computed as
5 percent of product revenue. The 5 percent rate is computed based on the most recent year’s
corporate cost as a percentage of revenue. Data on corporate costs and revenues for the past two
years follow.
Corporate Revenue Corporate Overhead Costs
Most recent year ..... $106,750,000 $ 5,337,500
Previous year . . . . . . . . $ 76,200,000 4,221,000
Roy O. Andre, the product manager for Bubbs, is concerned about whether the product will be
dropped by the company and has employed you as a financial consultant to help with some analy-
sis. In addition to the information given, Mr. Andre provides you with the following data on prod-
uct costs for Bubbs.
Month Cases Production Costs
1 ............. 207,000 $1,139,828
2 ............. 217,200 1,161,328
3 ............. 214,800 1,169,981
4 ............. 228,000 1,185,523
5 ............. 224,400 1,187,827
6 ............. 237,000 1,208,673
7 ............. 220,200 1,183,699
8 ............. 247,200 1,226,774
9 ............. 238,800 1,225,226
10 ............. 252,600 1,237,325
11 ............. 250,200 1,241,760
12 ............. 259,200 1,272,451
(LO 5-4, 5, 9)
Lan69479_ch05_176-225.indd 224 13/11/18 2:52 PM
Chapter 5 Cost Estimation 217
The controller’s office estimated overhead costs at $3,600 for fixed costs and $18 per unit for
variable costs. Your colleague, Lance, who graduated from a rival school, has already done the
analysis and reports the “correct” cost equation as follows:
Overhead = $10,600 + $16.05per unit
Lance also reports that the correlation coefficient for the regression is .82 and says, “With 82
percent of the variation in overhead explained by the equation, it certainly should be adopted as the
best basis for estimating costs.”
When asked for the data used to generate the regression, Lance produces the following:
Month Overhead Unit Production
.............. $, ,
.............. , ,
.............. , ,
.............. , ,
.............. , ,
.............. , ,
.............. , ,
.............. , ,
.............. , ,
.............. , ,
.............. , ,
.............. , ,
.............. , ,
The company controller is somewhat surprised that the cost estimates are so different. You have
therefore been assigned to check Lance’s equation. You accept the assignment with glee.
Required
Analyze Lance’s results and state your reasons for supporting or rejecting his cost equation.
5-62. Interpretation of Regression Results: Multiple Choice
The Business School at Eastern College is collecting data as a first step in the preparation of
next year’s budget. One cost that is being looked at closely is administrative staff as a function
of student credit hours. Data on administrative costs and credit hours for the most recent
13 months follow.
Month Administrative Costs Credit Hours
July .......... $ ,
August ....... ,
September.... , ,
October ...... , ,
November .... ,, ,
December .... , ,
January....... , ,
February...... ,, ,
March ........ , ,
April.......... , ,
May .......... ,, ,
June ......... ,
July .......... ,
Total ....... $,, ,
Average ...... $ ,
,
The controller’s office has analyzed the data and given you the results from the regression analysis,
as follows.
(LO 5-5)
Lan69479_ch05_176-225.indd 217 13/11/18 2:52 PM
216 Part II Cost Analysis and Estimation
Required
a. Draw a scattergraph relating overhead costs to the number of units.
b. Using the high-low method, estimate overhead costs for a month when output is expected to
be 120,000 units.
5-60. Interpretation of Regression Results: Simple Regression Using a Spreadsheet
Hartman Company’s Lucas plant manufactures thermostatic controls. Plant management has
experienced fluctuating monthly overhead costs and wants to estimate overhead costs accu-
rately to plan its operations and its financial needs. Interviews with plant personnel and studies
reported in trade publications suggest that overhead in this industry tends to vary with labor-
hours.
A member of the controller’s staff proposed that the behavior pattern of these overhead
costs be determined to improve cost estimation. Another staff member suggested that a good
starting place for determining cost behavior patterns is to analyze historical data. Following
this suggestion, monthly data were gathered on labor-hours and overhead costs for the past two
years. No major changes in operations occurred over this period of time. The data are shown in
the following table.
Month Labor-Hours Overhead Costs
1 251,563 $2,741,204
2 238,438 2,166,231
3 192,500 1,902,236
4 271,250 2,590,765
5 323,750 3,071,812
6 290,938 2,618,161
7 271,250 2,480,231
8 251,563 2,745,558
9 231,875 2,211,799
10 343,438 3,437,704
11 185,938 2,314,436
12 231,875 2,550,630
13 382,813 3,603,709
14 376,250 3,404,786
15 290,938 3,016,493
16 395,938 3,638,331
17 356,563 3,553,886
18 323,750 3,191,617
19 389,375 3,481,714
20 317,188 3,219,519
21 343,438 3,495,424
22 336,875 3,207,258
23 382,813 3,600,622
24 376,250 3,736,658
Required
a. Use the high-low estimation method to estimate the overhead cost behavior (fixed and vari-
able portions components of cost) for the Lucas plant.
b. Prepare a scattergraph showing the overhead costs plotted against the labor-hours.
c. Use a spreadsheet program to compute regression coefficients to describe the overhead cost
equation.
d. Use the results of your regression analysis to develop an estimate of overhead costs assuming
350,000 labor-hours will be worked next month.
5-61. Interpretation of Regression Results: Simple Regression
Your company is preparing an estimate of its production costs for the coming period. The control-
ler estimates that direct materials costs are $45 per unit and that direct labor costs are $21 per hour.
Estimating overhead, which is applied on the basis of direct labor costs, is difficult.
(LO 5-4, 5, 9)
(LO 5-4, 5)
Lan69479_ch05_176-225.indd 216 13/11/18 2:52 PM
Lan69479_fm_i-xxviii_1.indd 7 03/12/18 3:05 PM