I need assistance completing my Acc hmwrk. Thank you

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. Ad 5. Regress COGS against sales . 6. Using your sales trendline and annual sale next 3 years (3 years after the year with 4.3M line as well as at least one of the following: t 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 $ $ $ $ $ $ 2009 52,000 402,000 836,000 1,290,000 527,000 166,200 $ $ $ $ $ $ 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 $ $ $ $ $ $ $ $ $ $ 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) PROFORMA ICOME STATEMENT ACCOUNT Sales cost of goods sold GROSS PROFIT Selling and G & A expenses Depreciation Expense Fixed expenses EBIT Interest expense Earnings before taxes Taxes NET INCOME ACCOUNT / SALES PERCENTAGE CHANGE X FORECASTED SALES 11.68% 111.68% x3850000 3,250,000/3,850,000 84.42% 0.8443% X 4300000 600,000/3,850,00 15.58% 15.58% X 4,300,000 330,000/3,850,000 8.57% 8.57% X 4,300,000 20,000 + 50,000 100,000 76000/3850000 1.97% 1.97 % x 4,300,000 PROFORMA BALANCE SHEET ASSETS Cash and cash equivalents Accounts recievable Inventory Total current assets Plant and equipment Accumulated depreciation Net fixed Assets LIABILITIES AND OWNERS EQUITY Accounts payable Short term notes payable Other current liabilities TOTAL CURRENT LIABILITIES Long Term dL ebt TOTAL LIABILITIES Common stock Retained earnings TOTAL SHAREHOLDERS AND OWNERS EQUITY ACCOUNT /SALES 52,000/3,850,000 402000/3850000 836,000/3850000 PERCENTAGE CHANGE X SALES FO 1.35% x 4,300,000 10.44% X 4,300,000 21.71% X 4,300,000 527,000/3850000 166,200 33.50% X 4,300,000 166,200 + 5000 175,200/3850000 225,000/3850000 140,000/3850000 4.55% X 4,300,000 5.84% x 4,300,000 3.64 % x 4,300,000 429,612/3850000 11.16% x 4,300,000 460,000 225,988/44220 x 47632 CHART OF SALES 5000000 4500000 YEAR 2005 2006 REVENUE 1,890,532 2,098,490 4000000 3500000 3000000 2500000 2000000 1500000 1000000 YEA Colu REV Line 4500000 4000000 3500000 2007 2008 2009 2010 2,350,300 3,432,000 3,850,000 4,300,000 3000000 YEA Colu REV Line 2500000 2000000 1500000 1000000 500000 0 1 2 3 4 5 6 SCATTER PLOT YEAR 2005 2006 2007 2008 2009 2010 REVENUE 1,890,532 2,098,490 2,350,300 3,432,000 3,850,000 4,300,000 COGS 1,570,000 1,695,000 1,992,400 2,864,000 3,250,000 3,630,000 5000000 4500000 4000000 3500000 3000000 2500000 2000000 1500000 1000000 500000 0 2004 2005 2006 2007 2008 2009 2010 FORECASTED INCOME X FORECASTED SALES STATEMENT 4,300,000 3,630,060 699940 368,510 70,000 100,000 161,430 84,710 77,720 30,088 47,632 AGE CHANGE X SALES FORECAST FORECASTED BALANCE SHEET 58,050 448,987 933,714 1,440,751 588,597 171,200 471,397 195,678 251,299 156,363 603,340 479,826 1,083,166 460,000 243,425 1,786,591 YEAR Column C REVENUE Linear (REVENUE) YEAR Column C REVENUE Linear (REVENUE) 5 6 6 2007 2008 2009 2010 2011 Column C Linear (Column C) REVENUE Column E COGS