1 2 3 4 5 6 7 8 9 10 11 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, 2023: 1) your shark funding has been received, 2) new capital investments have been purchased and set up, and 3) additional labor has been 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 2020, 2021, and 2022, 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 2020-2022, increasing to $200,000 per year in 2023. 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 (Wyoming), you don't have to pay state taxes on your LLC's income. You do, however, still have to pay Federal taxes. Also, in 2022, higher tax rates were passed for the 2023 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 2020-2022 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 sharks 12 13 14 15 16 17 18 19 20 21 INVENTORY: Compute a 3-year average of inventory as a Percent-of-Sales, and then use that figure to forecast inventory levels through 2027. PLANT & EQUIPMENT: There is a new capital expenditure of $750,000 dollars in 2023, 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 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 2023. 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 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.) DIVIDENDS: You do not pay dividends now and do not plan to while in a growth stage. MILESTONE 2 FORECASTED FINANCIALS Fill in the yellow highlighted cells with your forecasted figures. SHOW ALL YOUR SUPPORTING CALCULATIONS! You may do this either within the cell by using formulas, out to the right, or both -- clearly labeling your work. All your work must be shown on this sheet, not on a separate tab. Forecasted Income Statement (20 points: 5 for showing work, 15 for accuracy) DURA-CLEAR WINDOWS, LLC Proforma Income Statement 2020 2021 2022 2023 2024 2025 2026 2027 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 Selling and Administrative Expense (224,900) (270,500) 1,875,000 5,625,000 (1,105,000) 3,093,750 770,000 8,718,750 (363,700) 7,031,250 8,789,063 3,867,188 4,833,984 6,042,480 7,553,101 10,898,438 Rent Expense (15,000) (15,000) (15,000) Depreciation Expense (65,000) (76,500) (139,000) Operating profit (EBIT) 95,100 98,000 252,300 Interest expense (35,000) (45,000) (85,000) Net Income before Taxes 60,100 53,000 167,300 Taxes (36,900) (49,200) (55,600) Net Income 23,200 3,800 111,700 Shares 60,000 60,000 78,000 Earnings per Share $0.39 $0.26 $2.38 Forecasted Balance Sheet (30 points: 5 for showing work, 25 for accuracy) DURA-CLEAR WINDOWS, LLC Proforma Balance Sheet 2020 2021 2022 2023 2024 2025 2026 2027 ASSETS Cash 30,000 40,000 30,000 Marketable Securities 20,000 25,000 30,000 Accounts Receivable 170,000 259,000 360,000 Inventory 230,000 261,000 290,000 Total Current Assets: 450,000 585,000 710,000 Plant and equipment 650,000 765,000 1,390,000 Less: accumulated depreciation (65,000) (141,500) (280,500) Net Plant and equipment 585,000 623,500 1,109,500 Total Assets 1,035,000 1,208,500 1,819,500 LIABILITIES & STOCKHOLDER'S EQUITY Accounts Payable 200,000 310,000 505,000 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 Total Liabilities 545,400 703,600 1,243,900 Common Stock ($1 par) 60,000 60,000 78,000 Capital paid in excess of par 190,000 190,000 262,000 Retained Earnings 239,600 254,900 235,600 Total Stockholder's Equity 489.600 504,900 575,600 Total Liabilities & Stockholder's Equit 1,035,000 1,208,500 1,819,500 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 15 points: 5 for showing work, 10 for accuracy Based upon your financial forecast for the years 2023 - 2027, 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) 2023 2024 2025 2026 2027 2 15 points: 5 for showing work, 10 for accuracy Compute Required New Funds (RNF) for each year (HINT: See Formula 4-1 in your Week 3 Chapter readings) OPTIONAL: It would be very helpful to fill out the table below identifying the necessary variables before attempting to compute RNF. A/S = Total Current Assets/Sales AS = Current Sales - Last year's Sales L/S = Total Current Liabilities/Sales P =Profit Margin (see 1a above!) $2 = Sales in the current yr D = Dividend Payout Ratio = Dividends 2023 2024 2025 2026 2027 1 Ratio Analysis (HINT: See Week 2 Chapter readings) (5 points: 1 pt for grammar/spelling, 4 pts for thought-out, correct answers) a. Are your profitability ratios better or worse than the Industry Averages shown on your Milestone 2 Metrics worksheet? b. Are the sharks likely to challenge your figures? Why or why not? (5 points: 1 pt for grammar/spelling, 4 pts for thought-out, correct answers) c. Are your liquidity ratios better or worse than the Industry Averages shown on your Milestone 2 Metrics worksheet? d. Regardless of your answer to question 1c, what can happen to a company with poor liquidity? Required New (5 points: 1 pt for grammar/spelling, 4 pts for thought-out, correct answers) e. Are your debt utilization ratios better or worse than the Industry Averages shown on your Milestone 2 Metrics worksheet? f. What is the difference between what a debt utilization ratio measures and what a liquidity ratio measures? 2 Funds (RNF) (5 points: 1 pt for grammar/spelling, 4 pts for thought-out, correct answers) a. You asked the sharks for $1M. Looking at your RNF results, is it enough? How do you know? b. Regardless of your answer to 2a, where you could pull funds from if you needed extra funds? (HINT: Look at your Assumptions to see where you could change a decision and free up funds.)