Three years ago TSL was generating $6 million a year and lost their major custom
ID: 2796552 • Letter: T
Question
Three years ago TSL was generating $6 million a year and lost their major customer base and is currently at $500K per year. Recently they were able to secure a major client called the Metro Lines in Los Angeles and can pick other Metro lines across the country.The president, CEO Jeff wants to put together a 5 year projection and has developed the following financial assumptions to develop this projection. Use these financial assumptions to create a 5 year proforma with the excel spreadsheet posted in Blackboard.Jeff has an opportunity to secure $500K from an investor. Jeff has convinced the investor that this new client has signed a new contract for the next ten years and has estimated there is a 25% revenue growth rate year over year. The first year Jeff has estimated he will be working on 5,000 computer boards to repair.The average computer boards TSL will be working on for Metro costs about $600 per board and Jeff has determined a refurbishing price of $1200 per board.Jeff has incurred some debt over the past year due to the low sales and has an outstanding note for $300K at 7.5% for the next 5 years. Jeff plans on paying monthly payments starting this year in his projections.Jeff has reviewed his fixed costs and they are $75,000 per month and he plans on not changing these costs over the next five years.TSL has fixed assets and is using the MACRS method for depreciation. Fixed assets are currently $625,000.
1 20%
2 32%
3 19.20%
4 11.52%
5 11.52%
6 5.76%
The tax rate for Jeff right now is 34% when calculating EBIT.
NET WORKING CAPITAL
Year 0
Year 1
Year 2
Year 3
Year 4
Year 5
A/R
100,000
200,000
300,000
400,000
500,000
INV
100,000
150,000
200,000
350,000
450,000
A/P
100,000
50,000
75,000
150,000
50,000
WC
NWC
Jeff wants to calculate NPV, IRR and Payback and is using a 13% discount rate for calculating these three critical factors to see what his five year projections reveal.
Year 0
Year 1
Year 2
Year 3
Year 4
Year 5
A/R
100,000
200,000
300,000
400,000
500,000
INV
100,000
150,000
200,000
350,000
450,000
A/P
100,000
50,000
75,000
150,000
50,000
WC
NWC
Explanation / Answer
Dear Student, Lets take the summary of the question Estimated sales at 1st year 5000 Growth every year 25% selling price per unit 1200 Varibale cost 600 Debt 300000 interest 7.50% Fixed cost 75000 per month tax rate 34% Cost of capital 13% Computaion of factors given In the problem year 0 1 2 3 4 5 SECTION 1 Units sold 5000 6250 7813 9766 12207 Sales price 1200 1200 1200 1200 1200 Varibale price 600 600 600 600 600 Sales revenue 6000000 7500000 9375000 11718750 14648438 Variable cost 3000000 3750000 4687500 5859375 7324219 Fixed costs 900000 900000 900000 900000 900000 Depreciation 125000 200000 120000 72000 72000 EBIT 1975000 2650000 3667500 4887375 6352219 Interest 22500 22500 22500 22500 22500 EBT 1952500 2627500 3645000 4864875 6329719 TAX @34% 663850 893350 1239300 1654058 2152104 NET INCOME 1288650 1734150 2405700 3210818 4177614 SECTION II EBIT 1975000 2650000 3667500 4887375 6352219 TAX @34% 671500 901000 1246950 1661708 2159754 DEPRECIATION 125000 200000 120000 72000 72000 EQUALS OPERATING CASHFLOWS 1428500 1949000 2540550 3297668 4264464 SECTION III AFTER TAX OPERATING PROFIT 1428500 1949000 2540550 3297668 4264464 WORKING CAPITAL REQUIREMENTS -100000 300000 425000 600000 900000 CHANGE IN WOKRING CAPITAL 400000 125000 175000 300000 -100000 CAPITAL REQUIREMENTS -800000 0 0 0 0 0 ASSET VALUE -625000 ASSET SALVAGE VALUE 36000 TOTAL CASH FLOWS -1525000 1028500 1824000 2365550 2997668 4400464 PVF @ 13% 1 0.884956 0.783147 0.69305 0.613319 0.54276 CASH INFLOWS -1525000 910177 1428460 1639445 1838526 2388396 NPV = 6680003 PAY BACK PERIOD 1 YEAR + 614823/1428460 = 1.43 YEARS
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.