The Income statement and balance sheet for Camelot Inc . are provided here . Note that firm’s…

The Income statement and balance sheet for Camelot Inc . are provided here . Note that firm’s…

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
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.
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
$ (1,20,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
$ 1,01,180
Change in Common Stock
$ –
Cash Dividends Paid to Shareholders
$ (22,000)
Total Cash Flows from Financing
$ 1,04,180
Net Change in Cash Balance
$ (5,600)