Case Study : Short-term Financial Planning
Introduction: Heavenly Reception
Heavenly Reception Limited is a local dealer for a national satellite television distributor. Recent growth has been very strong, but the company has to increase its financial leverage to deal with expansion. The high degree of financial risk is a concern to the company's controller as she prepares the budget for the coming fiscal year.
Assume the role of Abby Jeffers and prepare pro forma income statements, cash budgets, and balance sheets for Quarters 1, 2, 3, and 4 of 2004. Accompanying the budget should be a one-page memorandum which describes the changes in sales and cash flows each quarter, the financial decisions made, and if the company is in compliance with its ratio requirements.
The memo should be single-spaced and use the 11-point Arial font with .7 inch margins. All headings should be in12-point font and be bolded. Submit one Word file containing the memorandum and an Excel workbook file containing five worksheets: Input Data, Income Statement, Cash Budget, Balance Sheet, and Key Financial Ratios. The following templates for the pro forma financial statements should be used:
|
Income Statement
|
|
|
Q1
|
Q2
|
Q3
|
Q4
|
Year
|
|
Revenue Dish Sales Installation
Accounts Maintenance Fees Basic
Deluxe Optimal
Dish Maintenance
|
|
|
|
|
|
|
Income Statement
|
|
|
Q1
|
Q2
|
Q3
|
Q4
|
Year
|
|
Total Revenue Expenses
CGS
Selling Installation Maintenance Administration Depreciation Operating Income Interest Income Interest Expense Income Before Tax Income Tax
Net Income
|
|
|
|
|
|
|
Cash Budget
|
|
|
Q1
|
Q2
|
Q3
|
Q4
|
Year
|
|
Cash Balance, Beginning Cash Receipts
Dish Sales/Installation This Quarter
Last Quarter
Accounts Maintenance Fees Basic
Deluxe Optimal Maintenance Interest
Tax Refund
Total Cash Receipts Cash Disbursements Purchases
This Quarter Last Quarter Selling Installation
Maintenance
|
|
|
|
|
|
|
Cash Budget
|
|
|
Q1
|
Q2
|
Q3
|
Q4
|
Year
|
|
Administration Interest Income Tax
Regular Dividends Capital Purchases
Total Cash Disbursements Sub-total
Financing Borrowing Line-of-Credit Term Loan Repayment Line-of-Credit Term Loan Special Dividends
Issuance/Repurchase of Shares
Total Financing
Temporary Investment Cash Balance, Ending
|
|
|
|
|
|
|
Balance Sheet
|
|
|
Q1
|
Q2
|
Q3
|
Q4
|
|
Current Assets
Cash
Temporary Investments Accounts Receivable Satellite Inventory
Total Current Assets Property, Plant, & Equipment Equipment, Net
Total Assets Current Liabilities Accounts Payable Line of Credit
Current Portion of Long-term Debt Total Current Liabilities Long-term Liabilities
Term Loan Shareholders' Equity Common Shares Retained Earnings
Total Liabilities &Equities
|
|
|
|
|
|
Ratio Table
|
|
|
Q1
|
Q2
|
Q3
|
Q4
|
|
Current Ratio - 1.5 LOC/(A/R + Inventory)-1.0 Debt Ratio - .35
Times Interest Earned - 4.0
LOC Financing - $200,000
|
|
|
|
|
Attachment:- Assignment.rar