Question

Sora Industries has 63 million outstanding shares, $130 million in debt, $55 million in cash, and the following projected free cash flow for the next four years:

2 516.0 154.4 Year 1 Earnings and FCF Forecast ($ million) Sales 433.0 468.0 Growth vs. Prior Year Cost of Goods Sold Gross Profit Selling, General, & Admin. Depreciation EBIT 53.8 Less: Income Tax at 40% Plus: Depreciation Less: Capital Expenditures Less: Increase in NWC Free Cash Flow (7.0) 59.6 547.0 574.3 8.1% 10.3% 6.0% 5.0% (313.6) (345.7) (366.5) (384.8) 170.3 188.5 189.5 (93.6) (183.2) (109.4) (114.9) (7.5) (9.0) (9.5) 62.1 65.2 (21.5) (23.8) (24.8) (26.1) 7.0 7.5 9.0 9.5 (7.7) (10.) (9.9) (10.4) (6.3) (8.6) (5.6) (4.9) 24.6 30.8 33.3 25.3

Answer 1

DE F G H I Requirement a: Value of sora stock = 4.45 516 -345.7 L 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 468 -313.6 154.4 -93.6 547 -366.5 180.5 -109.4 574.3 -384.8 189.5 -114.9 170.3 -9.5 -103.2 -7.5 59.6 -23.84 65.1 53.8 -21.52 62.1 -24.84 -26.04 7.5 9.5 -7.7 -9.9 1037 -10 -8.6 24. 7 - 10.4 -4.9 33.3 1038 25.3 30.8 35.0 1 Sales 3 Cost of Goods Sold 4 Gross Profit 5 Selling, General & Admin. 6 Depreciation 7 EBIT 8 Less: Income tax at 40% 9 Plus: Depreciation 10 Less: Capital Expenditures 11 Less: Increases in NWC 12 Free Cash Flow Value of the firm at the end of year 4 [ 35 /(13%-5.1%) Total inflow PV factor (1/(1+13%)^year Present value of inflows Enterprise value Less : Debt Add: Cash equity value Number of shares(miilions] share value 443.04 1039 25.3 0.88496 22.4 24.7 0.78315 19.3 30.8 476.3 0.69305 0.61332 21.3 292.1 1040 1041 1042 1043 1044 1045 1046 1047 1048 355.13 -130 280.13 4.45

A B D E F G H I 1049 1050 Requirement b: Value of sora stock = 2.66 1051 1052 1053 0 1 468 2 516 3 547 4 574.3 -327.6 -361.2 -382.9 -402.00 164.1. -109.4 -9 172.3 -114.9 -9.5 45.7 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 154.8 -103.21 -7.5 44.1 -17.6 7.5 -10 47 140.4 -93.6 -7 39.8 -15.9 7 -7.7 -6.3 16.9 -18.3 -19.2 - 10.4 -8.6 1 Sales Cost of Goods Sold(70% ofsales) 4 Gross Profit 5 Selling, General & Admin. 6 Depreciation 7 EBIT 8 Less: Income tax at 40% 9 Plus: Depreciation 10 Less: Capital Expenditures 11 Less: Increases in NWC 12 Free Cash Flow Value of the firm at the end of year 4 [ 24.1 /(13%-5.1%) Total inflow PV factor (1/(1+13%)^year Present value of inflows Enterprise value Less: Debt Add: Cash equity value Number of shares(miilions] share value -4.9 15.4 20.9 22.9 24.11 305.06 16.9 0.88496 14.9 15.4 0.78315 12.0 20.9 328.0 0.69305 0.61332 14.5 201.2 1064 1065 1066 1067 1068 1069 1070 1071 1072 242.64 -130 167.64 1073 2.66 10

А в G H Requirement c: Value of sora stock = 6.82 1074 1075 1076 1077 1078 1079 1080 468 -313.6 154.4 516 -345.7 170.3 547 -366.5 574.3 -384.8 -74.9 -82.6 -87.5 -91.9 -7.5 80.2 1081 1082 1083 1084 1085 1086 1087 1088 -32.1 7.5 99.5 -10 1 Sales 3 Cost of Goods Sold 4 Gross Profit Selling, General & Admin.[16% of sales ] 6 Depreciation 7 EBIT 8 Less: Income tax at 40% 9 Plus: Depreciation 10 Less: Capital Expenditures 11 Less: Increases in NWC 12 Free Cash Flow Value of the firm at the end of year 4 [ 49.5/(13%-5.1%)] Total inflow PV factor (1/(1+13%)^year Present value of inflows Enterprise value Less : Debt Add: Cash equity value Number of shares(miilions] share value -8.6 37.0 -4.9 47.1 36.5 43.9 49.5 626.58 36.5 0.88496 32.31 37.0 0.78315 29.0 43.9. 673.6 0.69305 0.61332 30.4 413.2 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 504.90 -130 55 429.90 6.82

E F G H I Requirement d: Value of sora stock = 5.12 433 468 21 516 -345.7 170.3 | -103.21 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 -313.6 154.4 -93.6 -7 53.8 -21.5 547 574.3 -366.5 | -384.8 180.5 189.5 -109.4 -114.9 -9.5 62.1 65.11 -26.0 -7.5 9.5 59.6 -23.8 7.5 -10 -5.8 27.51 -7.7 21.8 53.4 -10.4 -3.3 34.9 32.6 36.7 1 Sales 3 Cost of Goods Sold 4 Gross Profit 5 Selling, General & Admin. 6 Depreciation 7 EBIT 8 Less: Income tax at 40% 9 Plus: Depreciation 10 Less: Capital Expenditures 11 Less: Increases in NWC 12 Free Cash Flow Value of the firm at the end of year 4 [ 36.7/(13%-5.1%)] Total inflow PV factor (1/(1+13%)year Present value of inflows Enterprise value Less : Debt Add: Cash equity value Number of shares(miilions] share value 464.56 1114 53.4 0.88496 47.21 27.5 0.78315 21.5 32.6 499.4 0.69305 0.61332 22.6 306.3 1115 1116 1117 1118 1119 1120 1121 1122 1123 397.70 -130 55 322.70 5.12

Increase in NWC in year 1 = 12% sales of year 1-18% sales of year 0 = 468 x 12% -433 x18% = $21.78 Increase in NWC in later years = 12% x change in sales

Requirement a: Value of sora stock = 4.45 Sales 468 ДА В 1025 1026 1027 1028 1 1029 3 1030 1031 1032 6 1033 1034 8 1035 19 1036 10 1037 1038 12 -313.6 =+E1028+E1029 -93.6 547 -366,5 =+G1028+G1029 -109.4 574.3 -384.8 =+H1028+H1029 - 114.9 516 -345.7 =+F1028+F1029 -103.2 -7.5 =+F1030+F1031+F1032 -23.84 7.5 - 10 -9.5 =+E1030+E1031+E1032 -21.52 =+G1030+G1031+G101=+H1030+H1031+H1032 -24.84 -26.04 9.5 -7.7 -99 - 10.4 11 -6.3 -8.6 -49 =+E1033+SUM(E1034:E1037) =+F1033+SUM(F1034:F1037) |=+G1033+SUM(G1034 =+H1033+SUM(H1034:H1037)=+H1038*1.051 Cost of Goods Sold Gross Profit Selling, General & Admin. Depreciation EBIT Less: Income tax at 40% Plus: Depreciation Less: Capital Expenditures Less: Increases in NWC Free Cash Flow Value of the firm at the end of year 4 [35 /(13%- Total inflow PV factor (1/(1+13%)^year Present value of inflows Enterprise value Less : Debt Add: Cash equity value Number of share value =+E1038+E1039 0.88496 =+E1040*E1041 =+F1038+F1039 0.78315 =+F1040*F1041 =+G1038+G1039 0.69305 =+G1040*G1041 443.0379 =+H1038+H1039 0.61332 =+H1040*H1041 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 =+SUM(E1042:H1042) -130 =+D1043+D1044+D1045 63 =+D1046/D1047

Ε H 468 516 574.3 -382.9 14 -327.6 =+E1053+E1054 -93.6 -361.2 =+F1053+F1054 -103.2 -7.5 =+F1055+F1056+F1057 =+(-F1058*40%) -402 =+G1053+G1054 =+H1053+H1054 -109.4 - 114.9 -9.5 =+G1055+G1056+G105=+H1055+H1056+H1057 =+(-G1058*40%) =+(-H1058*40%) =+E1055+E1056+E1057 =+(-E1058*40%) 9.5 -7.7 -9.9 - 10.4 AB 1050 Requirement b: 1051 Value of sora stock = 2.66 1052 1053 Sales Cost of Goods Sold(70% 1054 13 ofsales) 1055 Gross Profit 1056 15 Selling, General & Admin. 1057 16 Depreciation 1058 17 EBIT 1059 18 Less: Income tax at 40% 1060 19 Plus: Depreciation 10 Less: Capital Expenditures 1062 11 Less: Increases in NWC 1063 12 Free Cash Flow Value of the firm at the 1064 end of year 4 [ 24.1 /(13%- 1065 Total inflow 1066 PV factor (1/(1+13%) “year 1067 Present value of inflows 1068 Enterprise value Less : Debt 1070 Add: Cash 1071 equity value 1072 Number of 1073 share value 1074 -6.3 -4.9 =+E1058+SUM(E1059:E1062) =+F1058+SUM(F1059:F1062) =+G1058+SUM(G1059 =+H1058+SUM(H1059:H1062) =+H1063*1.051 =+E1063+E1064 0.88496 =+E1065*E1066 =+F1063+F1064 0.78315 =+F1065*F1066 =+G1063+G1064 0.69305 =+G1065*G1066 =24.1/7.9% =+H1063+H1064 0.61332 =+H1065*H1066 1069 =+SUM(E1067:H1067) - 130 =+D1068+D1069+D1070 =+D1071/D1072

H 433 468 - 313.6 =+E1078+E1079 2 516 -345.7 =+F1078+F1079 3 547 -366.5 =+G1078+G1079 4 574.3 -384.8 =+H1078+H1079 1083 ДА В 1075 Requirement c: 1076 Value of sora stock = 6.82 1077 1078 1 Sales 1079 13 Cost of Goods Sold 1080 Gross Profit Selling, General & 1081 15 Admin.(16% of sales ] 1082 16 Depreciation 17 EBIT 1084 8 Less: Income tax at 40% 1085 19 Plus: Depreciation 1086 10 Less: Capital Expenditures 1087 11 Less: Increases in NWC 1088 12 Free Cash Flow Value of the firm at the 1089 end of year 4[ 49.5/(13%- 1090 Total inflow 1091 PV factor (1/(1+13%)"year 1092 Present value of inflows 1093 Enterprise value 1094 Less : Debt 1095 Add: Cash 1096 equity value 1097 Number of 1098 share value 1099 =+(-E1078*16%) =+(-F1078*16%) =+(-G1078*16%) =+-H1078*16%) -7.5 -9.5 =+E1080+E1081+E1082 =+F1080+F1081+F1082 =+G1080+G1081+G108=+H1080+H1081+H1082 =+(-E1083*0.4) =+-F1083*0.4) =+(-61083*0.4) =+(-H1083*0.4) 7.5 9.5 -7.7 -10 - 10.4 -6.3 -8.6 -5.6 -4.9 =+E1083+SUM(E1084:E1087) =+F1083+SUM(F1084:F1087) =+G1083+SUM(G1084 =+H1083+SUM(H1084:H1087) =+H1088*1.051 -9.9 =+E1088+E1089 0.88496 =+E1090*E1091 =+F1088+F1089 0.78315 =+F1090*F1091 =+G1088+G1089 0.69305 =+G1090*G1091 -49.5/7.9% =+H1088+H1089 0.61332 =+H1090*H1091 =+SUM(E1092:H1092) -130 =+D1093+D1094+D1095 63 =+D1096/D1097

Requirement d: Value of sora stock = 5.12 3 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 6 -9 468 516 547 574.3 -313.6 -345.7 -366.5 -384.8 =+E1103+E1104 =+F1103+F1104 =+G1103+G1104 =+H1103+H1104 -93.6 -103.2 -109.4 -114.9 -7.5 -9.5 =+E1105+E1106+E1107 =+F1105+F1106+F1107 =+G1105+G1106+G110=+H1105+H1106+H1107 =+(-E1108*0.4) =+(-F1108*0.4) =+(-G1108*0.4) =+(-H1108*0.4) 7.5 -7.7 -10 -9.9 - 10.4 21.78 --(516-468) *12% --(547-516) *12% |--(574.3-547) *12% =+E1108+SUM(E1109:E1112) =+F1108+SUM(F1109:F1112) |=+G1108+SUM(G1109 =+H1108+SUM(H1109:H1112) =+H1113*1.051 8 9.5 1111 10 1112 1113 12 Sales Cost of Goods Sold Gross Profit Selling, General & Admin. Depreciation EBIT Less: Income tax at 40% Plus: Depreciation Less: Capital Expenditures Less: Increases in NWC Free Cash Flow Value of the firm at the end of year 4 [ 36.7/(13%- Total inflow PV factor (1/(1+13%) year Present value of inflows Enterprise value Less: Debt Add: Cash equity value Number of share value =+E1113+E1114 =+F1113+F1114 0.78315 =+F1115*F1116 =+G1113+G1114 0.69305 =+G1115*G1116 =36.7/7.9% =+H1113+H1114 0.61332 =+H1115*H1116 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 0.88496 =+E1115*E1116 =+SUM(E1117:H1117) - 130 55 =+D1118+D1119+D1120 =+D1121/D1122