1 | Financial Modeling. Brandon Peay | ||||||
|---|---|---|---|---|---|---|---|
2 | |||||||
3 | 2010 | % of sales | 2011 | 2012 | 2013 | 2014 | |
4 | |||||||
5 | Sales | $ 1,000.00 | 100% | $ 1,250.00 | $ 1,562.50 | $ 1,953.13 | $ 2,441.41 |
6 | COGS @ 60% | $ 600.00 | 60% | $ 750.00 | $ 937.50 | $ 1,171.88 | $ 1,464.84 |
7 | Gross Profit | $ 400.00 | 40% | $ 500.00 | $ 625.00 | $ 781.25 | $ 976.56 |
8 | Operating Expenses | $ 150.00 | 15% | $ 187.50 | $ 234.38 | $ 292.97 | $ 366.21 |
9 | EBIT | $ 250.00 | $ 312.50 | $ 390.63 | $ 488.28 | $ 610.35 | |
10 | Interest | $ 50.04 | $ 81.93 | $ 120.51 | $ 170.15 | $ 232.68 | |
11 | EBT | $ 199.96 | $ 230.57 | $ 270.11 | $ 318.13 | $ 377.67 | |
12 | Taxes @ 34% | $ 67.99 | $ 78.39 | $ 91.84 | $ 108.16 | $ 128.41 | |
13 | Net income | $ 131.97 | $ 152.18 | $ 178.28 | $ 209.96 | $ 249.26 | |
14 | Dividends | $ 43.99 | 33% of NI | $ 50.73 | $ 59.43 | $ 69.99 | $ 83.09 |
15 | Retained earnings | $ 87.98 | $ 101.45 | $ 118.85 | $ 139.98 | $ 166.17 | |
16 | |||||||
17 | |||||||
18 | |||||||
19 | Cash | $ 160.00 | 16% | $ 200.00 | $ 250.00 | $ 312.50 | $ 390.63 |
20 | Accounts Receivable | $ 440.00 | 44% | $ 550.00 | $ 687.50 | $ 859.38 | $ 1,074.22 |
21 | Inventory | $ 600.00 | 60% | $ 750.00 | $ 937.50 | $ 1,171.88 | $ 1,464.84 |
22 | Net Fixed Assets | $ 1,800.00 | 180% | $ 2,250.00 | $ 2,812.50 | $ 3,515.63 | $ 4,394.53 |
23 | Total Assets | $ 3,000.00 | 300% | $ 3,750.00 | $ 4,687.50 | $ 5,859.38 | $ 7,324.22 |
24 | |||||||
25 | |||||||
26 | Accounts Payable | $ 300.00 | 30% | $ 375.00 | $ 468.75 | $ 585.94 | $ 732.42 |
27 | Notes Payable | $ 100.00 | $ 100.00 | $ 100.00 | $ 100.00 | $ 100.00 | |
28 | Long Term Debt | $ 800.00 | $ 800.00 | $ 800.00 | $ 800.00 | $ 800.00 | |
29 | Owners Equity | $ 1,800.00 | $ 1,901.45 | $ 2,020.30 | $ 2,160.28 | $ 2,326.45 | |
30 | Liabilities + Owners equity | $ 3,000.00 | $ 3,176.45 | $ 3,389.05 | $ 3,646.21 | $ 3,958.87 | |
31 | External financing needed | #REF! | $ 573.55 | $ 1,267.45 | $ 2,160.32 | $ 3,284.98 |