Question

Phionia’s Finicky Feline Gourmet Cat Dinners

NOTE: There is a spreadsheet template provided for this assignment. You have to use it in order to complete the assignment.

Phionia Phelps has developed a gourmet cat food. Not only is this food eagerly eaten by the most finicky felines, but it is specially formulated to prevent the many health problems of aging cats. Phionia has been making the food on her kitchen range and selling it at $250 per case only to close acquaintances who are also cat lovers. One of her wealthy acquaintances has now offered to invest in her business if Phionia will begin selling the product through her website. However, the investor wants Phionia to produce a budget for the first six months of operation.

Based on her experience to date, Phionia predicts the following sales in cases:

Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
4,300	4,600	5,000	5,500	6,100	6,800	7,000	7,200	7,400	7,500

Each case of Finicky Feline Gourmet Cat Dinner requires 5 pounds of prime lamb meat, 10 pounds of short-grain Chinese rice, 2 pounds of wild caught Alaskan salmon, and 1 pound of secret vitamins and supplements. Phionia plans to maintain end-of-month inventories equal to 10 percent of the next month’s projected sales, to meet expected sales growth. All the ingredients inventories are to be maintained at 5 percent of the production needs for the next month, but not to exceed 1,000 pounds of any one ingredient. January will begin with all inventories at the projected levels.

Phionia has the following price quotes good for the following year:

Item	$ per pound
Lamb	$15.00
Rice	1.20
Salmon	24.00
Vitamins	45.00

   The Production process requires direct labor at two skill levels: (1) ingredient preparation, $18 per hour; and (2) cooking and canning, $ 24 per hour. Two workers are willing to work part time if there is not enough demand for them to work full time. It takes one hour to process one batch. Because of preparation and cleanup time, only six batches can be produced per day. Each batch produces enough food to fill 100 cases. Manufacturing overhead is $ 6,000 fixed per month plus $ 15 per case.

1. Prepare the following budgets for the period January through June, by month and in total for the period, using the spreadsheet template provided:

• Sales budget in dollars
• Production budget in units
• Direct materials purchases budget in pounds
• Direct materials purchases budget in dollars

2. Comment on the viability of this business and the advisability of the investor making a $50,000 investment to get it started.

Assumptions lbs./case Cost/lb. Total S/hr Lamb Ingr. Prep Cook/Can Rice Salmon Vitamins Cost/8 hr. day JAN-JUN 50.00 Jan Feb May Jun July Dec 4300 4500 5000 Mar 5500 Apr 6100 6800 7 000 7200 37,600 7500 4300 4500 5000 5500 5100 5800 7 000 7200 37,600 Sales Budget Production Budget Nov Projected Sales in Cases Add: Desired Ending Inventory Amount Needed Less: Beginning inventory # Cases to Produce Ingredient Inventory (<5% or 1000lbs) luamb Rice Salmon Vitamins TOTAL Purchases in POUNDS Lamb Rice Salmon Vitamins TOTAL Purchases IN DOLLARS Lamb 0 0 0 0 0 0 $ 0 S0S0S0S0S0 0 $0 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Salmon Vitamins TOTAL Manufacturing Batches Required Days Required Direct Labor Cost Manufacturing overhead Fixed Variable TOTAL Sales Price Total cost per case Gross Margin $ per case Gross Margin % Gross Margin $$$ TOTAL SO $250 50.00 $250.00 1009 $1,075,000 Sol $250 $0.00 $250.00 100% $1,150,000 $0 $250 $0.00 $250.00 100% $1,250,000 Sol $250 50.00 $250.00 100% $1,375,000 s ol $250 $0.00 $250.00 1006 100% $1,525,000 sol $250 $0.00 $250.00 100% $1,700,000 sol $250 $0.00 $250.00 10096 $1,750,000 so 50.00 $250 $0.00 $250.00 10096 $1,800,000 $9,400,000.00

Answer 1

Assumption	lbs/case	Cost/lbs	Total ($)
Lamb	5	15	75
Rice	10	1.2	12
Salmon	2	24	48
Vitamins	1	45	45
(In dollars)
Sales Budget
	Jan	Feb	Mar	Apr	May	Jun
Sales units	5000	5500	6100	6800	7000	7200
Selling price/ Case	250	250	250	250	250	250
Sales	12,50,000	13,75,000	15,25,000	17,00,000	17,50,000	18,00,000
Production Budget
	Jan	Feb	Mar	Apr	May	Jun
Projected Sales in Cases	5000	5500	6100	6800	7000	7200
Add: Desired ending inventory	550	610	680	700	720	740
less: Beginning inventory	500	550	610	680	700	720
Cases to produce	5050	5560	6170	6820	7020	7220
Ingredient Inventory (<5% or 1000lbs)	Jan	Feb	Mar	Apr	May	Jun
Lamb	278	309	341	351	361	371
Rice	278	309	341	351	361	371
Salmon	278	309	341	351	361	371
Vitamins	278	309	341	351	361	371
Total
Purchases in Pounds
Lamb	25250	27800	30850	34100	35100	36100
Rice	50500	55600	61700	68200	70200	72200
Salmon	10100	11120	12340	13640	14040	14440
Vitamins	5050	5560	6170	6820	7020	7220
Total	90900	100080	111060	122760	126360	129960
Purchases in Dollars
Lamb	18,93,750	20,85,000	23,13,750	25,57,500	26,32,500	27,07,500
Rice	6,06,000	6,67,200	7,40,400	8,18,400	8,42,400	8,66,400
Salmon	4,84,800	5,33,760	5,92,320	6,54,720	6,73,920	6,93,120
Vitamins	2,27,250	2,50,200	2,77,650	3,06,900	3,15,900	3,24,900
Total	32,11,800	35,36,160	39,24,120	43,37,520	44,64,720	45,91,920
Manufacturing
Batches required	50.5	55.6	61.7	68.2	70.2	72.2
Days required	8.41	9.26	10.28	11.36	11.7	12.03
Hours daily required	6	6	6	6	6	6
Direct labor cost per hour	42	42	42	42	42	42
Direct labor cost	2119.32	2333.52	2590.56	2862.72	2948.4	3031.56
Manufacturing Overhead
Fixed	6,000	6,000	6,000	6,000	6,000	6,000
Variable	75,750	83,400	92,550	1,02,300	1,05,300	1,08,300
Total	81,750	89,400	98,550	1,08,300	1,11,300	1,14,300
Sales	12,50,000	13,75,000	15,25,000	17,00,000	17,50,000	18,00,000
Total cost	32,95,669	36,27,894	40,25,261	44,48,683	45,78,968	47,09,252
Gross margin	-20,45,669	-22,52,894	-25,00,261	-27,48,683	-28,28,968	-29,09,252
It is not advisable to investro to invest $ 50000 in the business.