Generate the following at the end of a three month quarter (SHOW EXCEL FORMULAS)

• Balance Sheet at the end of month 3
• Income Statement for the three month period
• Statement of Cash Flows for the three month period

Assumptions:

• Three month time horizon
• Two product lines
  • Product A
    • Sales Price = $70 per unit
    • Costs = $50 per unit
    • Half of costs are paid in the current period and half the following month
  • Product B
    • Sales Price = $100 per unit
    • Costs = $70 per unit
    • Half of costs are paid in the current period and half the following month
• Beginning Inventory
  • 5,000 units of product A
  • 5,000 units of product B
  • Cost of sales on LIFO basis (accounting adjustment made at the end of each quarter)
• Sales Schedule
  • Month 1 = 10,000 units of A; 10,000 units of B
  • Month 2 = 15,000 units of A; 20,000 units of B
  • Month 3 = 15,000 units of A; 25,000 units of B
• Other Cash Outflows
  • S,G&A = $150,000 per month
  • Quarter end dividend of $250,000
  • CAPEX expenditure of $200,000 in month 2
• Other Assumptions
  • Sales collected
    • 40% month of sale
    • 30% following month
    • 30% month after that
  • Beginning A/R collected $125k in month 1 and $125k in month 2
  • Opening A/P balance paid in month 1
  • Even production schedule
  • Target ending inventory at end of quarter = 50% of previous months sales
  • Minimum Cash Balance = $100,000
  • Income Tax Rate = 20%
  • Long-term Debt interest = 5.5%
  • Line of Credit interest rate (if you need to borrow) = 30 day LIBOR (0.50%) plus 350 basis points interest paid on previous months ending balance

Opening Balance Sheet:

Cash 150,000

Accounts Receivable 250,000

Inventory 600,000

Total Current Assets 1,000,000

Fixed Assets 1,750,000

Total Assets 2,750,000

Accounts Payable 150,000

Line of Credit -0-

Total Current Liabilities 150,000

Long-term Debt 600,000

Total Liabilities 750,000

Common Stock 100,000

Retained Earnings 1,900,000

Total Liabilities & Equity 2,750,000