The Income statement and balance sheet for xxx 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

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.

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

Camelot Inc

Balance Sheet

As of Dec. 31, 2009

Assets

2009

2008

Cash and Equivalents

$ 52,000

$ 57,600

Accounts Receivable

$ 402,000

$ 351,200

Inventory

$ 836,000

$ 715,200

Total Current Assets

$ 1,290,000

$ 1,124,000

Plant & Equipment

$ 527,000

$ 491,000

Accumulated 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

Accounts Payable

$ 175,200

$ 145,600

Short-term Notes Payable

$ 225,000

$ 200,000

Other Current Liabilities

$ 140,000

$ 136,000

Total Current Liabilities

$ 540,200

$ 481,600

Long-term Debt

$ 424,612

$ 323,432

Total Liabilities

$ 964,812

$ 805,032

Common Stock

$ 460,000

$ 460,000

Retained 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

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 Current 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	$ -
Cash Dividends Paid to Shareholders	$ (22,000)
Total Cash Flows from Financing		$ 104,180
Net Change in Cash Balance		$ (5,600)