Case Study Questions & Excel About Colombo Yogurt

Task summary:Answer case study questions about the Colombo Frozen Yogurt Background  and complete Colombo Yogurt Profit-Loss Statement Excel. There are 6 questions, 1,2,4,5, and 6  to be answered in a doc file, 600 words total using the attached case. For each thread, students must support their assertions with at least 2 scholarly citations.  Each reply must incorporate a least 1 scholarly citation.  Any sources: cited must have been published within the last five years. Question no 3 is to be answered directly inside the attached xls.

Full order description:Dear Freelancer, please answer the case study questions and complete the  Colombo Yogurt Profit-Loss Statement Excel

Assignment1. Briefiy summarize Colombo’s competitive environment)

2. Describe General Mills’ strategy in response to the compétitive environment

3. Finish filling out the numbers on the spreadsheet provided in the case study)using ABC techniques. Discuss some strategies you are using to complete the analysis (Just a few sentences inside the doc file also).

4. Using the ABC analysis you completed on the worksheet answer the followingquestions.- Wnich segment has the most sales revenue?- which segment has the largest net income?

5. What does the ABC analysis reveal? Be sure and look at what drives the costand be specific.

6. How does the answer to question one above compare with your thoughtsabout revenue and net income prior to using ABC analysis.

PROFIT AND LOSS BY SEGMENT – PRE-ABC
Colombo Soft-Serve Frozen Yogart
Income Statement
Category
Line
Impulse Segment
Yogurt Shops
Total
#
Sales in Cases
1,500,000
I
Sales Revenue
$ 29,850,000
2
3
Less: Price Promotions
Net Sales (Line I – 2)
$
Less: Cost of Goods Sold
3,600,000
20,280,000
13,800,000
$
4
4,500,000
$ 25,350,000
$ 17,250,000
900,000
5,070,000
3,450,000
5
Gross Margin = Net Sales – COGS (Line 3 -4)
$
8,100,000
$
6,480,000
$
1,620,000
6
Less: Merchandising Expenses
7
8
Less: Selling General & Admin. Expenses
Net Income (Line 7 – 8 – 9)
$
1,725,000
1,185,000
5,190,000
$
1,380,000
948,000
4,152,000
$
345,000
237,000
1,038,000
$
1,200,000
300,000
23,880,000
$ 5,970,000
ACTIVITY BASED COSTING ANALYSIS
Colombo Soft-Serve Frozen Yogart
Income Statement
Category
Impulse Segment
Line
# Sales in Cases
Yogart Shops
1,500 ,000
1,200,000
300,000
Sales Revenue
$ 29,850,000
$ 23,880,000
$ 5,970,000
2
Less: Price Promotions
3
Net Sales (Line 1 – 2)
4,500,000
$ 25,350,000
3,600,000
$ 20,280,000
900,000
$ 5,070,000
4
5
COGS (ingred. , pkg. & storing)
COGS (pick pack & shipping) Pallets
$ 14,250,000
300,000
$
$
6
COGS (pick pack & shipping) Ind. Cases
7
Total Cost of Goods Sold (Line 4 + 5 + 6)
Gross Margin = Net Sales – COGS (Line 3 -7)
Less: Merchandising Expenses
2,700,000
$ 17,250,000
8,100,000
1,725,000
3,900,000
2,475,000
9
10 Less: Selling General & Admin. Expenses
11 Net Income (Line 8 – 9 – 10)
Cost Per
Impulse
Segment
Yogart
Shops
Total
Driver
Driver
Quantity
Quantity
Quantity
Cases
Pallets
$9.50 per case
$75 per pallet
Total
I
8
Cost
1,200,000
800
300,000
3,200
1,500,000
4,000
Ind. Cases $2.25 per ind. case
1,140,000
60,000
1,200,000
Kits
Time
3360
99%
90
1%
3450
100%
$500 per kit
% of time
5- 2 Colombo Soft-Serve Frozen Yogurt
In 1994, General Mills Incorporated, a S6 billion consumer goods company, acquired Colombo Froren Yogurt,
Mills Inc. (GM’) bclicved they could add Colombo frozen yogurt their existing product lineup to increase
net 5alC5 y,’ith little addition mdrketing cost.
Frown yogurt is sold through iwo distinct segments — independent shops and impulse locations such as
cafeterias,
buffets. Frozen yogurt is the ‘Daio business the shops whereas yogurt ig incremental to the
impulse locations’ main business. GMI’s large sales force already served the impulse market.
financial results in The first couple ofyears were mixed. Earnings increased slightly and then dropped each
year even though sales volume
relatively nxt,
merchandising easts dropped, while prieing promotion rates
escalated_
G Ml fot•cc focused on the impulse segments und pricing
were believed to be
driving volume increases. [lowever, volume in the shop segment declined of alarming rates and there was
widespread dissatisfaction in The sates organization. While G Ml knew gilts by segment, they didn’t track costs
by segment. Instead costs Were allocated based sales dolla1S The situatic”l was ripe for a clearer look using ABC
methods.
TODAY’S FROZEN YOGURT MARKET STRUCTURE:
When Colombo Yogurt Company began murketing son-serve frozen yogurt in early 1980’s. their main
distribution was through indenendent yogurt shops. the early 90’s, they faced competition from liranchise
operations such as TCHY Freshens [hatreplaced many of the independent yogurt shopsv And ‘lit market changed
as Foodservice operators such as cafeterias, Colleges. and bu frets started to add
ycjgurt to their
business. By the late 90 these Impulse locations accounted for 2/3 or thc soft.scryc
In the late BWs, Shop sales began to increase with the addition af distinctive new products such as SinooLhies,
boosters, and gran itase The Shops make their living from the soft-serve business and must innovate Or PO out Of
business (as lhousands have done in the last decade). On the other hand, the Irapulsc locations
living from
and the soft-serve
is only performance topspin, These firms arc unwilling to
any risk (new
equipment or extra labor) to serve highly differenliatcd products like smoothies or granitas.
THE GM I-COLOMBO MARKETING PLAN:
The GMI Foodservice Division markets brands such as Cheerios, Yopl ait. Betty Crocker€ Gold Medal Flour,
Hamburger Helper. Pop-secret. and Chcx Snack to Food
Firms. Hospitals, and schools. Colombo
yogurt was added this product lineup and the Foodservice sales fotce covered both Shop ind Impulse locations,
Salesforce.• Colombo’s salesforcc was merged into the Foodservice salesfcree Customers were reassigned to
salespeople who already serviced that geographical drca. The salespeople varied in their reaction to the produer.
Some found shops easy to sell to while others avoided thc shops despite the possible lost commission. Many
spent ofti’ne helping (heir impulse
understand
to use the machinery.
Merchandising Promoriares: Colombo traditionally charged the Shops for merchandising (hat •was
scale
and eye popping (neon signs). The Slmps used these signs to draw eustomers inside. GMI
not to charge
for merchandis ing and to provide same
scale merchandising 10 both Shops ard
locations. Shops were
very interested in thc kits while many Impulse locations
even
Pricing Promuri011N’ Pricing promulions are a mainstay of GMl’s impulse loeatiön approach. GM l’ s sa lesfurce
generally used these promotion events as an opponuaity 10 Visit their and take advantage 01 the occasion to
meet Scrvicc needs and sell other products that may be featured.
GM’ made price promotions available to segments ofthe market, While the deals were typieålly around per
they averaged S3
case againsT
shipped during year, OMI marketing knew price was not major
decision factor for Shops and they did not target pricing promotions to them. However, Shops werc aware of the
and took advantage of them_
THE BUSINESS STATUS – PRE-ABC:
PROFIT AND LOSS BY SEGMENT – PRE-ABC
Category
Impulse Segment
Yogurt Shops
Total
Sales in cases
Sales revenue
Less: Price Promotions
Net Sales
Less: Cast of Goads Sold
Cross Margin
Less: Merchandising
Less: SG&A
Net income
ABC ANALYSIS OF COST OF GOODS SOLD:
Cost of Goods Sold is made up of$ 14.25 0.000 for ingredients, packaging, and storagc and S3 for pickipack
and shipping. Since the product is the Same across Segments, the COSI ID produce should be the same.
However,
$’5 to pick and ship whereas individual orders cost $2.25 per case. There art 75 cases n a pallet und the sepnents
differ in their utilization of full pallets as shown below.
ment
Yo n Sho s
1m ulse
Cases in
Pallets
Individual cases
240.000
140,000
60.000
Tolal cases
ABC ANALYSIS OF MERCHANDISING:
Merchandising costs consist mainly Of kits
3,430 kits were sent out and 90 ofthem were sent to shops.
of where the kits were Sent indicated that
ABC ANALYSIS OF SELLING, GENERAL AND ADMINISTRATIVE:
Since sales representatives service several products, their costs are allocated to the various products based nn
gross sales dollars, GMI gave diaries to 10% oflhe sales force in randomly selected markets otthe coumry and
asked them to track their time in activity classifications for 60
The diaries indicated that sales reps
spent almost 3 times as much time on the yogurt than GM’ had estimated. The total allocation 10 Yogurt
jumped
w
Oftheirtime spent on Yogurt, only
ofthe time was spent on the shops,