Please help with this excel work sheet and see attached document below for 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 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 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 Sales history 2005 2006 2007 2008 2009 40% Revenue $ 1,890,532 $ 2,098,490 $ 2,350,308 $ 3,432,000 $ 3,850,000 COGS $ 1,570,200 $ 1,695,694 $ 1,992,400 $ 2,864,000 $ 3,250,000 The Income statement and balance sheet for firm's capital expenditures are expected to ris will lead to an increase of $5,000 in accumul should be $4.3M 1. Using percentage of sales analysis techniq statement and balance sheet. 2. Create a chart of sales by year, including y 3. Add a trend line. 4. Create a scatter plot of sales vs. cogs. Add 5. Regress COGS against sales . 6. Using your sales trendline and annual sales next 3 years (3 years after the year with 4.3M line as well as at least one of the following: tr balance sheet for xxx are provided here. Note that are expected to rise by $50,000 in the new year. This $5,000 in accumulated depreciation. Sales next year es analysis techniques prepare a pro forma income et. y year, including your pro forma estimate. sales vs. cogs. Add a trend line. sales . ne and annual sales data forecast the sales level in the the year with 4.3M in sales). Forecast using the trend of the following: trend, linest, regression. Camelot Inc Balance Sheet As of Dec. 31, 2009 Assets Cash and Equivalents Accounts Receivable Inventory Total Current Assets Plant & Equipment Accumulated Depreciation Net Fixed Assets Total Assets Liabilities and Owner's Equity Accounts Payable Short-term Notes Payable Other Current Liabilities Total Current Liabilities Long-term Debt Total Liabilities Common Stock Retained Earnings Total Shareholder's Equity Total Liabilities and Owner's Equity $ $ $ $ $ $ 2008 57,600 351,200 715,200 1,124,000 491,000 146,200 $ 360,800 $ 344,800 $ 1,650,800 $ 1,468,800 $ $ $ $ $ $ $ $ $ $ 175,200 225,000 140,000 540,200 424,612 964,812 460,000 225,988 685,988 1,650,800 145,600 200,000 136,000 481,600 323,432 805,032 460,000 203,768 663,768 1,468,800 $ $ $ $ $ $ 2009 52,000 402,000 836,000 1,290,000 527,000 166,200 $ $ $ $ $ $ $ $ $ $ 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 $ Cash Flows from Investing Change in Plant & Equipment $ (36,000) Total Cash Flows from Investing $ 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 $ Net Change in Cash Balance $ (73,780) (36,000) 104,180 (5,600)