80.2K

Verified Solution

Link Copied!

INSTRUCTIONS:

The Income statement and balance sheet for xxx 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.

2. Create a chart of sales by year, 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	40%
Sales history	Revenue	COGS
2005	$ 1,890,532	$ 1,570,200
2006	$ 2,098,490	$ 1,695,694
2007	$ 2,350,308	$ 1,992,400
2008	$ 3,432,000	$ 2,864,000
2009	$ 3,850,000	$ 3,250,000

Camelot Inc
Balance Sheet
As of Dec. 31, 2009
Assets	2009	2008
Cash and Equivalents	$ 52,000	$ 57,600
Accounts Receivable	$ 402,000	$ 351,200
Inventory	$ 836,000	$ 715,200
Total Current Assets	$ 1,290,000	$ 1,124,000
Plant & Equipment	$ 527,000	$ 491,000
Accumulated Depreciation	$ 166,200	$ 146,200
Net Fixed Assets	$ 360,800	$ 344,800
Total Assets	$ 1,650,800	$ 1,468,800
Liabilities and Owner's Equity
Accounts Payable	$ 175,200	$ 145,600
Short-term Notes Payable	$ 225,000	$ 200,000
Other Current Liabilities	$ 140,000	$ 136,000
Total Current Liabilities	$ 540,200	$ 481,600
Long-term Debt	$ 424,612	$ 323,432
Total Liabilities	$ 964,812	$ 805,032
Common Stock	$ 460,000	$ 460,000
Retained Earnings	$ 225,988	$ 203,768
Total Shareholder's Equity	$ 685,988	$ 663,768
Total Liabilities and Owner's Equity	$ 1,650,800	$ 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		$ (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 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		$ 104,180
Net Change in Cash Balance		$ (5,600)

Answer & Explanation Solved by verified expert

See Answer