{"text_format":"3","workbook":"xls","n_sheets":1,"sheets":["synergy.xls"],"read":"whole","date_system":"1904","formulas":"cached_only"}

{"sheet":"synergy.xls","index":0,"n_rows":83,"n_cols":9,"rows_kept":83,"flags":["formulas_cached_only"]}
["SYNERGY VALUATION WORKSHEET"]
["Enter the following information on the bidding firm"]
["Current Financial Information"]
["Revenues in current year =",null,null,{"v":1000,"d":"$1,000.00"}]
["COGS as % of Revenues =",null,null,{"v":0.7,"d":"70.00%"}]
["Tax Rate on income =",null,null,{"v":0.35,"d":"35.00%"}]
["Interest Expenses =",null,null,{"v":100,"d":"$100.00"}]
["Current Depreciation =",null,null,{"v":50,"d":"$50.00"}]
["Current Capital Spending =",null,null,{"v":75,"d":"$75.00"}]
["Working Capital as % of Revenue =",null,null,{"v":0.05,"d":"5.00%"}]
["Projections of growth in earnings"]
["Expected growth rate - next 5 years =",null,null,{"v":0.15,"d":"15.00%"}]
["Expected growth rate - after 5 years =",null,null,{"v":0.06,"d":"6.00%"}]
["Risk measures"]
["Beta of the stock =",null,null,{"v":1.1,"d":"1.10"}]
[]
["Enter the following information on the target firm"]
["Current Financial Information"]
["Revenues in current year =",null,null,{"v":800,"d":"$800.00"}]
["COGS as % of Revenues =",null,null,{"v":0.75,"d":"75.00%"}]
["Tax Rate on income =",null,null,{"v":0.35,"d":"35.00%"}]
["Interest Expenses =",null,null,{"v":100,"d":"$100.00"}]
["Current Depreciation =",null,null,{"v":75,"d":"$75.00"}]
["Current Capital Spending =",null,null,{"v":100,"d":"$100.00"}]
["Working Capital as % of Revenue =",null,null,{"v":0.05,"d":"5.00%"}]
["Projections of growth in earnings"]
["Expected growth rate - next 5 years =",null,null,{"v":0.2,"d":"20.00%"}]
["Expected growth rate - after 5 years =",null,null,{"v":0.07,"d":"7.00%"}]
["Risk measures"]
["Beta of the stock =",null,null,1.25]
[]
["General Information"]
["Current riskfree rate =",null,null,{"v":0.06,"d":"6.00%"}]
["Risk premium over riskfree rate =",null,null,{"v":0.055,"d":"5.50%"}]
[]
["Information on Synergy benefits"]
["What form does the synergy benefit take?",null,null,3,"(1: Cost reduction ; 2:Cost reduction and Increase growth: 3: Only increase growth)"]
[]
["I. The cost of goods sold without synergy is ",null,null,{"v":0.7222222222222222,"d":"72.22%"}]
["If the synergy is going to reduce costs, enter the new cost of goods sold"]
[]
["IIa. The  growth rate in earnings in the next five years without synergy is",{"$nulls":4},{"v":0.16264431006047278,"d":"16.26%"}]
["If the synergy will increase growth, enter the new growth rate",{"$nulls":4},{"v":0.2,"d":"20.00%"}]
[]
["IIb. The growth rate after year 5 is expected to be",{"$nulls":4},{"v":0.06295154464473324,"d":"6.30%"}]
["If the synergy will increase this growth rate, enter the new growth rate",{"$nulls":4},{"v":0.07,"d":"7.00%"}]
[]
[null,null,"Bidder","Target","A+B: No synergy","A+B (Synergy)"]
["Free Cashflow to Equity",null,{"v":98.47826086956525,"d":"$98.48"},{"v":33.333333333333336,"d":"$33.33"},{"v":131.8115942028986,"d":"$131.81"},{"v":131.8115942028986,"d":"$131.81"}]
["Growth rate for first 5 years",null,{"v":0.15,"d":"15%"},{"v":0.2,"d":"20%"},{"v":0.16264431006047278,"d":"16.26%"},{"v":0.2,"d":"20.00%"}]
["Growth rate after five years",null,{"v":0.06,"d":"6%"},{"v":0.07,"d":"7%"},{"v":0.06295154464473324,"d":"6.30%"},{"v":0.07,"d":"7.00%"}]
["Beta",null,{"v":1.1,"d":"1.10"},1.25,{"v":1.1436625973764074,"d":"1.14"},{"v":1.1436625973764074,"d":"1.14"},"Weighted by present values of A and B"]
["Req. rate of return",null,{"v":0.1205,"d":"12.05%"},{"v":0.12875,"d":"12.88%"},{"v":0.12290144285570241,"d":"12.29%"},{"v":0.12290144285570241,"d":"12.29%"}]
["Riskfree Rate",null,{"v":0.06,"d":"6.00%"}]
[]
[]
["YEAR","FCF (A)","Term Val (A)","FCF (B)","Term. Val (B)","FCF (A+B)","TV (A+B)","FCF (A+B:S)","TV (A+B:S)"]
[1,{"v":113.25000000000003,"d":"$113.25"},null,{"v":40,"d":"$40.00"},null,{"v":153.25000000000003,"d":"$153.25"},null,{"v":158.1739130434783,"d":"$158.17"}]
[2,{"v":130.2375,"d":"$130.24"},null,{"v":48,"d":"$48.00"},null,{"v":178.2375,"d":"$178.24"},null,{"v":189.80869565217395,"d":"$189.81"}]
[3,{"v":149.773125,"d":"$149.77"},null,{"v":57.599999999999994,"d":"$57.60"},null,{"v":207.373125,"d":"$207.37"},null,{"v":227.77043478260873,"d":"$227.77"}]
[4,{"v":172.23909374999997,"d":"$172.24"},null,{"v":69.11999999999999,"d":"$69.12"},null,{"v":241.35909374999994,"d":"$241.36"},null,{"v":273.3245217391305,"d":"$273.32"}]
[5,{"v":198.07495781249995,"d":"$198.07"},{"v":3470.4042195247926,"d":"$3,470.40"},{"v":82.94399999999999,"d":"$82.94"},{"v":1510.6396595744682,"d":"$1,510.64"},{"v":281.01895781249993,"d":"$281.02"},{"v":4981.043879099261,"d":"$4,981.04"},{"v":327.9894260869566,"d":"$327.99"},6634.009716338271]
["PRESENT VALUE",null,{"v":2497.4849704138655,"d":"$2,497.48"},null,{"v":1025.4781292976093,"d":"$1,025.48"},null,{"v":3523.564432929902,"d":"$3,523.56"},null,{"v":4523.8297390589105,"d":"$4,523.83"}]
[]
[{"$nulls":3},"Gains from synergy =",null,null,{"v":1000.2653061290084,"d":"$1,000.27"}]
[{"$nulls":3},"Most that bidder firm can bid for target =",null,null,{"v":2025.7434354266177,"d":"$2,025.74"}]
[{"$nulls":3},"% Premium over the market price =",null,null,{"v":0.9754135925006318,"d":"97.54%"}]
["NOTES:"]
["(1) It is not simple to back out the growth rates for the combined firm when there is no synergy because growth rates will change. "]
["(2) It is far simpler to remember that in the absence of synergy the cashflows, terminal value and present value of the combined firm"]
["will always be equal to the sum of the same for the individual firms."]
["(3) To back out the terminal growth rate of the combined firm in the absence of synergy, use the combined terminal value estimated"]
["in conjunction with the required rate of return to solve for the terminal growth rate."]
[]
["STEPS IN VALUING SYNERGY"]
["1. Value each firm separately, projecting out free cashflows and terminal value."]
["2. Value the combined firm assuming no synergy. (Add up the present values for the two firms estimated  in step 1)"]
["3. Prepare a cashflow statement for the combined firm by just adding up the items on the individual firms' statements. "]
["4. Evaluate where the gains from synergy are going to come from. (Higher revenue growth or lower costs)"]
["5. Translate the synergy gain into dollars on the combined statement. If revenues are going to grow faster because of the synergy"]
["apply a faster growth rate to revenue in the combined statement. If costs are going to be cut, show the reductions in costs on the statement."]
["6. Calculate the value of the combined firm with the changes made in step 5. "]
["7. Compare to the value in step 2. The difference is the synergy gain. This is the MOST that one should as a takeover premium."]