Question

PLEASE INCLUDE EXCEL FORMULAS OR WORK

• Prepare (7) operating budgets for the year by quarter & budgeted Income Statement for the Candy business
• Sales Information:
  • Company advertises that it will match the competition (Internal factor)
  • Five year historical selling total units per year: 220k, 210k, 180k, 196k, & 200k
  • Surrounding shopping area includes discount stores (i.e. Target, Walmart) selling similar product for $15, $20 respectively (external factor)
  • Stock Market down 25% in last 3 months, oil per barrel at record low (external factor)
  • Prepare a budget with the following:
    • Sales in units Q1 – 20,000, Q2 – 40,000, Q3 – 30,000, Q4 – 10,000
    • Selling price per unit: $10
• Production information:
  • Company’s desired level of ending finished goods inventory is 15% of next quarters budgeted unit sales.
  • Q1 beginning inventory would be PY Q4 ending. Which means that when calculating Q4 PY ending you would look at CY Q1 sales units.
  • When calculating Q4 ending you would need to know next years Q1 sales units. It is estimated that Q1 sales units for the following year will be a 10% increase to current year Q1.
• Direct materials information:
  • 12 ounces (oz) of chocolate per unit
  • Chocolate costs $.10 per ounce (oz)
  • Company’s desired level of ending material inventory is 20% of next quarters budgeted direct material needs.
  • Q1 beginning & Q4 ending use the same method as used during the production budget.
• Direct labor information:
  • Fifteen minutes per unit (15 minutes/60 minutes = .25/hr per unit)
  • Employees are paid $8/hour
• Overhead information:
  • Production supervisor salary for the year is $40,000
  • Machinery depreciation for the year using straight line is $13,000
  • Factory supplies is $2.00 per DL hour
  • Maintenance & repairs is $4.00 per DL hour
  • Utilities is $2.50 per DL hour
• Selling, General & Administrative (SG&A) information:
  • Executive salary for the year is $50,000
  • Taxes & insurance for the year is $7,500
  • Delivery expense is $.30 per sold unit
  • Other administrative expenses is $.10 per unit sold
• Cost of Goods Manufactured information:
  • WIP, beginning $180,000 & ending $155,000
• Income Statement
  • Income tax 40% on net income before taxes (NIBT)

Answer 1

Sales Budget
	Q-1	Q-2	Q-3	Q-4	Total
Sales (units)	20000	40000	30000	10000	100000
Selling price p.u	$10	$10	$10	$10	$10
Total Sales	$2,00,000	$4,00,000	$3,00,000	$1,00,000	$10,00,000
Production Budget
	Q-1	Q-2	Q-3	Q-4	Total
Sales (units)	20000	40000	30000	10000	100000
+Closing Stock @ 15% of next Qtr. Sales	6000	4500	1500	3300	15300
-Opening Stock	-3000	-6000	-4500	-1500	-15000
Total units to be produced	23000	38500	27000	11800	100300
Raw Material Purchase Budget
	Q-1	Q-2	Q-3	Q-4	Total
Units to be produced	23000	38500	27000	11800	100300
Material per unit (oz)	12	12	12	12	12
Total material for production (oz)	276000	462000	324000	141600	1203600
+Closing stock of materials @20% of next qtr. Needs	92400	64800	28320	60720	246240
-Opening stock of materials	-55200	-92400	-64800	-28320	-240720
Total Material to be purchased (oz)	313200	434400	287520	174000	1209120
Material cost per oz	$0.1	$0.1	$0.1	$0.1	$0.1
Total Material cost	$31,320	$43,440	$28,752	$17,400	$1,20,912
Direct Labour Budget
	Q-1	Q-2	Q-3	Q-4	Total
Units to be produced	31320	43440	28752	17400	120912
Hours per unit	0.25	0.25	0.25	0.25	0.25
Total Labour Hours	7830	10860	7188	4350	30228
Labour cost per hour	$8	$8	$8	$8	$8
Total Labour cost	$62,640	$86,880	$57,504	$34,800	$2,41,824

Budgeted Income Statement
Sales		$10,00,000
Less:Direct Costs
-Total Material cost	-$1,20,912
-Total Labour cost	-$2,41,824	-$3,62,736
Less:Overheads
-Production supervisor salary	-$40,000
-Machinery depreciation	-$13,000
-Factory supplies	-$60,456		=$2*30228 hours
-Maintenance & repairs	-$1,20,912		=$4*30228 hours
-Utilities	-$75,570	-$3,09,938	=$2.5*30228 hours
Less:SG&A Costs
Executive salary	-$50,000
Taxes & insurance	-$7,500
Delivery expense	-$30,000		=0.3*100000
Other administrative expenses	-$10,000	-$97,500	=0.1*100000
Less: WIP Cost
Opening WIP	-$1,80,000
Closing WIP	$1,55,000	-$25,000
Income before Tax		$2,04,826
-Tax @40%		-$81,930	=204826*40%
Net Income		$1,22,896