Excel Case study

CaseStudies13.xlsx

Home >Business & Finance homework help >Accounting homework help >Excel Case study

Case Studies

			Corporate Finance
			Ross, Westerfield, and Jaffe
			11th edition
			Case Studies #1
			Input boxes in tan
			Output boxes in yellow
			Given data in blue
			Calculations in red
			Answers in green
			NOTE: Some functions used in these spreadsheets may require that
			the "Analysis ToolPak" or "Solver Add-in" be installed in Excel.
			To install these, click on "Tools\|Add-Ins" and select "Analysis ToolPak"
			and "Solver Add-In."

Chapter 2

Chapter 2
Cash Flows at Warf Computer, Inc.
Input area:
Balance Sheet (in $ thousands)
	2015	2014		2015	2014
Current assets			Current liabilities
Cash and equivalents	$ 452	$ 391	Accounts payable	$ 519	$ 485
Accounts receivable	716	668	Accrued expenses	247	401
Inventories	641	663	Total current liabilities
Other	92	78
Total current assets
			Long-term liabilities
Fixed assets			Deferred taxes		$ 159
Property, plant, and equipment	$ 4,148	$ 3,179	Long-term debt	1,179	1,148
Less accumulated depreciation	1,340	1,092	Total long-term liabilities
Net property, plant, and equipment
Intangible assets and others	793	709	Stockholders equity
Total fixed assets			Preferred stock	$ 21	$ 21
			Common stock	126	126
			Capital surplus	794	779
			Accumulated retained earnings		1,603
			Less treasury stock	192	126
			Total equity
			Total liabilities and
Total assets			shareholders equity
Acquisition of fixed assets	$ 1,482
Sale of fixed assets	$ 429
New debt issued	$ 228
Debt retired
New stock issued
Stock repurchased
Income Statement (in $ thousands)
Sales	$ 7,557
Cost of goods sold	4,456
Selling, general, and administrative	848
Depreciation
Operating income
Other income	75
EBIT
Interest expense	137
Pretax income
Taxes	776
Current		$ 605
Deferred
Net income
Dividends	$ 292
Retained earnings
Output area:
Operating cash flow
Earnings before interest and taxes
Depreciation
-Current taxes
Operating cash flow
Net capital spending
Acquisition of fixed assets
Sale of fixed assets
Capital spending
Alternatively,
Ending fixed assets
-Beginning fixed assets
Depreciation
Capital spending
Net working capital cash flow
Ending NWC
Beginning NWC
NWC cash flow
Cash flow from assets
OCF
-Net capital spending
-Change in NWC
Cash flow from assets
Cash flow to creditors
Interest
Retirement of debt
Debt service
Proceeds from sale of long-term debt
Total
Alternatively
Beginning long-term debt
Ending long-term debt
Interest
Total
Cash flow to stockholders
Dividends
Repurchase of stock
Cash to stockholders
Proceeds from new stock issue
Total
Alternatively
Beginning total equity
Ending total equity
Dividends
Retained earnings
Statement of cash flows
Operations
Net income
Depreciation
Deferred taxes
Changes in assets and liabilities
Accounts receivable
Inventories
Accounts payable
Accrued expenses
Other
Total cash flow from operations
Investing activties
Acquisition of fixed assets
Sale of fixed assets
Total cash flow from investing activities
Financing activties
Retirement of debt
Proceeds of long-term debt
Dividends
Repurchase of stock
Proceeds from new stock issues
Total cash flow from financing activities
Change in cash (on balance sheet)

