Tricky Widgets Manufacturing
Appendix
Sales Forecast | |||||||||||||
Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 | ||
Unit Sales | |||||||||||||
Widget Basic | 0% | 22 | 22 | 23 | 23 | 24 | 24 | 25 | 25 | 26 | 26 | 27 | 27 |
Widget Deluxe | 0% | 25 | 26 | 26 | 27 | 27 | 28 | 28 | 29 | 29 | 30 | 30 | 31 |
Widget Premium | 0% | 27 | 28 | 28 | 29 | 29 | 30 | 30 | 31 | 32 | 32 | 33 | 34 |
Other | 0% | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Total Unit Sales | 74 | 75 | 77 | 79 | 80 | 82 | 83 | 85 | 87 | 88 | 90 | 92 | |
Unit Prices | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 | |
Widget Basic | $35,000.00 | $35,000.00 | $35,000.00 | $35,000.00 | $35,000.00 | $35,000.00 | $35,000.00 | $35,000.00 | $35,000.00 | $35,000.00 | $35,000.00 | $35,000.00 | |
Widget Deluxe | $50,000.00 | $50,000.00 | $50,000.00 | $50,000.00 | $50,000.00 | $50,000.00 | $50,000.00 | $50,000.00 | $50,000.00 | $50,000.00 | $50,000.00 | $50,000.00 | |
Widget Premium | $75,000.00 | $75,000.00 | $75,000.00 | $75,000.00 | $75,000.00 | $75,000.00 | $75,000.00 | $75,000.00 | $75,000.00 | $75,000.00 | $75,000.00 | $75,000.00 | |
Other | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $10.00 | $10.00 | $10.00 | $10.00 | $10.00 | $10.00 | $10.00 | |
Sales | |||||||||||||
Widget Basic | $770,000 | $785,400 | $801,108 | $817,130 | $833,473 | $850,142 | $867,145 | $884,488 | $902,178 | $920,221 | $938,626 | $957,398 | |
Widget Deluxe | $1,250,000 | $1,275,000 | $1,300,500 | $1,326,510 | $1,353,040 | $1,380,101 | $1,407,703 | $1,435,857 | $1,464,574 | $1,493,866 | $1,523,743 | $1,554,218 | |
Widget Premium | $2,025,000 | $2,065,500 | $2,106,810 | $2,148,946 | $2,191,925 | $2,235,764 | $2,280,479 | $2,326,088 | $2,372,610 | $2,420,062 | $2,468,464 | $2,517,833 | |
Other | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
Total Sales | $4,045,000 | $4,125,900 | $4,208,418 | $4,292,586 | $4,378,438 | $4,466,007 | $4,555,327 | $4,646,434 | $4,739,362 | $4,834,149 | $4,930,832 | $5,029,449 | |
Direct Unit Costs | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 | |
Widget Basic | 0.00% | $28,000.00 | $28,000.00 | $28,000.00 | $28,000.00 | $28,000.00 | $28,000.00 | $28,000.00 | $28,000.00 | $28,000.00 | $28,000.00 | $28,000.00 | $28,000.00 |
Widget Deluxe | 0.00% | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 | $40,000.00 |
Widget Premium | 0.00% | $60,000.00 | $60,000.00 | $60,000.00 | $60,000.00 | $60,000.00 | $60,000.00 | $60,000.00 | $60,000.00 | $60,000.00 | $60,000.00 | $60,000.00 | $60,000.00 |
Other | 0.00% | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 |
Direct Cost of Sales | |||||||||||||
Widget Basic | $616,000 | $628,320 | $640,886 | $653,704 | $666,778 | $680,114 | $693,716 | $707,590 | $721,742 | $736,177 | $750,901 | $765,919 | |
Widget Deluxe | $1,000,000 | $1,020,000 | $1,040,400 | $1,061,208 | $1,082,432 | $1,104,081 | $1,126,162 | $1,148,686 | $1,171,659 | $1,195,093 | $1,218,994 | $1,243,374 | |
Widget Premium | $1,620,000 | $1,652,400 | $1,685,448 | $1,719,157 | $1,753,540 | $1,788,611 | $1,824,383 | $1,860,871 | $1,898,088 | $1,936,050 | $1,974,771 | $2,014,266 | |
Other | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
Subtotal Direct Cost of Sales | $3,236,000 | $3,300,720 | $3,366,734 | $3,434,069 | $3,502,750 | $3,572,805 | $3,644,262 | $3,717,147 | $3,791,490 | $3,867,320 | $3,944,666 | $4,023,559 |
Personnel Plan | |||||||||||||
Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 | ||
Executives | 0% | $40,000 | $40,000 | $40,000 | $40,000 | $40,000 | $40,000 | $40,000 | $40,000 | $40,000 | $40,000 | $40,000 | $40,000 |
Managers | 0% | $80,000 | $80,000 | $80,000 | $80,000 | $80,000 | $80,000 | $80,000 | $80,000 | $80,000 | $80,000 | $80,000 | $80,000 |
Manufacturing Process Workers | 0% | $60,000 | $60,000 | $60,000 | $60,000 | $60,000 | $60,000 | $60,000 | $60,000 | $60,000 | $60,000 | $60,000 | $60,000 |
Engineering | 0% | $250,000 | $250,000 | $250,000 | $250,000 | $250,000 | $250,000 | $250,000 | $250,000 | $250,000 | $250,000 | $250,000 | $250,000 |
Total People | 75 | 75 | 75 | 75 | 85 | 85 | 85 | 100 | 100 | 100 | 100 | 115 | |
Total Payroll | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 |
Pro Forma Profit and Loss | |||||||||||||
Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 | ||
Sales | $4,045,000 | $4,125,900 | $4,208,418 | $4,292,586 | $4,378,438 | $4,466,007 | $4,555,327 | $4,646,434 | $4,739,362 | $4,834,149 | $4,930,832 | $5,029,449 | |
Direct Cost of Sales | $3,236,000 | $3,300,720 | $3,366,734 | $3,434,069 | $3,502,750 | $3,572,805 | $3,644,262 | $3,717,147 | $3,791,490 | $3,867,320 | $3,944,666 | $4,023,559 | |
Other | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
Total Cost of Sales | $3,236,000 | $3,300,720 | $3,366,734 | $3,434,069 | $3,502,750 | $3,572,805 | $3,644,262 | $3,717,147 | $3,791,490 | $3,867,320 | $3,944,666 | $4,023,559 | |
Gross Margin | $809,000 | $825,180 | $841,684 | $858,517 | $875,688 | $893,201 | $911,065 | $929,287 | $947,872 | $966,830 | $986,166 | $1,005,890 | |
Gross Margin % | 20.00% | 20.00% | 20.00% | 20.00% | 20.00% | 20.00% | 20.00% | 20.00% | 20.00% | 20.00% | 20.00% | 20.00% | |
Expenses | |||||||||||||
Payroll | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | |
Sales and Marketing and Other Expenses | $20,200 | $20,200 | $20,200 | $20,200 | $20,200 | $20,200 | $20,200 | $20,200 | $20,200 | $20,200 | $20,200 | $20,200 | |
Depreciation | $25,000 | $25,000 | $25,000 | $25,000 | $25,000 | $25,000 | $25,000 | $25,000 | $25,000 | $25,000 | $25,000 | $25,000 | |
Utilities | $1,000 | $1,000 | $1,000 | $1,000 | $1,000 | $1,000 | $1,000 | $1,000 | $1,000 | $1,000 | $1,000 | $1,000 | |
Payroll Taxes | 15% | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 |
Other | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
Total Operating Expenses | $476,200 | $476,200 | $476,200 | $476,200 | $476,200 | $476,200 | $476,200 | $476,200 | $476,200 | $476,200 | $476,200 | $476,200 | |
Profit Before Interest and Taxes | $332,800 | $348,980 | $365,484 | $382,317 | $399,488 | $417,001 | $434,865 | $453,087 | $471,672 | $490,630 | $509,966 | $529,690 | |
EBITDA | $357,800 | $373,980 | $390,484 | $407,317 | $424,488 | $442,001 | $459,865 | $478,087 | $496,672 | $515,630 | $534,966 | $554,690 | |
Interest Expense | $37,308 | $58,783 | $58,592 | $58,400 | $58,208 | $58,017 | $57,825 | $57,633 | $57,441 | $57,250 | $57,058 | $56,866 | |
Taxes Incurred | $88,648 | $87,059 | $92,068 | $97,175 | $102,384 | $107,695 | $113,112 | $118,636 | $124,269 | $130,014 | $135,873 | $141,847 | |
Net Profit | $206,844 | $203,138 | $214,824 | $226,742 | $238,896 | $251,289 | $263,928 | $276,818 | $289,962 | $303,366 | $317,036 | $330,976 | |
Net Profit/Sales | 5.11% | 4.92% | 5.10% | 5.28% | 5.46% | 5.63% | 5.79% | 5.96% | 6.12% | 6.28% | 6.43% | 6.58% |
Pro Forma Cash Flow | |||||||||||||
Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 | ||
Cash Received | |||||||||||||
Cash from Operations | |||||||||||||
Cash Sales | $2,022,500 | $2,062,950 | $2,104,209 | $2,146,293 | $2,189,219 | $2,233,003 | $2,277,663 | $2,323,217 | $2,369,681 | $2,417,075 | $2,465,416 | $2,514,725 | |
Cash from Receivables | $0 | $67,417 | $2,023,848 | $2,064,325 | $2,105,612 | $2,147,724 | $2,190,679 | $2,234,492 | $2,279,182 | $2,324,766 | $2,371,261 | $2,418,686 | |
Subtotal Cash from Operations | $2,022,500 | $2,130,367 | $4,128,057 | $4,210,618 | $4,294,831 | $4,380,727 | $4,468,342 | $4,557,709 | $4,648,863 | $4,741,840 | $4,836,677 | $4,933,411 | |
Additional Cash Received | |||||||||||||
Sales Tax, VAT, HST/GST Received | 0.00% | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 |
New Current Borrowing | $0 | $2,600,000 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
New Other Liabilities (interest-free) | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
New Long-term Liabilities | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
Sales of Other Current Assets | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
Sales of Long-term Assets | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
New Investment Received | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
Subtotal Cash Received | $2,022,500 | $4,730,367 | $4,128,057 | $4,210,618 | $4,294,831 | $4,380,727 | $4,468,342 | $4,557,709 | $4,648,863 | $4,741,840 | $4,836,677 | $4,933,411 | |
Expenditures | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 | |
Expenditures from Operations | |||||||||||||
Cash Spending | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | $430,000 | |
Bill Payments | $238,092 | $6,732,629 | $3,541,363 | $3,613,666 | $3,687,418 | $3,762,648 | $3,839,385 | $3,917,660 | $3,997,503 | $4,078,945 | $4,162,019 | $4,246,757 | |
Subtotal Spent on Operations | $668,092 | $7,162,629 | $3,971,363 | $4,043,666 | $4,117,418 | $4,192,648 | $4,269,385 | $4,347,660 | $4,427,503 | $4,508,945 | $4,592,019 | $4,676,757 | |
Additional Cash Spent | |||||||||||||
Sales Tax, VAT, HST/GST Paid Out | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
Principal Repayment of Current Borrowing | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
Other Liabilities Principal Repayment | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
Long-term Liabilities Principal Repayment | $23,003 | $23,003 | $23,003 | $23,003 | $23,003 | $23,003 | $23,003 | $23,003 | $23,003 | $23,003 | $23,003 | $23,003 | |
Purchase Other Current Assets | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
Purchase Long-term Assets | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
Dividends | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
Subtotal Cash Spent | $691,095 | $7,185,632 | $3,994,366 | $4,066,669 | $4,140,421 | $4,215,651 | $4,292,388 | $4,370,663 | $4,450,506 | $4,531,948 | $4,615,022 | $4,699,760 | |
Net Cash Flow | $1,331,405 | ($2,455,265) | $133,692 | $143,949 | $154,410 | $165,076 | $175,954 | $187,046 | $198,357 | $209,892 | $221,655 | $233,651 | |
Cash Balance | $3,331,405 | $876,140 | $1,009,831 | $1,153,781 | $1,308,190 | $1,473,266 | $1,649,220 | $1,836,266 | $2,034,623 | $2,244,515 | $2,466,171 | $2,699,822 |
Pro Forma Balance Sheet | |||||||||||||
Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 | ||
Assets | Starting Balances | ||||||||||||
Current Assets | |||||||||||||
Cash | $2,000,000 | $3,331,405 | $876,140 | $1,009,831 | $1,153,781 | $1,308,190 | $1,473,266 | $1,649,220 | $1,836,266 | $2,034,623 | $2,244,515 | $2,466,171 | $2,699,822 |
Accounts Receivable | $0 | $2,022,500 | $4,018,033 | $4,098,394 | $4,180,362 | $4,263,969 | $4,349,248 | $4,436,233 | $4,524,958 | $4,615,457 | $4,707,766 | $4,801,922 | $4,897,960 |
Inventory | $100,000 | $3,559,600 | $3,630,792 | $3,703,408 | $3,777,476 | $3,853,026 | $3,930,086 | $4,008,688 | $4,088,862 | $4,170,639 | $4,254,052 | $4,339,133 | $4,425,915 |
Other Current Assets | $100,000 | $100,000 | $100,000 | $100,000 | $100,000 | $100,000 | $100,000 | $100,000 | $100,000 | $100,000 | $100,000 | $100,000 | $100,000 |
Total Current Assets | $2,200,000 | $9,013,505 | $8,624,965 | $8,911,633 | $9,211,618 | $9,525,185 | $9,852,601 | $10,194,141 | $10,550,085 | $10,920,719 | $11,306,333 | $11,707,225 | $12,123,697 |
Long-term Assets | |||||||||||||
Long-term Assets | $2,500,000 | $2,500,000 | $2,500,000 | $2,500,000 | $2,500,000 | $2,500,000 | $2,500,000 | $2,500,000 | $2,500,000 | $2,500,000 | $2,500,000 | $2,500,000 | $2,500,000 |
Accumulated Depreciation | $0 | $25,000 | $50,000 | $75,000 | $100,000 | $125,000 | $150,000 | $175,000 | $200,000 | $225,000 | $250,000 | $275,000 | $300,000 |
Total Long-term Assets | $2,500,000 | $2,475,000 | $2,450,000 | $2,425,000 | $2,400,000 | $2,375,000 | $2,350,000 | $2,325,000 | $2,300,000 | $2,275,000 | $2,250,000 | $2,225,000 | $2,200,000 |
Total Assets | $4,700,000 | $11,488,505 | $11,074,965 | $11,336,633 | $11,611,618 | $11,900,185 | $12,202,601 | $12,519,141 | $12,850,085 | $13,195,719 | $13,556,333 | $13,932,225 | $14,323,697 |
Liabilities and Capital | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Month 7 | Month 8 | Month 9 | Month 10 | Month 11 | Month 12 | |
Current Liabilities | |||||||||||||
Accounts Payable | $10,000 | $6,614,664 | $3,420,989 | $3,490,836 | $3,562,082 | $3,634,756 | $3,708,885 | $3,784,500 | $3,861,630 | $3,940,305 | $4,020,556 | $4,102,415 | $4,185,913 |
Current Borrowing | $0 | $0 | $2,600,000 | $2,600,000 | $2,600,000 | $2,600,000 | $2,600,000 | $2,600,000 | $2,600,000 | $2,600,000 | $2,600,000 | $2,600,000 | $2,600,000 |
Other Current Liabilities | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 |
Subtotal Current Liabilities | $10,000 | $6,614,664 | $6,020,989 | $6,090,836 | $6,162,082 | $6,234,756 | $6,308,885 | $6,384,500 | $6,461,630 | $6,540,305 | $6,620,556 | $6,702,415 | $6,785,913 |
Long-term Liabilities | $4,500,000 | $4,476,997 | $4,453,994 | $4,430,991 | $4,407,988 | $4,384,985 | $4,361,982 | $4,338,979 | $4,315,976 | $4,292,973 | $4,269,970 | $4,246,967 | $4,223,964 |
Total Liabilities | $4,510,000 | $11,091,661 | $10,474,983 | $10,521,827 | $10,570,070 | $10,619,741 | $10,670,867 | $10,723,479 | $10,777,606 | $10,833,278 | $10,890,526 | $10,949,382 | $11,009,877 |
Paid-in Capital | $508,000 | $508,000 | $508,000 | $508,000 | $508,000 | $508,000 | $508,000 | $508,000 | $508,000 | $508,000 | $508,000 | $508,000 | $508,000 |
Retained Earnings | ($318,000) | ($318,000) | ($318,000) | ($318,000) | ($318,000) | ($318,000) | ($318,000) | ($318,000) | ($318,000) | ($318,000) | ($318,000) | ($318,000) | ($318,000) |
Earnings | $0 | $206,844 | $409,982 | $624,806 | $851,548 | $1,090,444 | $1,341,733 | $1,605,662 | $1,882,479 | $2,172,441 | $2,475,807 | $2,792,843 | $3,123,819 |
Total Capital | $190,000 | $396,844 | $599,982 | $814,806 | $1,041,548 | $1,280,444 | $1,531,733 | $1,795,662 | $2,072,479 | $2,362,441 | $2,665,807 | $2,982,843 | $3,313,819 |
Total Liabilities and Capital | $4,700,000 | $11,488,505 | $11,074,965 | $11,336,633 | $11,611,618 | $11,900,185 | $12,202,601 | $12,519,141 | $12,850,085 | $13,195,719 | $13,556,333 | $13,932,225 | $14,323,697 |
Net Worth | $190,000 | $396,844 | $599,982 | $814,806 | $1,041,548 | $1,280,444 | $1,531,733 | $1,795,662 | $2,072,479 | $2,362,441 | $2,665,807 | $2,982,843 | $3,313,819 |