Instructions
Week 5 Homework
Forecasting and Trends
The Income statement and balance sheet for Camelot Inc. are provided here. Note that firm's capital expenditures are expected to rise by $50,000 in the new year. This will lead to an increase of $5,000 in accumulated depreciation. Sales next year should be $4.3M, and the dividend payment to common stockholders will equal the dividend payment in 2009. Interest expense will vary with sales.
1. Using percentage of sales analysis techniques prepare a pro forma income statement and balance sheet for the next year.
2. Create a chart of sales by year for all years, including your pro forma estimate.
3. Add a trend line.
4. Create a scatter plot of sales vs. cogs. Add a trend line.
5. Regress cogs against sales.
6. Using your sales trendline and annual sales data forecast the sales level in the next 3 years (3 years after the year with 4.3M in sales). Forecast using the trend line as well as at least one of the following: trend, linest, regression.
Income Statement
Camelot Inc
Income Statement
For the Year Ended Dec. 31, 2009
2009 2008
Sales $ 3,850,000 $ 3,432,000
Cost of Goods Sold $ 3,250,000 $ 2,864,000
Gross Profit $ 600,000 $ 568,000
Selling and G&A Expenses $ 330,300 $ 240,000
Fixed Expenses $ 100,000 $ 100,000
Depreciation Expense $ 20,000 $ 18,900
EBIT $ 149,700 $ 209,100
Interest Expense $ 76,000 $ 62,500
Earnings Before Taxes $ 73,700 $ 146,600
Taxes $ 29,480 $ 58,640
Net Income $ 44,220 $ 87,960
Notes:
Tax Rate 40%
Sales history Revenue COGS
2005 $ 1,890,532 $ 1,570,200
2006 $ 2,098,490 $ 1,695,694
2007 $ 2,350,308 $ 1,992,400
2008 $ 3,432,000 $ 2,864,000
2009 $ 3,850,000 $ 3,250,000
The Income statement and balance sheet for Camelot Inc are provided here. Note that firm's capital expenditures are expected to rise by $50,000 in the new year. This will lead to an increase of $5,000 in accumulated depreciation. Sales next year should be $4.3M, and the dividend payment to common stockholders will equal the dividend payment in 2009. Interest expense will vary with sales.
1. Using percentage of sales analysis techniques prepare a pro forma income statement and balance sheet.
2. Create a chart of sales by year, including your pro forma estimate.
3. Add a trend line.
4. Create a scatter plot of sales vs. cogs. Add a trend line.
5. Regress cogs against sales.
6. Using your sales trendline and annual sales data forecast the sales level in the next 3 years (3 years after the year with 4.3M in sales). Forecast using the trend line as well as at least one of the following: trend, linest, regression.
Balance Sheet
Camelot Inc
Balance Sheet
As of Dec. 31, 2009
Assets 2009 2008
XXXXXXXXXXCash and Equivalents $ 52,000 $ 57,600
XXXXXXXXXXAccounts Receivable $ 402,000 $ 351,200
XXXXXXXXXXInventory $ 836,000 $ 715,200
Total Cu
ent Assets $ 1,290,000 $ 1,124,000
XXXXXXXXXXPlant & Equipment $ 527,000 $ 491,000
XXXXXXXXXXAccumulated Depreciation $ 166,200 $ 146,200
Net Fixed Assets $ 360,800 $ 344,800
Total Assets $ 1,650,800 $ 1,468,800
Liabilities and Owner's Equity
XXXXXXXXXXAccounts Payable $ 175,200 $ 145,600
XXXXXXXXXXShort-term Notes Payable $ 225,000 $ 200,000
XXXXXXXXXXOther Cu
ent Liabilities $ 140,000 $ 136,000
Total Cu
ent Liabilities $ 540,200 $ 481,600
XXXXXXXXXXLong-term Debt $ 424,612 $ 323,432
Total Liabilities $ 964,812 $ 805,032
XXXXXXXXXXCommon Stock $ 460,000 $ 460,000
XXXXXXXXXXRetained Earnings $ 225,988 $ 203,768
Total Shareholder's Equity $ 685,988 $ 663,768
Total Liabilities and Owner's Equity $ 1,650,800 $ 1,468,800
Statement of Cash Flows
Camelot Inc
Statement of Cash Flows
For the Year Ended Dec. 31, 2009 ($ in 000's)
Cash Flows from Operations
Net Income $ 44,220
Depreciation Expense $ 20,000
Change in Accounts Receivable $ (50,800)
Change in Inventories $ (120,800)
Change in Accounts Payable $ 29,600
Change in Other Cu
ent Liabilities $ 4,000
Total Cash Flows from Operations $ (73,780)
Cash Flows from Investing
Change in Plant & Equipment $ (36,000)
Total Cash Flows from Investing $ (36,000)
Cash Flows from Financing
Change in Short-term Notes Payable $ 25,000
Change in Long-term Debt $ 101,180
Change in Common Stock $ - 0
Cash Dividends Paid to Shareholders $ (22,000)
Total Cash Flows from Financing $ 104,180
Net Change in Cash Balance $ (5,600)