Chapter 3

	Chapter 3
	Ratios and Financial Planning at East Coast Yachts
	Input area:
	Tax rate	40%
	Sales	$ 210,900,000
	COGS	148,600,000
	Other expenses	25,192,000
	Depreciation	6,879,000
	EBIT
	Interest	3,791,000
	Taxable income
	Taxes (40%)
	Net income
	Dividends	$ 4,759,301
	Add to RE
	Assets		Liabilities & Equity
	Current Assets		Current liabilities
	Cash	$ 3,285,600	Accounts payable	$ 6,977,700
	Accounts rec.	5,910,800	Notes payable	14,342,600
	Inventory	6,627,300	Total CL
	Total CA
			Long-term debt	$ 36,400,000
	Fixed assets
	Net PP&E	$ 101,481,200	Shareholder equity
			Common stock	$ 5,580,000
			Retained earnings	54,004,600
			Total equity
	Total assets		Total L&E
	Growth rate	20%
	Minimum FA purchase	$ 25,000,000
	Output area:
1)	Current ratio
	Quick ratio
	Total asset turnover
	Inventory turnover
	Receivables turnover
	Debt ratio
	Debt-equity ratio
	Equity multiplier
	Interest coverage
	Profit margin
	Return on assets
	Return on equity
3)	Retention ratio
	Sustainable growth rate
	Sales
	COGS
	Other expenses
	Depreciation
	EBIT
	Interest
	Taxable income
	Taxes (40%)
	Net income
	Dividends
	Add to RE	-
	Assets		Liabilities & Equity
	Current Assets		Current liabilities
	Cash		Accounts payable
	Accounts rec.		Notes payable
	Inventory		Total CL
	Total CA
			Long-term debt
			Shareholder equity
			Common stock
	Fixed assets		Retained earnings
	Net PP&E		Total equity
	Net PP&E
	Total Assets		Total L&E
	EFN
	Current ratio
	Quick ratio
	Total asset turnover
	Inventory turnover
	Receivables turnover
	Debt ratio
	Debt-equity ratio
	Equity multiplier
	Interest coverage
	Profit margin
	Return on assets
	Return on equity
4)	Sales
	COGS
	Other expenses
	Depreciation
	EBIT
	Interest
	Taxable income
	Taxes (40%)
	Net income
	Dividends
	Add to RE
	Assets		Liabilities & Equity
	Current Assets		Current liabilities
	Cash		Accounts payable
	Accounts rec.		Notes payable
	Inventory		Total CL
	Total CA
			Long-term debt
			Shareholder equity
			Common stock
			Retained earnings
	Fixed assets		Total equity
	Net PP&E
	Total Assets		Total L&E
	EFN
5)	EFN if minimum FA purchase is
	Depreciation as a percentage of fixed assets
	New fixed assets
	New depreciation
	Sales
	COGS
	Other expenses
	Depreciation
	EBIT
	Interest
	Taxable income
	Taxes (40%)
	Net income
	Dividends
	Add to RE
	Assets		Liabilities & Equity
	Current Assets		Current liabilities
	Cash		Accounts payable
	Accounts rec.		Notes payable
	Inventory		Total CL
	Total CA
			Long-term debt
			Shareholder equity
			Common stock
	Fixed assets		Retained earnings
	Net PP&E		Total equity
	Total Assets		Total L&E
	EFN

Chapter 4

	Chapter 4
	The MBA Decision
	Input area:
	Current salary	$ 65,000
	Years until retirement	40
	Salary increase	3%
	Tax rate	26%
	Wilton
	Tuition per year	$ 70,000
	Books & Supplies	$ 3,000
	Starting salary	$ 110,000
	Signing bonus	$ 20,000
	Salary increase	4%
	Tax rate	31%
	Mount Perry
	Tuition per year	$ 85,000
	Books & Supplies	$ 4,500
	Signing bonus	$ 18,000
	Starting salary	$ 92,000
	Salary increase	3.5%
	Tax rate	29%
	Both schools
	Health insurance	$ 3,000
	Room & board increase	$ 2,000
	Discount rate	6.30%
	Output area:
3.	Current job
	Aftertax income
	Present value of salary
	Wilton MBA
	PV of tuition & expenses
	Aftertax bonus
	PV of bonus
	Aftertax salary
	PV of salary in two years
	Value of salary today
	PV of attending Wilton
	Mount Perry MBA
	PV of tuition & expenses
	PV of signing bonus
	Aftertax salary
	PV of salary in one year
	PV of salary today
	PV of attending Mt. Perry
