Please provide help with an excel sheet. This upload goes from the bottom up. Thanks

Camelot Inc Balance Sheet 3 As of Dec. 31. 2009 Assets 2009 2008 Cash and Equivalents 52,000 $ 57,600 6 Accounts Receivable $ 402,000 $ 351,200 7 Inventory $ 836,000 715,200 Total Current Assets $ 1,290,000 $ 1,124,000 Plant & Equipment $ 527,000 $ 491,000 10 Accumulated Depreciation $ 166,200 $ 146,200 11 Net Fixed Assets $ 360,800 $ 344.800 12 Total Assets $ 1.650.800 $ 1.468.800 13 Liabilities and Owner's Equity 14 Accounts Payable $ 175,200 $ 145,600 15 Short-term Notes Payable $ 225,000 $ 200,000 16 Other Current Liabilities sel 140,000 136,000 17 Total Current Liabilities $ 540,200 481,600 18 Long-term Debt $ 424,612 $ 323,432 19 Total Liabilities $ 964,812 $ 805,032 20 Common Stock $ 460,000 $ 460,000 21 Retained Earnings $ 225,988 $ 203,768 22 Total Shareholder's Equity $ 685,988 $ 663,768 23 Total Liabilities and Owner's Equity $ 1.650.800 $ 1.468.800 24Camelot Inc W NJ Statement of Cash Flows For the Year Ended Dec. 31. 2009 ($ in 000's) Cash Flows from Operations Net Income 44,220 6 Depreciation Expense 20,000 Change in Accounts Receivable (50,800) 8 Change in Inventories $ (120,800) 9 Change in Accounts Payable 29,600 10 Change in Other Current Liabilities 4,000 11 Total Cash Flows from Operations (73,780) 12 Cash Flows from Investing 13 Change in Plant & Equipment $ (36,000) 14 Total Cash Flows from Investing $ (36,000) 15 Cash Flows from Financing 16 Change in Short-term Notes Payable 25,000 17 Change in Long-term Debt 101,180 18 Change in Common Stock 19 Cash Dividends Paid to Shareholders $ (22,000) 20 Total Cash Flows from Financing 104,180 21 Net Change in Cash Balance (5.600) 22 23 24 25 26For 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 co Selling and G&A Expenses $ 330,300 $ 240,000 LD Fixed Expenses $ 100,000 $ 100,000 10 Depreciation Expense $ 20,000 $ 18.900 11 EBIT $ 149,700 $ 209,100 12 Interest Expense 76,000 $ 62,500 13 Earnings Before Taxes $ 73,700 $ 146,600 14 Taxes 29,480 EA 58,640 15 Net Income 44.220 87.960 16 17 Notes: 18 Tax Rate 40% 19 20 21 Sales history Revenue COGS 22 2005 $ 1,890,532 $ 1,570,200 23 2006 $ 2,098,490 $ 1,695,694 24 2007 $ 2,350,308 $ 1,992,400 25 2008 $ 3,432,000 $ 2,864,000 26 2009 $ 3,850,000 $ 3,250,000 27 28 29 30 31 32 The Income statement and balance sheet for Camelot Inc are provided here. Note that 33 firm's capital expenditures are expected to rise by $50,000 in the new year. This will lead 34 to an increase of $5,000 in accumulated depreciation. Sales next year should be $4.3M, 35 and the dividend payment to common stockholders will equal the dividend payment in 36 2009. Interest expense will vary with sales. 37 38 1. Using percentage of sales analysis techniques prepare a pro forma income statement and balance sheet. 39 40 2. Create a chart of sales by year, including your pro forma estimate. 3. Add a trend line. 41 4. Create a scatter plot of sales vs. cogs. Add a trend line. 42 5. Regress cogs against sales. 43 6. Using your sales trendline and annual sales data forecast the sales level in the next 3 44 years (3 years after the year with 4.3M in sales). Forecast using the trend line as well as 45 at least one of the following: trend, linest, regression. 462 Forecasting and Trends 3 4 The Income statement and balance sheet for Camelot Inc. are provided here. Note 5 that firm's capital expenditures are expected to rise by $50,000 in the new year. 6 This will lead to an increase of $5,000 in accumulated depreciation. Sales next year 7 should be $4.3M, and the dividend payment to common stockholders will equal the 8 dividend payment in 2009. Interest expense will vary with sales. 9 10 1. Using percentage of sales analysis techniques prepare a pro forma income statement and balance sheet for the next year. 11 2. Create a chart of sales by year for all years, including your pro forma estimate. 12 3. Add a trend line. 13 4. Create a scatter plot of sales vs. cogs. Add a trend line. 14 5. Regress cogs against sales. 15 6. Using your sales trendline and annual sales data forecast the sales level in the 16 next 3 years (3 years after the year with 4.3M in sales). Forecast using the trend 17 line as well as at least one of the following: trend, linest, regression. 18 19 20 21