statement of cash flows

Please prepare  Excel solution for the following cash flow problem with graphs 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 Shortterm 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)