5.	Current job PV minus
	bonus after Wilton costs
	Value in 2 years
	Aftertax beginning salary
	Pretax beginning salary

Chapter 5

		Chapter 5
		Bullock Gold Mining
		Input area:
		Year	Cash flow
		0	$ (850,000,000)
		1	$ 170,000,000
		2	$ 190,000,000
		3	$ 205,000,000
		4	$ 265,000,000
		5	$ 235,000,000
		6	$ 170,000,000
		7	$ 160,000,000
		8	$ 105,000,000
		9	$ (75,000,000)
		Required return	12%
		Output area:
		Payback period
		IRR
		IRR
		MIRR
		Profitability index
		NPV

Chapter 6 Case #1

Chapter 6
Bethesda Mining
Input area:
Land cost	$ 4,000,000
Aftertax land value	$ 6,500,000
Equipment	$ 95,000,000
Equipment salvage	60%
Contract sales/tons	500,000
Contract $/ton	$86
Year 1 production	620,000
Year 2 production	680,000
Year 3 production	730,000
Year 4 production	590,000
Spot market $/ton	$77
Variable cost/ton	$31
Fixed costs	$4,100,000
NWC percent	5%
Reclamation costs	$2,700,000
Charitable expense	$6,000,000
Tax rate	38%
Required return	12%
Year 1 depreciation	14.29%
Year 2 depreciation	24.49%
Year 3 depreciation	17.49%
Year 4 depreciation	12.49%
Output area:
Time 0 cash flow
Equipment
Land
NWC
Total
Sales	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6
Contract
Spot
Total
Sales
VC
FC
Dep
EBT
Tax
NI
+ Dep
OCF
Beginning NWC
Ending NWC
NWC cash flow
Total cash flow
Book value
Salvage	MV
	BV
	Taxes
Salvage CF
	Time	Cash flow
	0
	1
	2
	3
	4
	5
	6
Profitability index
Average accounting return
IRR
IRR
NPV

Chapter 6 Case #2

Chapter 6
Goodweek Tires, Inc.
Input area:
Research and development	$ 10,000,000
Test marketing cost	$ 5,000,000
Initial equipment cost	$ 160,000,000
Equipment salvage value	$ 65,000,000
OEM market:
Price	$ 41
Variable cost	$ 29
Automobile production	6,200,000
Growth rate	2.50%
Market share	11.00%
Replacement market:
Price	$ 62
Variable cost
Market sales	32,000,000
Growth rate	2.00%
Market share	8.00%
Price increase above inflation	1%
VC increase above inflation	1%
Marketing and general costs	$ 43,000,000
Tax rate	40.00%
Inflation rate	3.25%
Required return	13.40%
Initial NWC	$ 9,000,000
NWC percentage of sales	15%
Year 1 depreciation	14.29%
Year 2 depreciation	24.49%
Year 3 depreciation	17.49%
Year 4 depreciation	12.49%
Output area:
Nominal price increase
Nominal VC increase
	Year 0	Year 1	Year 2	Year 3	Year 4
OEM:
Automobiles sold
Tires for automobiles sold
SuperTread tires sold
Price
Replacement market:
Total tires sold in market
SuperTread tires sold
Price
Revenue:
OEM market
Replacement market
Total
Variable costs:
OEM market
Replacement market
Total
Revenue
Variable costs
Marketing and general costs
Depreciation
EBT
Tax
Net income
OCF
New working capital:
Beginning
Ending
NWC cash flow
Book value of equipment
Aftertax salvage value:
Market value
Taxes
Total
	Year 0	Year 1	Year 2	Year 3	Year 4
Operating cash flow
Capital spending
Net working capital
Total cash flows
Discounted cash flow
NPV
IRR
Profitability index