Simple Corp valuation model                        
 
       
   
 
This is a valuation of a simple company. Let's call it "Simple corp"  
The company produces and sells funny T-shirts with the smiling face  
This is a start-up project  
We have RUB300,000 of our own money and borrow RUB150,000  
 
Steps:    
 
First we need to construct the model and it should simply work. It's not nessesary to make it realistic.You can change everything later when all the connections and links in the model will be done  
 
1. Make Macro assumptions (not only macroeconomic figures but also prices for your goods and services, basic wages etc)  
2. Copy all the data from company's fillings for several periods (balance sheet, P&L, cash flows statement) if it is not a new project but existing company  
3. Make operational forecast (it shouldn't be very real, just the basis for calculation. You can change any inputs later when model is done)  
4. Build a model based on your assumptions. Start with P&L, continue with balance sheet and cash flows statement  
 
Short description of the project:    
 
We purchase t-shirt producing machine for RUB120,000  
We hire 1 employee  
We ignore rent costs assuming that we produce t-shirts in the garage. With rent costs such a small business is doomed  
We start t-shirt production  
One machine can produce 8 t-shirts per day (full capacity)  
We assume that in 2010 we produce only 3 t-shirts per day (capacity utilization)  
In 2012 we purchase 1 more machine and hire one more employee  
Thus in 2012 we have negative cash flows and have to borrow RUB350,000 due to high capex and salaries costs  
After that our business runs smoothly  
 
MACRO ASSUMPTIONS   2010E 2011E 2012E 2013E 2014E 2015E  
End-year exchange rate (RUB/USD)   29.5 29.0 28.7 28.5 28.2 28.0  
Average exchange rate (RUB/USD)   30.5 30.0 29.0 28.5 28.4 28.2  
End-year inflation (%)   10.5 10.5 10.0 9.5 9.0 8.5   Nobody is able to predict inflation rate. Let's be realistic.  
Average inflation (%)   10.8 10.8 10.3 9.8 9.3 8.8   So we just make assumption about its smooth decrease  
       
Cost per one unit   2010E 2011E 2012E 2013E 2014E 2015E  
Empty T-shirt cost, RUB   100 111 122 134 147 160   T-shirt is a simple, non-essential good. I assume its price will grow with inflation rate (in Russia)  
T-shirt machine cost, RUB   120 000 132 960 146 655 161 027 176 003 191 491   Took it from my head. I don't even know how it looks like  
Paint cost (per 1 shirt), RUB   20.0 22.2 24.4 26.8 29.3 31.9   Cost are often based on inflation  
       
Salary of one employee, RUB/month   30 000 34 740 40 055 45 983 52 559 59 812   In Moscow salaries usually grow faster than inflation. I assumed additional 5% to inflation rate per year
       
Price   2010E 2011E 2012E 2013E 2014E 2015E  
        I believe that my T-shirts are exlusive and I will increase their prices as I want  
Funny T-shirt price, RUB   590 708 850 1 020 1 223 1 468   Value added tax = 18%  
       
OPERATIONAL INPUTS   2010E 2011E 2012E 2013E 2014E 2015E  
# of machines   1 1 2 2 2 2   Change it in a way you want it, but don't forget to add new machine purchase to assets, capex etc
          Let's assume that we'll be successful and purchase the second machine in 2012  
Annual machine capacity   2080 2080 4160 4160 4160 4160  
Annual production   780 1040 1040 1248 1560 1560   Assumed number of t-shirts per week multiplied by 52 (# of weeks in a year)  
Annual sales rate   97% 97% 97% 97% 97% 97%  
Annual sales   757 1032 1040 1242 1551 1560   Just a preliminary assumption. I start to produce when  
Inventories   23 31 31 37 47 47   I assume that 3% of customers will refuse to buy the T-shirts when they see them  
       
       
P&L   2010E 2011E 2012E 2013E 2014E 2015E  
Revenue   446 394 730 798 883 584 1 265 999 1 897 090 2 290 250  
Cost of production   -93 600 -138 278 -152 521 -200 962 -274 564 -298 726  
# of employees   1 1 2 2 2 2  
Salaries   -360 000 -416 880 -961 325 -1 103 601 -1 261 416 -1 435 492   We hire another person to operate the second machine in 2012  
       
DD&A   -17 143 -17 143 -38 094 -38 094 -38 094 -38 094  
Marketing costs   -30 500 -15 000 -16 545 -18 166 -19 856 -21 603   Web site maintenance. Internet ad  
Operating income   -54 849 143 496 -284 901 -94 824 303 160 496 335  
Interest income   15 750 9 530 11 116 -2 742 -7 540 -268   I assume that only 50% of my cash in the beginning of the year can be put on deposit account  
Interest expence   -30 000 -15 000 -20 000 -10 000 0 0   Linked to debt (balance). Cost of debt is 20%  
PBT   -69 099 138 026 -293 784 -107 566 295 621 496 067  
Tax rate   0% 20% 0% 0% 20% 20%  
Income tax   0 -27 605 0 0 -59 124 -99 213  
Net income   -69 099 110 421 -293 784 -107 566 236 497 396 854  
       
       
BALANCE SHEET 2009 2010E 2011E 2012E 2013E 2014E 2015E  
Current assets        
Cash   272 281 317 613 -78 352 -215 418 -7 667 408 958  
Inventories   2 808 4 148 4 576 6 029 8 237 8 962  
Total current assets   275 089 321 762 -73 776 -209 389 570 417 920  
Non-current assets        
PP&E (cost)   120 000 120 000 266 655 266 655 266 655 266 655  
PP&E (new eq)   120 000 0 146 655 0 0 0  
Total PP&E   240 000 120 000 266 655 266 655 266 655 266 655  
Accumulated DD&A   17 143 34 286 72 379 110 473 148 566 186 660  
PP&E   222 857 85 714 194 276 156 182 118 089 79 995  
Total non-current assets   222 857 85 714 194 276 156 182 118 089 79 995  
TOTAL ASSETS   497 946 407 476 120 499 -53 207 118 658 497 915  
EQUITY        
Paid-in capital 300 000 417 045 291 154 272 962 256 821 242 191 224 594  
       
Retained earnings   -69 099 41 322 -252 462 -360 029 -123 532 273 322  
Total equity   347 946 332 476 20 499 -103 207 118 658 497 915   Usually analysts dont pay much attention to equity decomposition.  
Short term liabilities         It is often calculated as Assets - Liabilities  
Short term debt 0 0 0 0 0 0 0  
Long term liabilities        
Long term debt 150 000 150 000 75 000 100 000 50 000 0 0  
TOTAL LIABILITIES 150 000 150 000 75 000 100 000 50 000 0 0  
EQUITY AND LIABILITIES   497 946 407 476 120 499 -53 207 118 658 497 915  
check   0 0 0 0 0 0  
       
working capital   2 808 4 148 4 576 6 029 8 237 8 962  
CASH FLOW STATEMENT   2010E 2011E 2012E 2013E 2014E 2015E    
Net income   -69 099 110 421 -293 784 -107 566 236 497 396 854  
DD&A   17 143 17 143 38 094 38 094 38 094 38 094  
Change in inventories   2 808 1 340 427 1 453 2 208 725  
       
       
CFO   -49 148 128 904 -255 264 -68 020 276 798 435 672  
Maintenance capex   -8 571 -8 571 -19 047 -19 047 -19 047 -19 047   To fix different malfunctions. For us it is equal to 50% of depresiation  
Purchases of PP&E   -120 000 0 -146 655 0 0 0  
CFI   -128 571 -8 571 -165 702 -19 047 -19 047 -19 047  
Change in ST debt         This is one of the most important rows in Cash Flow Statement, because here you can  
Change in LT debt   0 -75 000 25 000 -50 000 -50 000 0 add some cash from debt if your company does not generate enough operating cash flows  
CFF   0 -75 000 25 000 -50 000 -50 000 0  
Change in cash   -177 719 45 333 -395 965 -137 066 207 751 416 625  
Cash at the beginning   450 000 272 281 317 613 -78 352 -215 418 -7 667  
Cash at the end   272 281 317 613 -78 352 -215 418 -7 667 408 958   Cash at the end of the year must be positive.  
      Regulate it with debt or shareholders' capital additions  
     
     
DEPRECIATION   2010E 2011E 2012E 2013E 2014E 2015E  
       
Cost of 1 machine 120000 120 000 132 960 146 655 161 027 176 003 191 491   Usually an asset has a salvage value, but not nessesarily.  
Useful life 7       We assume that after 7 years we'll just throw the machine away  
       
Capex   120 000 0 146 655 0 0 0  
         
Depreciation 2009 0 0 0 0 0 0   Do not use this simple formula in complex models. If machine's useful life was less than 6 years,  
  2010 17 143 17 143 17 143 17 143 17 143 17 143   for example 3 years, we would stop depreciating the asset in 2013  
  2011   0 0 0 0 0  
  2012     20 951 20 951 20 951 20 951  
  2013     0 0 0  
  2014     0 0  
  2015           0  
       
Total DD&A   17 143 17 143 38 094 38 094 38 094 38 094  
       
Accumulated DD&A   17 143 34 286 72 379 110 473 148 566 186 660  
     
     
VALUATION  
    2010E 2011E 2012E 2013E 2014E 2015E  
EBIT   -54 849 143 496 -284 901 -94 824 303 160 496 335  
Tax   0 -28 699 0 0 -60 632 -99 267  
Fully taxed EBIT   -54 849 114 797 -284 901 -94 824 242 528 397 068  
DD&A   17 143 17 143 38 094 38 094 38 094 38 094  
Capex   -128 571 -8 571 -165 702 -19 047 -19 047 -19 047    
Change in working capital   -2 808 -1 340 -427 -1 453 -2 208 -725  
Free cash flows   -169 085 122 028 -412 936 -77 230 259 367 415 390 2 622 151  
Terminal value     2 622 151  
     
Net Debt -150 000 -122 281 -242 613 178 352 265 418 7 667 -408 958    
     
WACC   2010 2011 2012 2013 2014 2015 2016  
Share of equity             67%  
Cost of equity (for us it is normal rate of return)   15.5%   5% above inflation rate  
Share of debt     33%  
Cost of debt     20%   Cost of credit in Russian bank for small business  
Terminal growth rate     1%   Just a guess  
WACC             17.0%  
     
     
NPV (at the beginning of the year)   1 244 095 1 724 221 1 433 101 2 032 918 2 758 617 3 386 144  
 
01.01.2011  
02.06.2011 Later this sell should be changed to =TODAY()   151  
31.12.2011           209  
 
Current fair value of business, RUB 1 555 210  
 
Note: we determined a fair value of the business as of 02.06.2011  
Well, I hope that everything is correct and I've calculated the cost of t-shirt business without mistakes  
Let's imagine that we want to sell our business in 2014  
It will worth RUB 2 758 617 in 2014 money or RUB 1 722 399 in the beginning of 2011