S T U V W X Y AutoSave OFF Home Insert Draw 35= Page Layout Formulas Data Review View Shape Format Module 05 Assignment Template Tell me Share Comments G X Calibri (Body) 11 ' ab Wrap Text General $ Paste B U I Merge & Center %9 .00 0 .000 Conditional Format Formatting as Table Cell Styles Insert Delete Format Sort & Find & Ideas Filter Select Yes No O fx Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? TextBox 1 A B C D E F G H J K L M N 1 Breakeven 2 3 4 5 Your client, SmartClean, Inc., is a cleaning service for office and industrial locations. SmartClean has been in business for 5 years and has shown steady revenue growth each year. The owner originally started the business using a business loan. The owner has $10,000 remaining on the loan after steadily making payments and has an excellent personal and business credit history. 6 7 8 The owner wishes to expand the SmartClean business into three new territories, needs an infusion of capital, and is looking for $50,000 to make the expansion. 9 10 11 The expected fixed costs for the current business and expansion are $75,000. SmartClean's average charge per job is $250.00. The variable cost per job is $35.00. 12 14 15 16 23456 13 Conduct breakeven analysis (based on given price and costs) in this spreadsheet. 1) Complete the table below. 2) Calculate the BEQ. 17 18 3) Referring to the table you completed in part 1), what is the minimum quantity (in buckets of 50 jobs) you recommend to the owner to cover the remaining $10,000 loan? 19 20 21 23 2222222222 24 25 Quantity Total Revenue Variable Costs Fixed Costs Total Costs Profit/ (Loss) 26 0 Variable Fixed Costs Costs $35 $75,000 Price BEQ $250 27 50 28 100 29 150 30 200 Minimum Quantity 31 250 32 300 33 350 34 400 35 450 36 500 37 550 38 600 39 650 40 41 42 43 44 45 46 47 Ready Breakeven Leverage + P Q R |B 100% 35= Module 05 Assignment Template Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments G X Calibri (Body) 11 A A Wrap Text General M Paste B I U A Merge & Center $ % 9 0 .00 .00 0 Conditional Format Cell Insert Delete Format Formatting as Table Styles Sort & Filter Find & Select Ideas Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? Yes No AutoSave OFF J16 A fx B C D E F H 1 2 A firm needs $80 million (M) to fund startup operations. Sales of $50M are expected with $35.28M in expenses. The firm's tax rate is 33%. 3 4 1) What are net earnings and the return on equity if the owners provide the needed $80M? 5 2) What are net earnings and the return on equity if $40M of the needed $80M is borrowed at an interest rate of 10%? 6 7 8 9 10 a) Owner Investment (No Financial Leverage) $50,000,000 $35,280,000 b) Borrowed Funds (with Financial $50,000,000 $35,280,000 Tax 33% To Start $80,000,000 Borrowed $40,000,000 11 Sales 12 Expenses Earnings before Interest and Taxes 13 (EBIT) 14 Interest Earnings before 15 Taxes (EBT) 16 Taxes 17 Net Earnings 18 Return of Equity Rate 10% Interest under the "Borrowed Funds" scenario is equal to 10% of the $40M of borrowed funds. Taxes are equal to 33% of Earnings before Taxes (EBT). Return on equity is calculated by dividing net earnings by owner's equity. When financial leverage is used, owner's equity is only the contribution from the owners. Borrowed funds are not considered equity. 19 20 21 Note: 22 23 . 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 Ready Breakeven Leverage + J K L M N O P d R S T U V W X |B 100%