Accounting with Excel

The assignment is the attach file. Please, read the attachment for complete directions.

 

DeTazz

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