Accounting with Excel

Twelve months ago, David opened a coffee shop, The Daily Grind, in Mercy Hospital's former gift shop. David was confident that he had the knowledge ro -"k.a success of this new business. He produced a quality product that people needed, had priced the product to be very competitive, and had a gteat location in a high-traffic area of the hospital.

the first year of operations. Fringe benefits for David, including health insurance and payroll taxes, accounted for an additional $10,000 of cosrs for th. company. Part-time employees work an average of 24 hours each week and are paid $9 per hour. payroll taxes and other costs average about $1.00 pu, Lorr for part_ time employees. As shown in the following table, part_ time employees worked from 656 ho.r., to 727 hours each month:Material Gosts

t3-n. o"il, *i;; ;;;;;; ;;";;-l A coffee beans costing $B per pound. Each pound

I

of coffee beans produces 256 ounces of coffee. boff"" I is soid in three sizes: a small cup holding g ourl...,

" j

medium cup holding 12 ounces, and a large .,rp hoid_ l ing 16 ounces.

I

The cups needed to serve the coffee cost $.0-5 for I the small cup, $.06 for the medium cup, and g.07 j for the large cup. Lids cost $.03 per cup-and are the i same regardless of cup size. Sleeves cost an additional I $.04 per cup. On average, sugar and cream cost $.02 oer I cup for small cups, $.03 for medium cups, and $.0+ fo. j large cups. j

I

_l lahor Gosts

Month

January

February

March

April

May

June

July

August

September

October ,

November

December

Part-time Employee Labor Hours

l22hours

656 hours

727 hours

705 hours -/27

hours

705 hours

727 hours

727 hours

705 hours

721 hours

705 hours

lll nours

three employees during the morning shift (7:00_11:00), two empioyees from 11:00 until 3:00, and rhree em_ ployees from 3:00 to 7:00. Labor is a fixed cosr, be_ cause the employees are paid regardless of whether coffee is sold. David worked 60 hours each week,

he Daily Grind is open 12 hours each d,ay,7 days a week (365 days per year), and is staffed with

on average, and was paid a saiary of $30,000 during

rThis comprehensive case incrudes topics and concepts covered in chapters i through 6 of

the book, induding pr0duct c0sting, (ost behavior, and cost-volume_profit analysis. The case also indudes a writ;ng requirement. ldeally, it would be assigned after completion of Chapter 6.

F'\ B Eu1,"g the hrsr year of operations, the hospitale F charged rent of $2,000 per month. As part of

the rental cosr, the hospital provided furnirure and fix_ tures for the shop, as well as nightly cleaning services. David leased a drip coffeemaker, refrigerator, coffee grinder, scale, and cash register for $150 per month, totai. David paid directly for his utilities (electricity and water). The costs of electricity include the costs of heat_ ing and cooling the shop. as well as rhe cosr of running the electric appliances (refrigerator, coffeemaker, etc.I

0uerhead Gosts

" -t j

:

,!,'i.il-"- i1:l

! I I

I

I I

l

I l I

I I

I l

I

i

I

I

. rd,v,I r!o. o,ovf,.zlb 0tat12

Appendix B: The Daity Grind 309

For the first 12 months of operations, utiliry expenses were as follows:

*eq:iirarnerts

3, Calculate the cosr of coffee beans per ounce of coffee sold.

P, Calculate the cost of cups, lids, sleeves, cream, and sugar per unit for small, medium, and large cups ofcoffee and in total.

3. Calculate the total labor costs for the year. 4, P.epare an income statement for The Daily

Grind for the last year. You can assume that there are no inventories on hand at the end of the year. (AIl coffee and supplies purchased during the year are consumed.)

5, Determine whether the costs incurred by The Daily Grind are fixed, variable (with respect to number of cups of coffee sold), or mixed.

S" Use regression analysis and the highilow method to calculate the monthly fixed cost and the variable componenr of the utility expenses incurred by The Daily Grind. Use cups of coffee sold as the independent variable and utility expense as the dependent variable in your regression analysis. After calculating both numbers, round your final answers to two decimal places.

7" Compare the regression resulrs with the highlow results. Which model would you suggest?

8. Calculate the contribution margin earned for each product (round to three decimal places) and the weighted-average contribution margin (round to four decimal places).

S, Assume the sales mix given in the problem. 'What

is Daily Grindt break-even point in terms of the number of cups of coffee sold during the year?

33. David is contemplating adding a new 2O-ounce product for the coming year and discontinuing sales of the small B-ounce cup. The new crrp, lid, and sleeve cost the same as the 15-ounce cup, but cream and sugar is expected to cosr $.0- pe, cup instead of $.04 per cup. The new extralarge cup would be priced at $2.40. David anticipates that the new sales mix would be S0% for the 12-ounce cup,30%" for the 15-ounce cup, and 20"/o for the new 20-ounce cup. Assume that material, labor, and overhead costs remain the

.,Minjhr:,,,

January ,

February

March .'

April,

May; : ,:.

JgJlg' ;'.: ': ,r july '

August ,: September:. .. October

Novernberl;. ,

Decembetr::

Total .. ":': :

Utility Expense

$qz $s1 o

- $s24

$+ao

$440

$460

$4s2

$430

$s3s

$s70

$s80

$60q

$6033

1:.::at:,::8;-80O

: .r.ri ri 8.60O r.-. :i.l--.. ::

,.ii.,.; l!yQ00

'. 'r'l,.dt;620. . .,-,,'::12,000.,:.

i,,,'r:'. -12t066.,

$elling and Administratiue Gosts

vertising materials during the year. He also paid $1,000 for liability insurance.

Sales Beuenue' During the first year of operations, David set the shop's prices to be slightly lower than their competitors'. The Daily Grind sells a small cup of coffee for g1.25, a me_ dium cup for 91.65, and a large cup for $t.9S. Sales revenue was as follows:

,:Mon$1.:...':,::,,

lanuary' ,, '., .

kbruary: .,,-.:,' 'MarChl ',' ' ' Apri!, ,, . May : .,i -

.Jgng,', r ' ',' 'JU$," : .:':,:': 'r:....:, Augusi ' . . Sqfiembgi..,

O66fogr, ' ' :'.,,. Novelmber,, ' ,.

D€Gfriber. , ,,,,..: *Coffee

sales average 1 0% small cups (g ounces), 50% medium cups (l 2 ounces), and 40% large cups (l 6 ounces).

3l 0 Appendix B: The Doity Grind

ning Year' How would this

s"me in'the Tf:i* affect the company s break- change in sales

mt

even Point?

l'1 . David .-""* :*:l:iii'"",T ;iT :l,::il":l' ffi i:i1%:.ff[::;:'il'il'

s i s'ooo or rringe

benefits) *nlt "d"utitg ni' *iotttto"d to 40

hours

oer week *itn *lplil *ttttt or vacation dur-

ffi;;;;' n"a""'ine his workload will requtre

I i:

*

increasing the number of hours workedtry

part-

time employtt' rv i'oso hours per Yeal

Assume

the introductit" if tit" t"tra-larye cup and the

new sales *i* "' ai""sstd in requirement

10'

Vhat level "f "";; ';les *oolJ-b" required in

;;;; ;;' David to reach his goal?

t*' trVritt a short memo to David and discuss

whether yt" ti'i"iit will be able to reach his

;;;il;t the second Year of operanons'

APPendix B: The 311