5 I . 1 2 im 4 5 5 7 B SCENARIO & REQUIREMENTS SCENARIO: You have invented the first maintenance-free jet wax called "Jetwax 100." Jetwax 100 can last up to 1 year on any plane surface. In order to start your business, Clean Jet Products, you have invested all of your savings, the savings of your parents, and the savings of some of your friends. Three years have passed since you began selling the engines primarily to small aviation companies that operate single-engine planes, but now the big aviation companies are requesting your products. Sadly, you are already operating at full capacity. There is a need to switch from small-and-medium sized companies to big commercial corporations, but this would require a large infusion of funding. Space, equipment, employees, etc., all need to be increased. You applied to the TV show Shark Tank-where entrepreneurs compete for funding from angel investors (or "sharks")-to see if a shark would invest in your business and also serve as a mentor. It was a pleasant surprise to discover that you had been accepted to the show! After preparing most of your marketing and sales pitches, it is time to prepare your forecasted financial statements. Your objective is to convince the sharks that investing in your business would be very profitable. In addition, you need to decide how much ownership you're willing to give up in exchange for funding. CLEAN JET PRODUCTS, LLC Pro forma Income Statement 20X0 20X1 20X2 20X3 20X4 20X5 20X6 20X7 Sales (all on credit) 1,200,000 Cost of Goods Sold (800,000) Gross Profit 400,000 1,500,000 (1,040,000) 460,000 1,875,000 5,625,000 (1,105,000) 3,093,750 770,000 8,718,750 Selling and Administrative Expense (224,900) (270,500) (363,700) #REF! Rent Expense (15,000) (15,000) (15,000) (200,000) (200,000) 7,031,250 8,789,063 10,986,328 13,732,910 3,867,188 4,833,984 6,042,480 7,553,101 10,898,438 13,623,047 17,028,809 21,286,011 #REF! #REF! #REF! (200,000) #REF! (200,000) (200,000) Depreciation Expense (65,000) (76,500) (139,000) #REF! #REF! #REF! #REF! #REF! Operating profit (EBIT) 95,100 98,000 252,300 #REF! #REF! #REF! #REF! #REF! Interest expense (35,000) (45,000) (85,000) #REF! #REF! #REF! #REF! #REF! Net Income before Taxes 60,100 53,000 167,300 #REF! #REF! #REF! #REF! #REF! Taxes (36,900) (49,200) (55,600) #REF! #REF! #REF! #REF! #REF! Net Income 23,200 3,800 111,700 #REF! #REF! #REF! #REF! #REF! Shares 60,000 60,000 78,000 104,000 104,000 104,000 104,000 104,000 Earnings per Share $0.39 $0.26 $2.38 #REF! #REF! #REF! #REF! #REF! 1 2 3 4 Forecasted Balance Sheet (30 points: 5 for showing work, 25 for accuracy) CLEAN JET PRODUCTS, LLC Pro forma Balance Sheet 20X0 20X1 20X2 5 ASSETS 5 Cash 7 Marketable Securities 30,000 40,000 30,000 20,000 25,000 30,000 8 Accounts Receivable 170,000 259,000 360,000 Inventory 230,000 261,000 290,000 Total Current Assets: 450,000 585,000 710,000 1 Plant and equipment 650,000 765,000 1,390,000 2 Less: accumulated depreciation (65,000) (141,500) (280,500) B Net Plant and equipment 585,000 623,500 1,109,500 4 Total Assets 1,035,000 1,208,500 1,819,500 5 5 LIABILITIES & STOCKHOLDER'S EQUITY 7 Accounts Payable 200,000 310,000 505,000 B Accrued Expenses 20,400 30,000 35,000 Total Current Liabilities 220,400 340,000 540,000 Long-term Liabilities 325,000 363,600 703,900 1 Total Liabilities 545,400 703,600 1,243,900 2 Common Stock ($1 par) 60,000 60,000 78,000 3 Capital paid in excess of par 190,000 190,000 262,000 5 4 Retained Earnings 6 Total Liabilities & Stockholder's Equity 239,600 254,900 235,600 Total Stockholder's Equity 489,600 504,900 575,600 1,035,000 1,208,500 1,819,500 20X3 20X4 20X5 20X6 20X7 MILESTONE 2 METRICS SHOW ALL YOUR SUPPORTING CALCULATIONS! You may do this either within the cell by using formulas, or to the side or below -- clearly labeling your work. All your work must be shown on this sheet, not on a separate tab. 1 30 points: 10 for showing work, 10 for accuracy Based upon your financial forecast for the years 20X3-20X7, compute the following ratios, placing your final results in the yellow highlighted area: Industry Averages a Profit Margin 12.20% b ROA 8.75% ROE 22.42% d Current ratio 2.33X e Quick ratio 1.45X f Debt-to-Total Assets 43.05% g Times Interest Earne 10.28X (HINT: Enter Interest Expense as a positive number for TIE) 20x3 20X4 20X5 20X6 20X7 ASSUMPTIONS 1 2 3 4 5 6 7 NOTE: You will use a combination of Proforma, Ratios, and the Percent-of-Sales methods to crea The Sharks gave you the $1,000,000 in funding you requested in exchange for 25% ownership of your company's profits. All the following are complete by March 31, 20X3: 1) your shark funding has been received, 2) new capital investments have been purchased and set up, and 3) additional labor has been hired and SELLING & ADMINISTRATIVE EXPENSE: Use a 3-year average Percent-of-Sales to forecast S&A expenses. (HINT: Find what percent S&A expense is for each of 20X0, 20X1, and 20X2, and average the 3 results together. Use the resulting average S&A Percent-of-Sales to forecast S&A into the future.) RENT EXPENSE: Rent expense is a fixed cost in the amount of $15,000 per year in 20X0-20X2, increasing to $200,000 per year in 20X3. DEPRECIATION EXPENSE: Depreciation expense is a fixed cost in the amount of 10% of Plant & Equipment each year. INTEREST EXPENSE: This is a Fixed cost, and is 10% of Long-term Liabilities. TAXES: Because you live in a business-friendly State (Florida), you don't have to pay state taxes on your LLC's income. You do, however, still have to pay Federal taxes. Also, in 202, higher tax rates were passed for the 20X3 tax year, pushing income over $400,000 into the 39.6% tax bracket. Because of this, use 36% as your effective tax rate. (NOTE: If the taxes shown for 20X0-20X2 seem high, it's because you had income from another job that threw your LLC income into a slightly higher tax bracket. However, you'll quit that job IF the 8 SHARES: Issued 26,000 $1-par shares to the sharks for a 25% ownership stake. 9 CASH: Increases to $50,000 in 20X3 and stays at that level. 10 MARKETABLE SECURITIES: Plan to keep Marketable Securities at 60% of Cash levels. 11 12 13 ACCOUNTS RECEIVABLE: Use a 3-year average Receivables Turnover ratio to forecast. INVENTORY: Compute a 3-year average of inventory as a Percent-of-Sales, and then use that figure to forecast inventory levels through 20x7. PLANT & EQUIPMENT: There is a new capital expenditure of $750,000 dollars in 20X3, paid for from the $1M in funding from the Sharks, rather than with new debt. (All capital expenditures are assumed to occur on January 1st of the year of purchase, and no equipment is sold or salvaged 14 15 16 17 18 19 20 ACCUMULATED DEPRECIATION: Each year, 10% of the total amount of Plant and Equipment is added to the depreciation amount. ACCOUNTS PAYABLE: Use the 3-year average Current Ratio to forecast. (HINT: The Current Ratio will help you forecast TOTAL Current Liabilities, not Accounts Payable. Find Total Current Liabilities and Accrued Expenses first, and then you can solve for Accounts Payable.) ACCRUED EXPENSES: Use the 3-year average Percent-of-Sales method to forecast. LONG-TERM LIABILITIES: Pay down $100,000 of old debt every year starting in 20X3. COMMON STOCK ($1 Par): Increase by the dollar value of shares issued to Sharks (26,000 shares at $1 par). CAPITAL PAID IN EXCESS OF PAR: The Shark's full investment of $1M must be reflected on the Balance Sheet. The stock received by the Shark, worth $26,000 (at Par value of $1 per share), is already reflected under Common Stock. The rest of the shark's investment value is added to this RETAINED EARNINGS: This amount is whatever it takes to make the Balance Sheet balance! (HINT: Remember Total Liabilities + Stockholder's Equity is equal to Total Assets. Once Total L + SE is determined, Total SE can be solved. Finally, once Total SE is solved, Retained Earnings can be solved.) 21 DIVIDENDS: You do not pay dividends now and do not plan to while in a growth stage.