Excel question
babayeluo123
Pear-BaselineValuation.xlsxSheet1
| Last Yr | Model Yr 1 | Model Yr 2 | Model Yr 3 | Model Yr 4 | Model Yr 5 | Model Yr 6 | Model Yr 7 | Model Yr 8 | Model Yr 9 | Model Yr 10 | Model Yr 11 | Model Yr 12 | Model Yr 13 | Model Yr 14 | Model Yr 15 | Model Yr 16 | Model Yr 17 | Model Yr 18 | Model Yr 19 | Model Yr 20 | ||
| Baseline Assumptions | BASELINE ASSUMPTIONS | |||||||||||||||||||||
| # Salespeople Hired (Assume Beg of Yr) | 225 | 305 | 385 | 465 | 545 | 625 | 705 | 785 | 865 | 945 | 1,025 | 1,105 | 1,185 | 1,265 | 1,345 | 1,425 | 1,505 | 1,585 | 1,665 | 1,745 | 1,825 | |
| Average Retention of Salespeople | 87.50% | 86.00% | 84.50% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | |
| Average Price per Unit | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | |
| Average Units Sold per Salesperson | 685 | 700 | 715 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | |
| Net Earned Rate of Assets | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | |
| Cost of Goods Sold as % of Sales | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | |
| Additional One-Time R&D Spending | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | ||
| Inflation on R&D and Fixed Expenses | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | |
| Additional One-Time SG&A Expenses | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | ||
| Int Rate on Debt | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | |
| Tax Rate | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | |
| Discount Rate | 10.50% | |||||||||||||||||||||
| Shock to Assumptions | SHOCK TO ASSUMPTIONS | |||||||||||||||||||||
| # Salespeople Hired | ||||||||||||||||||||||
| Average Retention of Salespeople | ||||||||||||||||||||||
| Average Price per Unit | ||||||||||||||||||||||
| Average Units Sold per Salesperson | ||||||||||||||||||||||
| Net Earned Rate of Assets | ||||||||||||||||||||||
| Cost of Goods Sold as % of Sales | ||||||||||||||||||||||
| Additional One-Time R&D Spending | ||||||||||||||||||||||
| Inflation on R&D and Fixed Expenses | ||||||||||||||||||||||
| Additional One-Time SG&A Expenses | ||||||||||||||||||||||
| Int Rate on Debt | ||||||||||||||||||||||
| Tax Rate | ||||||||||||||||||||||
| Discount Rate | ||||||||||||||||||||||
| Assumptions for Risk Scenario(s) Run | ASSUMPTIONS FOR RISK SCENARIO(S) RUN | |||||||||||||||||||||
| # Salespeople Hired | 305 | 385 | 465 | 545 | 625 | 705 | 785 | 865 | 945 | 1,025 | 1,105 | 1,185 | 1,265 | 1,345 | 1,425 | 1,505 | 1,585 | 1,665 | 1,745 | 1,825 | ||
| Average Retention of Salespeople | 86.00% | 84.50% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | 82.00% | ||
| Average Price per Unit | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | 300 | ||
| Average Units Sold per Salesperson | 700 | 715 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | 730 | ||
| Net Earned Rate of Assets | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | 4.00% | ||
| Cost of Goods Sold as % of Sales | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | 75.60% | ||
| Additional One-Time R&D Spending | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | ||
| Inflation on R&D and Fixed Expenses | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | 2.25% | ||
| Additional One-Time SG&A Expenses | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | ||
| Int Rate on Debt | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | 5.00% | ||
| Tax Rate | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | 22.50% | ||
| Discount Rate | 10.00% | |||||||||||||||||||||
| PROJECTION (millions) | ||||||||||||||||||||||
| Salespeople (End of Yr) | 1,000 | 1,122 | 1,274 | 1,426 | 1,616 | 1,838 | 2,085 | 2,353 | 2,639 | 2,939 | 3,250 | 3,571 | 3,900 | 4,236 | 4,576 | 4,921 | 5,269 | 5,620 | 5,974 | 6,330 | 6,687 | |
| Net Growth in Salespeople | 12.2% | 13.5% | 11.9% | 13.3% | 13.7% | 13.5% | 12.9% | 12.1% | 11.4% | 10.6% | 9.9% | 9.2% | 8.6% | 8.0% | 7.5% | 7.1% | 6.7% | 6.3% | 6.0% | 5.6% | ||
| INCOME STATEMENT | REVENUE | 18.8% | 15.3% | 15.0% | 12.7% | 13.5% | 13.6% | 13.1% | 12.5% | 11.7% | 11.0% | 10.2% | 9.5% | 8.9% | 8.3% | 7.8% | 7.3% | 6.9% | 6.5% | 6.1% | 5.8% | |
| Sales | 188 | 223 | 257 | 296 | 333 | 378 | 430 | 486 | 547 | 611 | 678 | 747 | 818 | 891 | 965 | 1,040 | 1,116 | 1,192 | 1,270 | 1,347 | 1,425 | |
| Net Investment Income | 6 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | |
| Total Revenues | 194 | 230 | 264 | 303 | 340 | 385 | 437 | 493 | 554 | 618 | 685 | 754 | 825 | 898 | 972 | 1,047 | 1,123 | 1,200 | 1,277 | 1,354 | 1,432 | |
| Revenue Growth | 18.9% | 14.8% | 14.6% | 12.4% | 13.3% | 13.3% | 12.9% | 12.3% | 11.6% | 10.8% | 10.1% | 9.4% | 8.8% | 8.2% | 7.7% | 7.2% | 6.8% | 6.4% | 6.1% | 5.8% | ||
| EXPENSES | ||||||||||||||||||||||
| Cost of Goods Sold (CGS) | 136 | 168 | 194 | 223 | 252 | 286 | 325 | 367 | 413 | 462 | 512 | 565 | 619 | 673 | 729 | 786 | 844 | 901 | 960 | 1,019 | 1,078 | |
| R&D | 10 | 10 | 10 | 11 | 11 | 11 | 11 | 12 | 12 | 12 | 12 | 13 | 13 | 13 | 14 | 14 | 14 | 15 | 15 | 15 | 16 | |
| Additional R&D Expenses | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | |
| Selling, General & Admin. Expenses (SG&A) | 18 | 19 | 19 | 20 | 20 | 21 | 21 | 21 | 22 | 22 | 23 | 23 | 24 | 25 | 25 | 26 | 26 | 27 | 27 | 28 | 29 | |
| Additional SG&A Expenses | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | |
| Interest on Debt | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | |
| Total Expenses | 170 | 203 | 230 | 260 | 289 | 324 | 363 | 407 | 453 | 502 | 554 | 607 | 662 | 717 | 774 | 832 | 890 | 949 | 1,008 | 1,068 | 1,128 | |
| Earnings Before Income Tax (EBT) | 23 | 27 | 34 | 43 | 51 | 62 | 74 | 87 | 101 | 116 | 131 | 147 | 164 | 181 | 198 | 215 | 233 | 251 | 269 | 287 | 305 | |
| Pre-Tax Profit Margin | 12.1% | 11.6% | 13.0% | 14.2% | 15.1% | 16.0% | 16.9% | 17.6% | 18.2% | 18.7% | 19.1% | 19.5% | 19.8% | 20.1% | 20.4% | 20.6% | 20.7% | 20.9% | 21.0% | 21.2% | 21.3% | |
| INCOME TAX (Assume GAAP=TAX Amounts) | 7 | 6 | 8 | 10 | 12 | 14 | 17 | 19 | 23 | 26 | 30 | 33 | 37 | 41 | 45 | 48 | 52 | 56 | 60 | 64 | 69 | |
| Net Income (Assume = shareholder dividends) : For simplicity, assume any negative net income is funded by a call on shareholders, rather than reducing assets and equity | 16 | 21 | 27 | 33 | 40 | 48 | 57 | 67 | 78 | 90 | 102 | 114 | 127 | 140 | 153 | 167 | 181 | 194 | 208 | 222 | 236 | |
| Post-Tax Profit Margin | 8.5% | 9.0% | 10.1% | 11.0% | 11.7% | 12.4% | 13.1% | 13.6% | 14.1% | 14.5% | 14.8% | 15.1% | 15.4% | 15.6% | 15.8% | 15.9% | 16.1% | 16.2% | 16.3% | 16.4% | 16.5% | |
| Shares Outstanding | 105 | 105 | 105 | 105 | 105 | 105 | 105 | 105 | 105 | 105 | 105 | 105 | 105 | 105 | 105 | 105 | 105 | 105 | 105 | 105 | 105 | |
| eps | 0.16 | 0.20 | 0.25 | 0.32 | 0.38 | 0.46 | 0.54 | 0.64 | 0.74 | 0.85 | 0.97 | 1.09 | 1.21 | 1.33 | 1.46 | 1.59 | 1.72 | 1.85 | 1.98 | 2.12 | 2.25 | |
| VALUATION & ANALYSIS | FREE CASH FLOW (Assume no non-cash items) | 16 | 21 | 27 | 33 | 40 | 48 | 57 | 67 | 78 | 90 | 102 | 114 | 127 | 140 | 153 | 167 | 181 | 194 | 208 | 222 | 236 |
| 5-Year | 5-Year | |||||||||||||||||||||
| Company
Value : Assume company closes with terminal value equal to equity after 20 years |
: For simplicity, assume any negative net income is funded by a call on shareholders, rather than reducing assets and equity | Rev CAGR | Net Income CAGR | |||||||||||||||||||
| Baseline | 696.9 | 14.8% | 23.9% | |||||||||||||||||||
| Scenario Run | 696.9 | 14.8% | 23.9% | |||||||||||||||||||
| % Change | 0.0% | 0.0% | 0.0% | |||||||||||||||||||
| Absolute Value of Change | - 0 | 0.0% | 0.0% | |||||||||||||||||||
| Actual Market Valuation | ||||||||||||||||||||||
| Date | Today | |||||||||||||||||||||
| Stock Price | $ 6.50 | |||||||||||||||||||||
| Market Capitalization | 682.5 | |||||||||||||||||||||
| P/E ratio | 33.1 | |||||||||||||||||||||
| "Under-Valuation" | 2.1% | |||||||||||||||||||||
| BALANCE SHEET | ASSETS | |||||||||||||||||||||
| Invested Assets | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | |
| Other Assets (Non-depreciating) | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | |
| Total Assets | 220 | 220 | 220 | 220 | 220 | 220 | 220 | 220 | 220 | 220 | 220 | 220 | 220 | 220 | 220 | 220 | 220 | 220 | 220 | 220 | 220 | |
| LIABILITIES | ||||||||||||||||||||||
| Debt | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | |
| Other | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | |
| Total Liabilities | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | 180 | |
| EQUITY | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 |
