fin615 week 5 homework 2017

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)