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:
  • Companys 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)
  • Companys 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)