Prob 3-1: ACME General Corp.
Note: Enter formulas in the cells highlighted yellow. Cells that have a little triangle in the upper right hand corner have comments you can view. Let the cursor hover over the cell and the comment will appear. These comments have hints or instructions. There may be slight (1 or 2 decimal) difference between this spreadsheet and what is in the text due to rounding of the entries in the statements.
Part a. Enter formulas to complete the balance sheet and income statement.
Income Statement (Millions)
200120022003
Sales3,729.604,139.864,512.44
Costs of Goods Sold2,312.352,566.712,797.71
Sales, General and Administrative745.92827.97902.49
Depreciation186.48206.99225.62
Operating Profit
Interest expense88.0596.49105.73
Earnings Before Taxes
Taxes (40%)
Net Income
Dividends160.04104.89
Additions to Retained Earnings
Balance Sheet (Millions)
Assets200120022003
Cash37.3041.4045.12
Inventory522.14579.58631.74
Accounts receivable932.401034.961128.11
Total current assets
Gross PPE2,619.283,031.403,443.32
Accumulated depreciation754.48
Net PPE
Total assets
Liabilities
Accounts payable372.96413.99451.24
Accrued expenses186.48206.99225.62
Short-term debt183.19285.90381.71
Total current liabilities
Long-term debt1,000.001,000.001,000.00
Total liabilities
Common stock500.00600.00600.00
Retained earnings1,114.01
Total common equity
Total liabilities and equity
Part b. Enter formulas to complete the statement of shareholders’ equity and statement of cash flow.
Statement of Shareholders’ Equity
20022003
Balance as of December 31 of previous year
Net Income
Dividends on Common Stock
Issuance of Common Stock
Common Stock Repurchases0.000.00
Balance as of December 31
Statement of Cash Flows
20022003
Operating Activities
Net Income
Depreciation & Amortization
Change in Inventory
Change in Accounts Receivable
Change in Accounts Payable
Change in Accruals
Net cash from operating activities
Investing Activities
Investment in PPE
Net cash from investing activities
Financing Activities
Change in short-term debt
Change in long-term debt
Change in common stock
Common dividends
Net cash from financing activities
Net cash flow
Starting cash
Ending cash
Part c. Calclate free cash flow and return on invested capital.
Free Cash Flow Calculation
200120022003
Operating profit
Tax on operating profit (40%)
NOPAT
Operating current assets
Operating current liabilities
NOWC
Net PPE
Total net operating capital
Investment in total net operating capital277.20
FCF
Return on Invested Capital (ROIC)