EBIT	- 892,400.00	- 1,955,100.00	857,700.00	988,602.00	1,572,580.00
Interest received (+) / paid (-)	-	-	-	-	-		0	0	0	0	0
EBT	- 892,400.00	- 1,955,100.00	857,700.00	988,602.00	1,572,580.00
Tax	-	-	-	-	321,500.00
Net income//loss	- 892,400.00	- 1,955,100.00	857,700.00	988,602.00	1,251,080.00
Calculated numbers
Revenue growth rate		14.25%	124.05%	16.54%	7.98%
Cost of sales / Revenue	35.62%	34.12%	51.65%	53.45%	51.56%
SGA growth rate		34.75%	5.32%	11.84%	6.65%
Average other expenses / income					-	-
Average Interest					-	-
Average tax rate					20.44%	20.44%
Valuation
EAT
Depreciation							25,545.00	25,440.00	23,200.00	24,600.00	25,100.00
Increase in NWC							200,540.00	354,900.00	510,250.00	350,100.00	125,200.00
Capex							541,200.00	324,100.00	122,100.00	50,120.00	40,120.00
Interest expense (1 - tax rate)
FCF
WACC					7%
Infinite growth rate					3.00%
Terminal value
PV
5 year CF												Optional step
Terminal value												Optional step
Enterprise value
Value of debt					-
Value of cash					3,554,000.00
Value of equity
Number of shares					1,755,400.00
Estimated value per share					-
Current price per share					115

Context

We are evaluating a proposed acquisition whereby the "Bidder" firm will buy the "Target" firm via a stock swap

Complete the highlighted cells in each sheet to complete the tasks below

Tasks

1

The completed "Bidder" sheet is provided

2

Follow the same format as the "Bidder" sheet to complete the "Target" sheet

3

For "Combined_sim1", assume that the merged entity continues to function no different from the sum of pre merger entities. Complete the highlighted parts and obtain the value of the combined entity.

4

For "Combined_sim2" we now incorporate synergies as indicated in revenue, cost of sales and SGA. Complete the highlighted parts and obtain the valuation information as before

5

Carry out a sensitivity analysis (for your response to part 4 above) for

i)

Increase in value due to merger ($)

ii)

Increase in value per existing share for bidder

On the basis of the two key synergy assumptions of volume of sales and SGA

6

Based on 4 and 5 above, calculate the maximum target share price that the bidder can accept when deciding the exchange ratio (i.e. the number of shares of bidder to be issues to a holder of 1 share in the target firm). Put differently calculate the maximum premium over the target share price of $115 the bidder should be willing to accept

7

Assume you are an investor in the bidder and you anticipate this deal to be announced tomorrow at a) the share exchange ratio calculated in "Combined_sim1" but b) with the synergies indicated in "Combined_sim2"

i)

Should you increase of decrease your shareholding in the bidder firm? Why?

ii)

Suppose all these calculations are based on "normal" actions by a generic bidder management. Now comment on how these projections may be modified based on any two issues noted by Shleifer and Vishny (1998).

For bonus points (that compensate for any deficiencies or errors in other parts of the assignment):

1

Suggest up to three ways in which the sheets can be better/ alternatively designed to incorporate information.

2

Implement them and save your suggestions as a separate file

Note:

Clearly markets are not perfectly efficient in this assignment - prices can diverge signficantly from esimated value

					Historic	Averages	Forecast
Year	-4	-3	-2	-1	0		1	2	3	4	5
Income statement
Revenues	27,799,000.00	30,601,000.00	32,376,000.00	34,350,000.00	36,397,000.00		38,938,995.17	41,658,525.30	44,567,989.55	47,680,653.07	51,010,707.48
Cost of sales	15,353,000.00	16,534,000.00	17,405,000.00	19,038,000.00	20,441,000.00		21,385,551.55	22,879,135.33	24,477,032.18	26,186,527.40	28,015,415.12
Gross profit	12,446,000.00	14,067,000.00	14,971,000.00	15,312,000.00	15,956,000.00		17,553,443.63	18,779,389.97	20,090,957.37	21,494,125.67	22,995,292.35
SGA expenses	8,766,000.00	9,892,000.00	10,469,000.00	10,563,000.00	11,511,000.00		12,332,617.53	13,212,879.43	14,155,971.55	15,166,378.50	16,248,905.00
Other expenses (+)/income (-)	103,000.00	- 58,000.00	- 140,000.00	- 196,000.00	66,000.00		- 45,000.00	- 45,000.00	- 45,000.00	- 45,000.00	- 45,000.00
EBIT	3,577,000.00	4,233,000.00	4,642,000.00	4,945,000.00	4,379,000.00		5,265,826.09	5,611,510.53	5,979,985.81	6,372,747.17	6,791,387.35
Interest expenses (negative numbers indicate interest paid)	- 33,000.00	- 28,000.00	- 19,000.00	- 59,000.00	- 54,000.00		- 38,600.00	- 38,600.00	- 38,600.00	- 38,600.00	- 38,600.00
EBT	3,544,000.00	4,205,000.00	4,623,000.00	4,886,000.00	4,325,000.00		5,227,226.09	5,572,910.53	5,941,385.81	6,334,147.17	6,752,787.35
Tax	851,000.00	932,000.00	863,000.00	646,000.00	2,392,000.00		1,394,329.58	1,486,538.72	1,584,827.18	1,689,593.79	1,801,263.42
Net income//loss	2,693,000.00	3,273,000.00	3,760,000.00	4,240,000.00	1,933,000.00		3,832,896.52	4,086,371.81	4,356,558.64	4,644,553.38	4,951,523.94
Calculated numbers
Revenue growth rate		10.08%	5.80%	6.10%	5.96%	6.98%
Cost of sales / Revenue	55.23%	54.03%	53.76%	55.42%	56.16%	54.92%
SGA growth rate		12.85%	5.83%	0.90%	8.97%	7.14%
Average other expenses / income					- 45,000.00	- 45,000.00
Average Interest					- 38,600.00	- 38,600.00
Average tax rate					26.67%	26.67%
Valuation
EAT							3,832,896.52	4,086,371.81	4,356,558.64	4,644,553.38	4,951,523.94
Depreciation							56,675.57	313,177.18	335,003.40	358,350.75	383,325.24
Increase in NWC							1,439,190.48	630,664.12	674,616.91	721,632.91	771,925.58
Capex							- 133,588.47	610,329.20	652,864.80	698,364.83	747,035.88
Interest expense (1 - tax rate)							- 28,303.69	- 28,303.69	- 28,303.69	- 28,303.69	- 28,303.69
FCF							2,612,273.76	3,186,859.36	3,392,384.01	3,611,210.08	3,844,191.41
WACC					5%
Infinite growth rate					2.50%
Terminal value											157,611,847.85
PV
5 year CF							2,487,879.77	2,890,575.39	2,930,468.86	2,970,951.47	3,012,024.56
Terminal value											123,493,006.93
Enterprise value					137,784,906.99
Value of debt					3,810,000.00
Value of cash					5,245,000.00
Value of equity					139,219,906.99
Number of shares					1,623,800.00
Estimated value per share					85.74
Current price per share					90.00