Finance on Exel sheets

FinanceCH10.xlsx

Home >Business & Finance homework help >Finance on Exel sheets

Sheet1

1. Determine values below based on data given:
Unit sales forecast
Unit sell	50.00							54.50	46.25
Unit VC	24.00	0.4800						24.00	24.00
Unit CM	26.00	0.5200
Discount rate	0.09
n =	7.00
				Acct. Br Ev.	NPV Br Ev.	NPV Br Ev.	Acct Br. Ev.	Initial Results	Initial Results
						at low price	at low price	at Premium	at Discount
Units			10,000.00			10,000.00
Investment		(560,000.00)
Revenues			500,000.00							Unit sales forecast
Variable costs			240,000.00							Unit sell	24.00
CM			260,000.00	- 0	- 0	- 0	- 0			Unit VC	24.00
Fixed Costs			90,000.00	90,000.00	90,000.00	90,000.00	90,000.00			Unit CM	- 0
Depreciation			80,000.00	80,000.00	80,000.00	80,000.00	80,000.00
EBIT			90,000.00							CM	- 0
Tax	0.35		31,500.00							Units	10,000.00
Net			58,500.00							Unit CM	x
Cash										Unit CM	- 0
PV cash flows										VC =	24.00
NPV										Sell =	24.00
IRR

2. Determine Accounting and NPV Breakeven with following data:
		0	1	2	3	4	5	6	7	8	9	10
invest		(1,000,000)
Units sold			5,000.00000	5,000.00000	5,000.00000	5,000.00000	5,000.00000	5,000.00000	5,000.00000	5,000.00000	5,000.00000	5,000.00000
Unit sell price			100	100	100	100	100	100	100	100	100	100
VC %	0.4		40	40	40	40	40	40	40	40	40	40
CM %	0.6
sales
VC	0.4
FC			200,000.0000	200,000.0000	200,000.0000	200,000.0000	200,000.0000	200,000.0000	200,000.0000	200,000.0000	200,000.0000	200,000.0000
depr			100,000.0000	100,000.0000	100,000.0000	100,000.0000	100,000.0000	100,000.0000	100,000.0000	100,000.0000	100,000.0000	100,000.0000
EBT			(300,000.0000)	(300,000.0000)	(300,000.0000)	(300,000.0000)	(300,000.0000)	(300,000.0000)	(300,000.0000)	(300,000.0000)	(300,000.0000)	(300,000.0000)
tax	0.35
Net income
Cash flow		(1,000,000)
r	0.12
				Acct Br Even		NPV Br Even
npv			Units		Units
			Rev		Rev
Pmt			COGS		COGS
Pv			GM		GM
i			Fixed		Fixed
n			Depr		Depr
Fv			EBIT		EBIT
NPV			Tax		Tax
			Net		Net
			Cash		Cash
			Pv		Pv
			NPV=		NPV=
3. Determine Degree of Operating Leverage under each condition below:
Levered Firm
	Variable	Fixed	Total	Total	Operating
Units	Costs	Costs	Costs	Revenue	Income
- 0	- 0	60,000			- 0
20,000		60,000			- 0
40,000		60,000			- 0
50,000		60,000			- 0
60,000		60,000			- 0
80,000		60,000			- 0
100,000		60,000			- 0
Unit selling price: $2		2.00
Unit variable cost: .80		0.80			- 0
Unit contribution margin: $1.20		1.20			- 0
Fixed costs: $60,000		60,000			- 0
					- 0
VC%		0.40			- 0
CM%		0.60			- 0
					- 0
% Change in Operating Income
% Change in Volume
At 80,000 units, a 1% change in volume will bring a 2.67% change in operating income
Conservative Firm
	Variable	Fixed	Total	Total	Operating
Units	Costs	Costs	Costs	Revenue	Income
- 0	- 0	12,000			- 0
20,000		12,000			- 0
30,000		12,000			- 0
40,000		12,000			- 0
60,000		12,000			- 0
80,000		12,000			- 0
100,000		12,000			- 0
Unit selling price: $2		2.00
Unit variable cost: $1.60		1.60
Unit contribution margin: .40		0.40
Fixed Costs: $12,000		12,000
4. Determine Fixed cost and Breakeven for data set below:
		Volume	Total Costs	Unit Sell	Cost		Costs	Revenues
	Hi	4,000,000	4,500,000	2.00	- 0	Hi	4,500,000
	LO	2,000,000	3,500,000			Lo	3,500,000
	Change					Change	1,000,000
	VC/unit					VC%
		Δ cost/Δ volume				CM%
	Fixed cost =	Total cost	minus	Variable cost		Br $
		4,500,000				Volume
		3,500,000
	Volume	SP	Revenue	COGS	GM	Fixed	Net

	Volume	Unit SP/ Cogs	Rev	Units Cost	COGS	GM	Fixed	Total Cost
Rev	1,000,000	2.00	2,000,000
COGS	1,000,000
GM
Fixed			- 0
Net			- 0
Ave Cost

	Volume	Unit SP/ Cogs	Rev	Units Cost	COGS	GM	Fixed	Total Cost
Rev	2,000,000	2.00	4,000,000
COGS	2,000,000	- 0
GM
Fixed			- 0
Net			- 0
Ave Cost