PPT finance analysis

User Generated

ohuuwx

Business Finance

Description

finish ppt , start from dupont analysis to the end, which is silde 34. you need to make comment and explaination for each silde, I also provide company's data and a sample in the file.

Unformatted Attachment Preview

LOKAAL BRINGING YOU UNIQUE VENUES FOR EVERY OCCASION OUTLINE  Executive Summary  Demographics and Market  Day 0 Balance Sheet  Start-up Expenses + Day 1 Balance Sheet  12 Month Build Up  Income Statement Forecast  Balance Sheet Forecast  Free Cash Flow Forecast, HV, and Valuations  SWOT, Porter's 5 Forces, DuPont Analysis  Conclusions EXECUTIVE SUMMARY (BUSINESS MODEL CANVAS) DETAILED BUSINESS DESCRIPTION PRODUCT  Platform for hosts  Service charge per booking LOCATION  Web-based  Website  App  Android and Appstore MANAGEMENT  CEO – Phillip Warda  CFO – My Nguyen  COO – Joice Kim  CIO – Louis Lam  CMO – Irving Chen ORGANIZATION CHART  CEO  CFO  COO  CIO  CMO REVENUE MODEL + STRATEGY  Service Fee  Two Levels  Customer Level   8-12% Host Level  3%  Improve market awareness of Lokaal services  Turn your extra space to potential profit  Skip the event planner and come to us MARKET CUSTOMERS  Private/Commercial Real Estate Owners  Event Planners  Families  Part-time Artists  Companies  Interest Groups  Churches MARKET SIZE  2011 Data  1.8 million meetings and events take place annually in the United States, attracting approximately 205 million participants  organizers spend an average of $1,290 per attendee. Incentive meetings have the highest per-capita spend ($1,620), followed by conventions/congresses ($1,430), trade shows ($1,360), corporate meetings ($1,230) and other meetings ($850). COMPANY FINANCIALS DAY 0 BALANCE SHEET A ssets Cash L ia bilities $521,609 Notes Payable $500,000 Total Liabilities $500,000 Stockholder's Equity Total Assets $521,609 Louis Lam, Capital $4,322 My Nguyen, Capital 4,322 Irving Zhen, Capital 4,322 Joice Kim, Capital 4,322 Phillip Warda, Capital 4,322 Total Liabilities and Stockholder's Equity $521,609 START-UP EXPENSES  Incorporation Fee - $90  Rent Deposit - $759  Supplies - $240  Advertising Expenses - $4,000  Licensing Fees – $1,000  Prepaid Insurance Premiums - $4,225  Domain Name - $4,730  Salary Expense - $45,270 DAY 1 Day 1 Balance Sheet $600,000 $500,000 $505,330 $500,000 $400,000 $300,000 $200,000 $100,000 $Cash $4,984 $11,295 Prepaids Fixed Assets $21,609 Notes Payable Stockholder's Equity 12 MONTH BUILD UP OF UNIT SALES 12 MONTH BUILD UP OF EXPENSES 5 YEAR INCOME STATEMENT ASSUMPTIONS 5 YEAR FORECAST OF REVENUE + INCOME 5 YEAR BALANCE SHEET FORECAST COST OF EQUITY FREE CASH FLOW FORECAST SENSITIVITY VALUATION SENSITIVITY  Bar chart BREAK EVEN PERIOD BREAK EVEN SWOT + PORTER'S 5 FORCES SWOT PORTER'S 5 FORCES INCOME STATEMENT, BALANCE SHEET, DEBT MANAGEMENT DUPONT ANALYSIS DUPONT ANALYSIS INCOME STATEMENT PERFORMANCE ASSET MANAGEMENT DEBT MANAGEMENT CONCLUSION Profit Margin 61% 60% 59% 58% 57% 56% 55% 54% 53% 52% 51% Total Asset Turnover 2.5 2 1.5 1 0.5 0 Profit Margin 2014 2015 TATO 2014 2015 Equity Multiplier Return On Equity 1.2 120.00% 1 100.00% 0.8 80.00% 0.6 60.00% 0.4 40.00% 0.2 20.00% 0 0.00% Equity Multiplier 2014 2015 ROE 2014 2015 While Top Widget’s Profit Margin has increased and suggests that our differentiation strategy is working Total Asset Turnover is reasonable: We need to keep a fair bit of inventory to get the Widget to our customers as soon as it has been ordered We have chosen not to employ debt in the first 5 years Income Statement, Balance Sheet and Debt Management Margins 90% 80% 70% 60% 50% 40% 30% 20% 10% 0% Gross Margin Operating Margin 2013 2014 2015 Profit Margin Days Sales Outstanding 50 Inventory Turnover 14 45 12 40 10 35 30 8 25 6 20 15 4 10 2 5 0 0 DSO 2014 2015 Inventory Turnover 2014 2015 Fixed Asset Turnover 2.05 2 1.95 1.9 1.85 1.8 1.75 1.7 1.65 1.6 1.55 Fixed Asset Turnover 2014 2015 Assets = 50% Debt+50% Equity 250 Interest Coverage 6.2 6 200 5.8 5.6 150 5.4 5.2 100 5 4.8 50 4.6 0 4.4 Assets Debt 2014 2015 Equity Times Interest Earned 2014 2015 Margin performance suggest our assumptions are reasonable and support our strategy. Top Widgets Inc. represents an exciting opportunity for investors Management is asking $xxx in return for yy% of the company. Expected return to investors will come from an IPO or Strategic Acquisition by Mega Widgets or similar much larger company Etc. Last revised 11/2017. See revision notes on last tab. Step 1 - Enter info about your company in yellow shaded boxes below. Preparer Name Company Name Starting Month Starting Year Team 7 Lokaal January 2019 l Step 2 - Read the following instructions This Microsoft Excel Workbook is designed to provide those starting a business or already running a business with information that will allow them to make a "go /no-go" decision. It will help a potential entrepreneur project operating profit, develop a projected income statement, balance sheet and cash flow forecast. Color-Coding: Enter Data Here Adjust as Needed It is designed for a wide variety of users, from those who have little or no accounting or Excel experience to those who may be well versed in finance, accounting and the use of Microsoft Excel. The workbook contains a number of worksheets, each documented two ways. Extensive directions and guidance for a particular page or on a specific accounting topic are found in blue boxes (like this one) on pages that are not self-explanatory. Want a guide? Visit our website: The second way this workbook is documented is using Excel comments in a given cell. Comments are normally hidden from sight. If you see a red triangle in the upper right corner of a cell, you can hover your mouse over the triangle to see the note. As your mouse moves away from the triangle, the comment will disappear. www.score.org/getstarted Comments will have a beige background. Each comment may have a specific direction for that cell, may be a reminder of something the author believes important, or may have some additional information about the accounting topic. The cells and formulas in this workbook are protected. Cells with yellow or light blue backgrounds are designed for user input. All other cells are designed to generate data based on user input. Need to make the numbers bigger? Increase the The cells with formulas in this workbook are1locked. If changes are needed, the unlock code is "1234." Please use caution when unlocking the spreadsheets. If you want to change a formula, we strongly recommend that you save a copy of February April January this spreadsheet under a different name beforeMarch doing so. magnification. This will either be located in the lower right-hand corner, or in the Functions bar at the top of the page. May June July August Septembe October r November December Start-Up Expenses Year 1 (Starting Balance Sheet) Start-up Expenses Year 1 (Starting Balance Sheet) Prepared By: Team 7 Company Name: Lokaal Fixed Assets Real Estate-Land Real Estate-Buildings Leasehold Improvements Equipment Furniture and Fixtures Other Total Fixed Assets Amount 5,800 5,495 11,295 $ Operating Capital Pre-Opening Salaries and Wages Prepaid Insurance Premiums Inventory Legal and Accounting Fees Rent Deposits Utility Deposits Supplies Advertising and Promotions Licenses Other Initial Start-Up Costs Working Capital (Cash On Hand) Total Operating Capital Total Required Funds Depreciation (years) Not Depreciated 40 15 500 per7owner laptop/ 1500 per developer laptop/300 printer/copy 5 5 Amount Sources of Funding Notes 4,225 90 759 240 4,000 1,000 $50,000 450,000 510,314 521,609 $ $ Percentage Owner's Equity Outside Investors Additional Loans or Debt Commercial Loan Commercial Mortgage Credit Card Debt Vehicle Loans Other Bank Debt Total Sources of Funding Total Funding Needed Notes Workers Comp 90 to incorporate in Delaware Utilities included in rent Avg. $20/month Marketing 200 to Register Trademark./ 800 Tax Permit/ Etc Domain Name/AWS for year 0 10-12 month/Software engineer Totals 100.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 100.00% $ $ Loan Rate Term in Months Monthly Payments Notes 521,609 521,609 - 6.00% 84 6.00% 240 7.00% 60 6.00% 48 5.00% 36 Cell D 42 must equal cell C31 $ You are fully funded (Balanced) - See Loan Amortization & Depreciation Schedule Existing Businesses ONLY -- Calculating Cash on Hand Cash + Accounts Receivable + Prepaid Expenses - Accounts Payable - Accrued Expenses Total Cash on Hand $ - 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 1-StartingPoint 5/14/2019 11:27 AM Payroll Year 1 Payroll Year 1 Prepared By: Company Name: Team 7 Lokaal Average Hourly Employee Types Estimated Number of Owners Pay (to 2 decimal Hrs./Week /Employees places, ex. $15.23) (per person) Estimated Pay/Month (Total) January February March April May June July August September October November December Annual Totals Owner(s) 5 30.00 40 $ 26,000 26,000 26,000 26,000 26,000 26,000 26,000 26,000 26,000 26,000 26,000 26,000 26,000 $ 312,000 Full-Time Employees 2 36.00 40 $ 12,480 12,480 12,480 12,480 12,480 12,480 12,480 12,480 12,480 12,480 12,480 12,480 12,480 $ 149,760 Part-Time Employees 2 18.00 15 $ 2,340 2,340 2,340 2,340 2,340 2,340 2,340 2,340 2,340 2,340 2,340 2,340 2,340 $ 28,080 Independent Contractors(On-Call) 1 50.00 19 $ 4,117 4,117 4,117 4,117 4,117 4,117 4,117 4,117 4,117 4,117 4,117 4,117 4,117 $ 49,400 Support Staff 1 20.00 40 $ 3,467 3,467 3,467 3,467 3,467 3,467 3,467 3,467 3,467 3,467 3,467 3,467 3,467 $ 41,600 0 0.00 0 $ - - - - - - - - - - - - - $ - 114 $ 44,937 44,937 $ 539,240 Total Salaries and Wages 10 $ 134.00 $ 44,937 $ 44,937 $ 44,937 $ 44,937 $ 44,937 $ 44,937 $ 44,937 $ 44,937 $ 44,937 $ 44,937 $ 44,937 $ Estimated Taxes & Payroll Taxes and Benefits Social Security Wage Base Limit $ Medicare Percentage of Benefits/Month Salary/Wage (Total) 128,400 -- January February March April May June July August September October November December Annual Totals 6.20% $ 2,531 2,531 2,531 2,531 2,531 2,531 2,531 2,531 2,531 2,531 2,531 2,531 2,531 $ 30,370 1.45% $ 592 592 592 592 592 592 592 592 592 592 592 592 592 $ 7,103 Federal Unemployment Tax (FUTA) $ 7,000 0.60% $ 35 35 35 35 35 35 35 35 35 35 35 35 35 $ 420 State Unemployment Tax (SUTA) $ 7,000 3.45% $ 201 201 201 201 201 201 201 201 201 201 201 201 201 $ 2,415 Worker's Compensation -- 3.48% $ 1,421 1,421 1,421 1,421 1,421 1,421 1,421 1,421 1,421 1,421 1,421 1,421 1,421 $ 17,046 Employee Health Insurance -- 0.00% $ - - - - - - - - - - - - - $ - Other Employee Benefit Programs -- 0.00% $ - - - - - - - - - - - - - $ - $ 6,004 $ 6,004 $ 6,004 $ 6,004 $ 6,004 $ 6,004 $ 6,004 $ 6,004 $ 6,004 $ 6,004 $ 6,004 $ 6,004 $ 6,004 $ 72,049 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 611,289 Total Payroll Taxes and Benefits Total Salaries and Related Expenses 18.18% 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 2a-PayrollYear1 5/14/2019 11:27 AM Payroll Years 1-3 Payroll Years 1-3 Prepared By: Company Name: Team 7 Lokaal Employee Types Year 1 Totals Growth Rate 1 to 2 Second Year Growth Rate 2 to 3 Third Year Growth Rate 3 to 4 Four Year Growth Rate 4 to 6 Fifth Year Owner(s) 312,000 20.0% 374,400 20.0% 449,280 20.0% 539,136 30.0% 700,877 Full-Time Employees 149,760 20.0% 179,712 30.0% 233,626 40.0% 327,076 30.0% 425,199 Part-Time Employees 28,080 10.0% 30,888 30.0% 40,154 20.0% 48,185 20.0% 57,822 Independent Contractors 49,400 3.0% 50,882 10.0% 55,970 15.0% 64,366 3.0% Total Salaries and Wages $ 539,240 $ 635,882 $ 779,030 $ 978,763 66,297 $ 1,250,194 Payroll Taxes and Benefits Social Security 30,370 20.0% 36,444 30.0% 47,377 30.0% 61,591 30.0% 80,068 7,103 20.0% 8,523 30.0% 11,080 30.0% 14,404 30.0% 18,726 420 20.0% 504 30.0% 655 30.0% 852 30.0% 1,107 2,415 20.0% 2,898 30.0% 3,767 30.0% 4,898 30.0% 6,367 Employee Pension Programs 14,695 0.0% 14,695 0.0% 14,695 0.0% 14,695 0.0% 14,695 Worker's Compensation Medicare Federal Unemployment Tax (FUTA) State Unemployment Tax (SUTA) 17,046 3.0% 17,558 3.0% 18,085 3.0% 18,627 3.0% 19,186 Employee Health Insurance - 3.0% - 3.0% - 3.0% - 3.0% - Other Employee Benefit Programs - 10.0% - 10.0% - 10.0% - 10.0% Total Payroll Taxes and Benefits $ 72,049 $ 80,622 Total Salaries and Related Expenses $ 611,289 $ 716,504 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 2b-PayrollYrs1-5 $ 95,660 $ 874,690 $ 115,066 $1,093,829 $ 140,148 $1,390,343 5/14/2019 11:27 AM Sales Forecst Year 1 Sales Forecast Year 1 Prepared By: Company Name: Team 7 Lokaal Complete This Chart First: Product Lines Sales Price Per Units COGS Per Unit Unit 210.00 $ 0 $ - $ 0 0 $ - 0 0 $ 0 0 Margin Per Unit $ 209.50 - $ - $ - $ - - $ - $ - 0 $ - $ - $ - 0 $ - $ - $ - Reservation Service Charge 3,400 $ 0 0.50 Category Product Lines January February March April May June July August September October November December Annual Totals Breakdown Category / Total Reservation Service Charge 3400 Sold 50 100 200 200 300 450 600 300 200 200 200 600 Total Sales 10,500 21,000 42,000 42,000 63,000 94,500 126,000 63,000 42,000 42,000 42,000 126,000 $ 714,000 3,400 100.0% 100.0% 100.0% Total COGS 25 50 100 100 150 225 300 150 100 100 100 300 $ 1,700 0.2% 100.0% Total Margin 10,475 20,950 41,900 41,900 62,850 94,275 125,700 62,850 41,900 41,900 41,900 125,700 $ 712,300 99.8% 100.0% 0 Sold 0 0 0 0 0 0 0 0 0 0 0 0 Total Sales - - - - - - - - - - - - $ - 0.0% 0.0% Total COGS - - - - - - - - - - - - $ - 0.0% 0.0% Margin - - - - - - - - - - - - $ - 0.0% 0.0% 0 Sold 0 0 0 0 0 0 0 0 0 0 0 0 Total Sales - - - - - - - - - - - - $ - 0.0% 0.0% Total COGS - - - - - - - - - - - - $ - 0.0% 0.0% Margin - - - - - - - - - - - - $ - 0.0% 0.0% 0 Sold 0 0 0 0 0 0 0 0 0 0 0 0 Total Sales - - - - - - - - - - - - $ - 0.0% 0.0% Total COGS - - - - - - - - - - - - $ - 0.0% 0.0% Margin - - - - - - - - - - - - $ - 0.0% 0.0% 0 Sold 0 0 0 0 0 0 0 0 0 0 0 0 Total Sales - - - - - - - - - - - - $ - 0.0% 0.0% Total COGS - - - - - - - - - - - - $ - 0.0% 0.0% Margin - - - - - - - - - - - - $ - 0.0% 0.0% 0 Sold 0 0 0 0 0 0 0 0 0 0 0 0 Total Sales - - - - - - - - - - - - $ - 0.0% 0.0% Total COGS - - - - - - - - - - - - $ - 0.0% 0.0% Margin - - - - - - - - - - - - $ - 0.0% 0.0% Total Units Sold 50 100 200 200 300 450 600 300 200 200 200 600 $ 126,000 $ 63,000 $ 42,000 $ 42,000 $ 42,000 $ 126,000 $ $ 300 $ 150 $ 100 $ 100 $ 100 $ 300 $ 1,700 $ 125,700 $ 62,850 $ 41,900 $ 41,900 $ 41,900 $ 125,700 $ 712,300 0 0 0.0% 0 0 0.0% 0 0 0.0% 0 0 0.0% 0 Total Sales $ 10,500 $ 21,000 $ 42,000 $ 42,000 $ 63,000 $ 94,500 Total Cost of Goods Sold $ 25 $ 50 $ 100 $ 100 $ 150 $ 225 Total Margin $ 10,475 $ 20,950 $ 41,900 $ 41,900 $ 62,850 $ 94,275 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 3a-SalesForecastYear1 0 0.0% 3,400 714,000 5/14/2019 11:27 AM Sales Forecast Years 1-3 Sales Forecast Year 1-3 Prepared by: Company Name: Team 7 Lokaal Growth Rate Year 1 to Year 2: 15.00% Growth Rate Year 2 to Year 3: 25.00% Growth Rate Year 3 to Year 4: 30.00% Growth Rarte Year 4 to Year 5: 40.00% 36645 Category Septembe Product Lines Year 1 Totals January February March April May June July August r Novembe Decembe Year 2 October r r Totals 62790 Category Breakdow Category / n Total Septembe January February March April May June July August r Novembe Decembe Year 3 October r r Totals Breakdow Category / n Total Reservation Service Charge 3400 Sold 3400 Total Sales $ 58 115 230 230 345 518 690 345 230 230 230 690 3,910 0.0% 72 144 288 288 431 647 863 431 288 288 288 863 714,000 12,075 24,150 48,300 48,300 72,450 108,675 144,900 72,450 48,300 48,300 48,300 144,900 $ 821,100 100.0% 0.0% 15,094 30,188 60,375 60,375 90,563 135,844 181,125 90,563 60,375 60,375 60,375 181,125 $ 1,700 29 58 115 115 173 259 345 173 115 115 115 345 $ 1,955 0.2% 0.0% 36 72 144 144 216 323 431 216 144 144 144 431 Total Margin $ 712,300 12,046 24,093 48,185 48,185 72,278 108,416 144,555 72,278 48,185 48,185 48,185 144,555 $ 819,145 99.8% 0.0% 15,058 30,116 60,231 60,231 90,347 135,520 180,694 90,347 60,231 60,231 60,231 180,694 Total COGS 4,888 $ 1,026,375 0.0% 100.0% 0.0% 2,444 0.2% 0.0% $ 1,023,931 99.8% 0.0% $ 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.0% 0 0 0 0 0 0 0 0 0 0 0 0 Total Sales $ - - - - - - - - - - - - - $ - 0.0% 0.0% - - - - - - - - - - - - $ - 0.0% 0.0% Total COGS 0 Sold $ - - - - - - - - - - - - - $ 0 - 0.0% 0.0% - - - - - - - - - - - - $ 0 - 0.0% 0.0% 0.0% Margin $ - - - - - - - - - - - - - $ - 0.0% 0.0% - - - - - - - - - - - - $ - 0.0% 0.0% 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.0% 0 0 0 0 0 0 0 0 0 0 0 0 Total Sales $ - - - - - - - - - - - - - $ - 0.0% 0.0% - - - - - - - - - - - - $ - 0.0% 0.0% Total COGS 0 Sold $ - - - - - - - - - - - - - $ 0 - 0.0% 0.0% - - - - - - - - - - - - $ - 0.0% 0.0% Margin $ - - - - - - - - - - - - - $ - 0.0% 0.0% - - - - - - - - - - - - $ - 0.0% 0.0% 0 - 0 Sold Total Sales Total COGS $ Margin $ 0 0 0.0% - 0 0 0 0 0 0 0 0 0 0 0 0 0 0.0% 0 0 0 0 0 0 0 0 0 0 0 0 0 - - - - - - - - - - - - $ - 0.0% 0.0% - - - - - - - - - - - - $ - 0.0% 0.0% - - - - - - - - - - - - - $ - 0.0% 0.0% - - - - - - - - - - - - $ 0 - 0.0% 0.0% 0.0% - - - - - - - - - - - - - $ - 0.0% 0.0% - - - - - - - - - - - - $ - 0.0% 0.0% 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.0% 0 0 0 0 0 0 0 0 0 0 0 0 Total Sales $ - - - - - - - - - - - - - $ - 0.0% 0.0% - - - - - - - - - - - - $ - 0.0% 0.0% Total COGS 0 Sold $ - - - - - - - - - - - - - $ 0 - 0.0% 0.0% - - - - - - - - - - - - $ 0 - 0.0% 0.0% 0.0% Margin $ - - - - - - - - - - - - - $ - 0.0% 0.0% - - - - - - - - - - - - $ - 0.0% 0.0% 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.0% 0 0 0 0 0 0 0 0 0 0 0 0 Total Sales $ - - - - - - - - - - - - - $ - 0.0% 0.0% - - - - - - - - - - - - $ - 0.0% 0.0% Total COGS 0 Sold $ - - - - - - - - - - - - - $ - 0.0% 0.0% - - - - - - - - - - - - $ - 0.0% 0.0% Margin $ $ - 0.0% 0.0% $ - 0.0% 0.0% Total Units Sold Total Sales $ - - - - - - - - - - - - - 3400 58 115 230 230 345 518 690 345 230 230 230 690 ###### ###### 714,000 $ 12,075 Total Cost of Goods Sold $ 1,700 $ 29 Total Margin $ 712,300 $ 12,046 ###### ###### $ $ 58 ###### 115 ###### ###### $ 115 ###### ###### $ 173 ###### $ 259 ###### $ 345 ###### ###### $ 48,300 $ 173 $ 115 ###### $ 48,185 0 3,910 ###### $ 48,300 ####### $ $ $ $ 1,955 $ 819,145 115 $ 115 ###### $ 48,185 345 ####### 821,100 - - - - - - - - - - - - 72 144 288 288 431 647 863 431 288 288 288 863 ###### ###### ###### ###### $ $ 36 ###### 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 3b-SalesForecastYrs1-5 72 ###### ###### $ 144 ###### ###### ###### $ $ 144 ###### 216 ###### $ 323 ###### $ 431 ###### ###### $ 60,375 $ 216 $ 144 ###### $ 60,231 ###### $ 60,375 $ 144 $ 144 ###### $ 60,231 0 0.0% 4,888 ####### $ 1,026,375 $ 431 $ 2,444 ####### $ 1,023,931 5/14/2019 11:27 AM Sales Forecast Years 1-3 0 62790 94185 141277.5 188370 94185 62790 62790 7147.875 12825.75 21976.5 21976.5 32964.75 49447.125 65929.5 32964.75 21976.5 21976.5 21976.5 65929.5 188370 28591.5 51303 87906 87906 131859 197788.5 263718 131859 87906 87906 87906 62790 Category Septemb Product Lines January February March April May June July August er Novembe Decembe Year 4 October r r Totals Category Breakdo Category / wn Total Septembe January February March April May June July August r October Novembe Decembe Year 5 Breakdo Category / r wn Total r Totals Reservation Service Charge 3400 Sold 122 218 374 374 561 841 1,121 561 374 374 374 1,121 Total Sales 25,528 45,806 78,488 78,488 117,731 176,597 235,463 117,731 78,488 78,488 78,488 235,463 Total COGS 61 109 187 187 280 420 561 280 187 187 187 561 Total Margin 25,467 45,697 78,301 78,301 117,451 176,176 234,902 117,451 78,301 78,301 78,301 234,902 6,413 $ 1,346,756 0.0% 170 305 523 523 785 1,177 1,570 785 523 523 523 1,570 100.0% 0.0% 35,739 64,129 109,883 109,883 164,824 247,236 329,648 164,824 109,883 109,883 109,883 329,648 3,207 0.2% 0.0% 85 153 262 262 392 589 785 392 262 262 262 785 $ 1,343,550 99.8% 35,654 63,976 109,621 109,621 164,431 246,647 328,863 164,431 109,621 109,621 109,621 328,863 $ 8,978 ######## $ 4,489 ######## 0.0% 100.0% 0.0% 0.2% 0.0% 99.8% 0.0% 0 0 Sold Total Sales Total COGS Margin 0 0 Sold Total Sales Total COGS Margin 0 0 Sold Total Sales Total COGS Margin 0 0 Sold Total Sales Total COGS Margin 0 0 Sold Total Sales Total COGS Margin Total Units Sold Total Sales 122 ###### Total Cost of Goods Sold $ Total Margin 61 ###### 218 ###### $ 109 ###### 374 ###### $ 187 ###### 374 561 ###### $ 187 ###### 841 ###### $ 280 ###### 1,121 ###### $ 420 ###### ###### $ 561 ###### $ 561 374 ###### $ 78,488 ###### $ 78,488 $ $ 280 ###### 187 $ 78,301 374 187 374 $ 187 ###### $ 78,301 1,121 6,413 ####### $ 1,346,756 $ 561 $ 3,207 ####### $ 1,343,550 170 ###### $ 85 ###### 305 523 ###### $ 153 ###### 523 ###### $ 262 ###### 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 3b-SalesForecastYrs1-5 785 ###### $ 262 ###### 1,177 ###### $ 392 ###### 1,570 ###### $ 589 ###### ###### $ 785 ###### $ 785 523 ###### $ 109,883 392 ###### $ 262 $ 109,621 523 ###### $ 262 ###### 523 ####### $ 262 ####### 1,570 ####### $ 785 ####### 8,978 ######## $ 4,489 ######## 5/14/2019 11:27 AM Additional Inputs Additional Inputs Prepared By: Company Name: Team 7 Lokaal Accounts Receivable (A/R) Days Sales Outstanding Percent of Collections Year 1 0 Year 2 Year 3 Year 4 Year 5 Paid within 30 days 20% 20% 20% 20% 20% Paid between 30 and 60 days 80% 80% 80% 80% 80% Paid in more than 60 days 0% 0% 0% 0% 0% Allowance for bad debt 0% 0% 0% 0% 0% 100% 100% 100% 100% 100% This should equal 100% ----> ### ### ### ### 95 150 159 238 141278 2E+05 7147.875 12825.75 21976.5 21976.5 32964.75 49447.125 65929.5 32964.75 21976.5 21976.5 21976.5 65929.5 ### ### ### ### 188370 28591.5 51303 87906 87906 131859 197788.5 263718 131859 87906 87906 87906 215 150 150 187 438.32188 186.95938 150.0625 239.8375 274.70625 385.89688 569.03438 706.3875 457.84375 287.7875 261.625 313.95 Accounts Payable (A/P) Percent of Disbursements Year 1 Year 2 Year 3 Year 4 Year 5 Paid within 30 days 10% 10% 10% Paid between 30 and 60 days 70% 70% 70% Paid in more than 60 days 20% 20% 20% 20% 20% 100% 100% 100% 100% 100% This should equal 100% ----> 10% 10% 70% 172.5 336.70313 139.70313 350.391 420.5 70% 5744.0672 Line of Credit Assumptions Desired Minimum Cash Balance 50000 Line of Credit Interest Rate 6.00% Additional Fixed Assets Purchases Fixed Assets Depreciation (years) January Februar y March April Ma Jun Jul Augus Septembe Octobe Novembe Decembe y e y t r r r Year Year 1 r 2 Totals Total Year 3 Year 4 Total Year 5 Total Total Real Estate 40 $ - - - - - - - - - - - - $ - $ - $ - $ - $ - Leasehold Improvements 15 $ - - - - - - - - - - - - $ - $ - $ - $ - $ - #### $ Equipment 7 $ 5,800 - - - - - - - - - - - $ 5,800 $ - - $ - Furniture and Fixtures 5 $ 5,495 - - - - - - - - - - - $ 5,495 $ - $ - $ - $ - Vehicles 5 $ - - - - - - - - - - - - $ - $ - $ - $ - $ - Other Fixed Assets 5 $ - - - - - - - - - - - - $ - $ - $ - $ - $ - ##### $ - - $- $- $- - $ 11,295 $ - #### $ - $ - Total Additional Fixed Assets $ - $ $ - $ - $ - $ - $ Income Tax Assumptions Effective Income Tax Rate - Year 1 20.0% Effective Income Tax Rate - Year 2 20.0% Effective Income Tax Rate - Year 3 20.0% Amortization of Start-Up Costs Amortization Period in Years 3 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 4-AdditionalInputs 5/14/2019 11:27 AM Operating Expenses Year 1 Operating Expenses Year 1 Prepared By: Company Name: Team 7 Lokaal January February March April May June July August September October November December Annual Totals Expenses Advertising 5,000 4,000 4,000 4,000 4,500 4,500 3,000 3,000 2,000 2,000 2,500 3,000 $ 41,500 $ - Commissions and Fees - - - - - - - - - - - - $ - Contract Labor (Not included in payroll) - - - - - - - - - - - - $ - Insurance (other than health) - - - - - - - - - - - - $ - Legal and Professional Services - - - - - - - - - - - - $ - Licenses - - - - - - - - - - - 800 $ 800 Office Expense - https://sfbay.craigslist.org/sby/off/d/save-in-north-san-jose-on/6570689605.html 759 759 759 759 759 759 759 759 759 759 759 759 $ 9,108 Rent or Lease -- Vehicles, Machinery, Equipment 97 97 97 190 190 190 190 97 97 190 190 190 $ 1,815 Rent or Lease -- Other Business Property 99 - - - - - - - - - - - $ 99 Supplies $ - 20 20 20 20 20 20 20 20 20 20 20 20 $ 240 Travel, Meals and Entertainment - - - - - - - - - - - 500 $ 500 Utilities-included in office space - - - - - - - - - - - - $ - Miscellaneous - - - - - - - - - - - - $ - 5,269 $ 54,062 Total Expenses $ 5,975 $ 4,876 $ 4,876 $ 4,969 $ 5,469 $ 5,469 $ 3,969 $ 3,876 $ 2,876 $ 2,969 $ 3,469 $ Other Expenses Depreciation 161 161 161 161 161 161 161 161 161 161 161 161 $ 1,928 Commercial Loan - - - - - - - - - - - - $ - Commercial Mortgage - - - - - - - - - - - - $ - Credit Card Debt - - - - - - - - - - - - $ - Vehicle Loans - - - - - - - - - - - - $ - Other Bank Debt - - - - - - - - - - - - $ - Line of Credit - - - - - - - - - - - - $ - - - - - - - - - - - - - $ - Interest Bad Debt Expense Total Other Expenses $ 161 $ 161 $ 161 $ 161 $ 161 $ 161 $ 161 $ 161 $ 161 $ 161 $ 161 $ 161 $ 1,928 Total Fixed Operating Expenses $ 6,136 $ 5,037 $ 5,037 $ 5,130 $ 5,630 $ 5,630 $ 4,130 $ 4,037 $ 3,037 $ 3,130 $ 3,630 $ 5,430 $ 55,990 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 5a-OpExYear1 5/14/2019 11:27 AM Operating Expenses Years 1-3 Operating Expenses Years 1-3 Prepared By: Company Name: Team 7 Lokaal Line Item 2019 Advertising Growth Rate 1 to 2 2020 Growth Rate 2 to 3 2021 41,500 3.0% 42,745 30.0% 55,569 0 - 3.0% - 3.0% - Commissions and Fees - 5.0% - 5.0% - Contract Labor (Not included in payroll) - 3.0% - 3.0% - Insurance (other than health) - 3.0% - 3.0% - Legal and Professional Services - 3.0% - 3.0% - 800 Licenses 5.0% 840 5.0% 882 Office Expense - https://sfbay.craigslist.org/sby/off/d/save-in-north-san-jose-on/6570689605.html 9,108 3.0% 9,381 3.0% 9,663 Rent or Lease -- Vehicles, Machinery, Equipment 1,815 3.0% 1,869 3.0% 1,926 99 3.0% 102 3.0% 105 - 5.0% - 5.0% - Supplies 240 3.0% 247 3.0% 255 Travel, Meals and Entertainment 500 3.0% 515 3.0% 530 Utilities-included in office space - 3.0% - 3.0% - Miscellaneous - 3.0% - 3.0% - Rent or Lease -- Other Business Property 0 Total Expenses $ 54,062 $ 55,700 $ 68,929 Other Expenses Depreciation 1,928 1,928 2,160 Commercial Loan - - - Commercial Mortgage - - - Credit Card Debt - - - Vehicle Loans - - - Other Bank Debt - - - Line of Credit - - 50,000 Interest Bad Debt Expense - 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 5b-OpExYrs1-5 5/14/2019 11:27 AM Operating Expenses Years 1-3 Total Other Expenses $ 1,928 $ 1,928 $ 52,160 Total Operating Expenses $ 55,990 $ 57,627 $ 121,089 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 5b-OpExYrs1-5 5/14/2019 11:27 AM Operating Expenses Years 1-3 Growth Rate 3 to 4 Third Year Growth Rate 4 to 5 Third Year 40.0% 77,796 10.0% 85,575 3.0% - 3.0% - 5.0% - 5.0% - 3.0% - 3.0% - 3.0% - 3.0% - 3.0% - 3.0% - 5.0% 926 5.0% 972 20.0% 11,595 5.0% 12,175 15.0% 2,214 15.0% 2,547 3.0% 108 3.0% 111 5.0% - 5.0% - 10.0% 280 10.0% 308 20.0% 637 30.0% 828 3.0% - 3.0% - 3.0% - 3.0% - $ 93,556 $ 102,516 - - - - - - - - - - - - 50,000 50,000 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 5b-OpExYrs1-5 5/14/2019 11:27 AM Operating Expenses Years 1-3 $ 50,000 $ 143,556 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 5b-OpExYrs1-5 $ 50,000 $ 152,516 5/14/2019 11:27 AM Cash Flow Forecast Year 1 Cash Flow Forecast Year 1 Prepared By: Company Name: Team 7 Lokaal January Beginning Balance $ February 450,000 $ March 395,182 $ April 351,942 $ May 321,276 $ June 307,276 $ July 296,961 $ August 309,704 $ September 355,377 $ October 413,690 $ November 418,392 $ 406,372 December $ Totals 393,862 Cash Inflows Cash Sales Accounts Receivable Total Cash Inflows $ 2,100 4,200 8,400 8,400 12,600 18,900 25,200 12,600 8,400 8,400 8,400 25,200 $ 142,800 - 8,400 16,800 33,600 33,600 50,400 75,600 100,800 50,400 33,600 33,600 33,600 $ 470,400 58,800 $ 613,200 2,100 $ 12,600 $ 25,200 $ 42,000 $ 46,200 $ 69,300 $ 100,800 $ 113,400 $ 58,800 $ 42,000 $ 42,000 $ Cash Outflows Investing Activities New Fixed Asset Purchases - - - - - - - - - - - - $ Additional Inventory - - - - - - - - - - - - $ - Cost of Goods Sold 3 23 50 90 105 148 218 270 175 110 100 120 $ 1,410 $ 54,062 Operating Activities Operating Expenses 5,975 4,876 4,876 4,969 5,469 5,469 3,969 3,876 2,876 2,969 3,469 5,269 50,941 50,941 50,941 50,941 50,941 50,941 50,941 50,941 50,941 50,941 50,941 50,941 - - - - - - - - 106 - - 8,898 $ 9,004 Loan Payments - - - - - - - - - - - - $ - Owners Distribution - - - - - - - - - - - - $ - - - - - - - - - - - - $ - Payroll Taxes $ 611,289 Financing Activities Line of Credit Interest Line of Credit Repayments - - - - - - - - - - - - $ - Dividends Paid - - - - - - - - - - - - $ - Total Cash Outflows $ 56,918 $ 55,839 $ 55,867 $ 56,000 $ 56,515 Net Cash Flows $ (54,818) $ (43,239) $ (30,667) $ (14,000) $ (10,315) $ Operating Cash Balance $ 395,182 $ 351,942 $ 321,276 $ 307,276 $ 296,961 $ Line of Credit Drawdown $ - $ - $ - $ - $ - Ending Cash Balance $ 395,182 $ 351,942 $ 321,276 $ 307,276 $ 296,961 Line of Credit Balance $ - $ - $ - $ - $ - 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 6a-CashFlowYear1 $ 56,557 $ 55,127 $ 55,087 $ 54,098 $ 54,020 $ 54,510 $ 65,228 12,743 $ 45,673 $ 58,313 $ 4,702 $ (12,020) $ (12,510) $ 309,704 $ 355,377 $ 413,690 $ 418,392 $ 406,372 $ 393,862 $ $ - $ - $ - $ - $ - $ - $ - $ 309,704 $ 355,377 $ 413,690 $ 418,392 $ 406,372 $ 393,862 $ 387,434 $ - $ - $ - $ - $ - $ - $ - $ 675,766 (6,428) $ (62,566) 387,434 $ - 5/14/2019 11:27 AM Cash Flow Forecast Years 1-3 Cash Flow Forecast Years 1-3 Prepared By: Company Name: Team 7 Lokaal Year 1 Totals January Beginning Balance $ February March 387,434 $ 426,066 April $ 376,120 May June July August $ 340,692 $ 324,538 $ 313,197 $ 328,372 September October $ 379,692 $ 445,441 November December $ 448,509 $ 432,332 $ 416,167 28,980 Cash Inflows Cash Sales $ 142,800 2,415 4,830 9,660 9,660 14,490 21,735 28,980 14,490 9,660 9,660 9,660 Accounts Receivable $ 470,400 100,800 9,660 19,320 38,640 38,640 57,960 86,940 115,920 57,960 38,640 38,640 $ 613,200 79,695 $ 115,920 $ 130,410 New Fixed Asset Purchases $ - - - - - - - - - - - - Additional Inventory $ - - - - - - - - - - - - - Cost of Goods Sold $ 1,410 233 86 58 104 121 170 250 311 201 127 115 138 Total Cash Inflows $ 103,215 $ 14,490 $ 28,980 $ 48,300 $ 53,130 $ $ 67,620 $ 48,300 $ 48,300 38,640 $ 67,620 Cash Outflows Investing Activities - Operating Activities Operating Expenses $ 54,062 4,642 4,642 4,642 4,642 4,642 4,642 4,642 4,642 4,642 4,642 4,642 4,642 Payroll $ 611,289 59,709 59,709 59,709 59,709 59,709 59,709 59,709 59,709 59,709 59,709 59,709 59,709 Taxes $ 9,004 - - - - - - - - - - - 9,003 Loan Payments $ - - - - - - - - - - - - - Owners Distribution $ - - - - - - - - - - - - - Line of Credit Interest $ - - - - - - - - - - - - - Line of Credit Repayments $ - - - - - - - - - - - - - Dividends Paid $ - - - - - - - - - - - - Total Cash Outflows $ 675,766 Net Cash Flows $ Financing Activities 64,583 $ $ 64,520 $ 64,600 $ 64,661 $ 64,552 (62,566) $ 38,632 $ (49,946) $ (35,428) $ (16,154) $ (11,341) $ 15,175 $ 51,320 $ 65,749 $ 3,068 $ 426,066 Operating Cash Balance Line of Credit Drawdown $ - $ $ - Ending Cash Balance $ 426,066 Line of Credit Balance $ - 64,436 $ 64,408 $ 64,454 $ 64,471 $ 64,477 $ 64,465 $ $ (16,177) $ (16,165) $ 73,491 (5,871) $ 376,120 $ 340,692 $ 324,538 $ 313,197 $ 328,372 $ 379,692 $ 445,441 $ 448,509 $ 432,332 $ 416,167 $ 410,296 $ $ $ $ $ $ $ $ $ $ $ - - - - - - - - - - - $ 376,120 $ 340,692 $ 324,538 $ 313,197 $ 328,372 $ 379,692 $ 445,441 $ 448,509 $ 432,332 $ 416,167 $ 410,296 $ $ $ $ $ $ $ $ $ $ $ - - - 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 6b-CashFlowYrs1-5 - - - - - - - - 5/14/2019 11:27 AM Cash Flow Forecast Years 1-3 Year 2 Totals Beginning Balance Cash Inflows Cash Sales $ 164,220 Accounts Receivable $ 641,760 $ 805,980 New Fixed Asset Purchases $ - Total Cash Inflows Cash Outflows Investing Activities Additional Inventory $ - Cost of Goods Sold $ 1,912 Operating Activities Operating Expenses $ 55,700 Payroll $ 716,504 Taxes $ 9,003 Loan Payments $ - Owners Distribution $ - Line of Credit Interest $ - Line of Credit Repayments $ - Dividends Paid $ - Total Cash Outflows $ 783,118 Net Cash Flows $ 22,862 $ - Financing Activities Operating Cash Balance Line of Credit Drawdown Ending Cash Balance Line of Credit Balance 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 6b-CashFlowYrs1-5 5/14/2019 11:27 AM Cash Flow Forecast Years 1-3 January Beginning Balance February $ 410,296 March April May June July $ 450,082 $ 389,208 $ 346,476 $ 327,837 $ 315,214 August $ 335,735 September October November December $ 401,438 $ 485,177 $ 487,870 $ 469,202 $ Year 3 Totals January 450,548 February $ 437,689 March $ 508,723 $ 459,532 Cash Inflows Cash Sales Accounts Receivable Total Cash Inflows 3,019 6,038 12,075 12,075 18,113 27,169 36,225 18,113 12,075 12,075 12,075 36,225 $ 205,275 5,106 9,161 15,698 115,920 12,075 24,150 48,300 48,300 72,450 108,675 144,900 72,450 48,300 48,300 48,300 $ 792,120 144,900 20,423 36,645 118,939 18,113 36,225 60,375 66,413 99,619 144,900 163,013 84,525 60,375 60,375 84,525 $ 997,395 150,006 29,584 52,343 - Cash Outflows Investing Activities New Fixed Asset Purchases 250 250 250 250 250 250 250 250 250 250 250 250 $ 3,000 - - Additional Inventory - - - - - - - - - - - - $ - - - - Cost of Goods Sold 268 101 72 129 151 212 313 388 252 158 144 173 $ 2,360 337 140 95 Operating Activities Operating Expenses 5,744 5,744 5,744 5,744 5,744 5,744 5,744 5,744 5,744 5,744 5,744 5,744 $ 68,929 5,744 5,744 5,744 72,891 72,891 72,891 72,891 72,891 72,891 72,891 72,891 72,891 72,891 72,891 72,891 $ 874,690 72,891 72,891 72,891 - - - - - - - - 2,696 - - 18,327 $ 21,023 - - - Loan Payments - - - - - - - - - - - - $ - - - - Owners Distribution - - - - - - - - - - - - $ - - - - Line of Credit Interest - - - - - - - - - - - - $ - - - - Line of Credit Repayments - - - - - - - - - - - - $ - - - - Dividends Paid - - - - - - - - - - - - $ - - - 97,384 $ 970,002 $ 78,972 $ 27,393 $ 71,034 $ (49,191) $ (26,387) Payroll Taxes Financing Activities Total Cash Outflows $ 79,153 $ $ 79,097 $ 79,198 $ 79,273 $ 81,833 Net Cash Flows $ 39,786 $ (60,874) $ (42,732) $ (18,639) $ (12,623) $ 20,522 $ 65,702 $ 83,739 $ 2,692 $ (18,668) $ (18,654) $ (12,859) $ Operating Cash Balance $ 450,082 $ 389,208 $ 346,476 $ 327,837 $ 315,214 $ 335,735 $ 401,438 $ 485,177 $ 487,870 $ 469,202 $ 450,548 $ 437,689 Line of Credit Drawdown $ $ $ $ $ $ $ $ $ $ $ - $ - Ending Cash Balance $ 450,082 $ 389,208 $ 346,476 $ 327,837 $ 315,214 $ 335,735 $ 401,438 $ 485,177 $ 487,870 $ 469,202 $ 450,548 $ 437,689 Line of Credit Balance $ $ $ $ $ $ $ $ $ $ $ $ - - 78,986 - $ 78,957 - $ 79,014 - $ 79,036 - - - - 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 6b-CashFlowYrs1-5 - $ 79,043 - $ 79,029 - $ $ - 78,775 $ 78,730 $ 508,723 $ 459,532 $ 433,145 $ $ $ - - - $ 508,723 $ 459,532 $ 433,145 $ $ $ - - - 5/14/2019 11:27 AM Cash Flow Forecast Years 1-3 April Beginning Balance May June July $ 433,145 $ 432,848 $ 440,390 Cash Sales 15,698 23,546 35,319 Accounts Receivable 62,790 62,790 94,185 78,488 86,336 129,504 New Fixed Asset Purchases - - Additional Inventory - - Cost of Goods Sold 150 159 August $ 491,021 September October November December Year 4 Totals January $ 600,406 $ 733,267 $ 764,300 $ 764,003 $ 763,706 47,093 23,546 15,698 15,698 15,698 47,093 $ 141,278 188,370 94,185 62,790 62,790 62,790 188,370 211,916 109,883 78,488 78,488 109,883 $ 1,303,286 - - - - - - - $ - - - - - - - $ 238 350 420 215 150 150 187 $ February March April May $ 794,766 $ 911,211 $ 873,807 $ 868,301 $ 899,309 269,351 7,148 12,826 21,977 21,977 32,965 $ 1,033,935 188,370 28,592 51,303 87,906 87,906 195,518 41,417 73,280 109,883 120,871 - - - - - - - - - - - - 2,590 438 187 150 240 275 Cash Inflows Total Cash Inflows Cash Outflows Investing Activities Operating Activities Operating Expenses 5,744 5,744 5,744 5,744 5,744 5,744 5,744 5,744 5,744 $ 68,929 5,744.07 5,744 5,744 5,744 5,744 72,891 72,891 72,891 72,891 72,891 72,891 72,891 72,891 72,891 $ 874,690 72,891 72,891 72,891 72,891 72,891 - - - - - - - - - $ - - - - - - Loan Payments - - - - - - - - - $ - - - - - - Owners Distribution - - - - - - - - - $ - - - - - - Line of Credit Interest - - - - - - - - - $ - - - - - - Line of Credit Repayments - - - - - - - - - $ - - - - - - Dividends Paid - - - - - - - - - $ - - - - - $ 78,822 $ 946,209 $ (297) $ 31,061 $ 357,078 $ 116,445 $ (37,405) $ $ 911,211 $ 873,807 $ 868,301 $ 899,309 $ 941,270 $ $ $ $ $ Payroll Taxes Financing Activities 78,784 $ 78,794 $ 78,873 $ (297) $ 7,543 $ 50,631 $ 491,021 $ Net Cash Flows $ Operating Cash Balance $ 432,848 $ 440,390 Line of Credit Drawdown $ $ $ Ending Cash Balance $ 432,848 $ 440,390 $ 491,021 $ 600,406 $ 733,267 $ 764,300 $ 764,003 $ 763,706 $ 794,766 $ 911,211 $ 873,807 $ 868,301 $ 899,309 $ 941,270 Line of Credit Balance $ $ $ $ $ $ $ $ $ $ $ $ $ $ - - - 78,985 79,055 $ 78,850 $ $ 109,385 $ 132,861 $ 31,033 $ $ 600,406 $ 733,267 $ 764,300 $ 764,003 $ 763,706 $ 794,766 $ $ $ $ $ $ - $ - - 78,784 $ (297) $ - 78,784 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 6b-CashFlowYrs1-5 - - $ - 79,073 - $ 78,822 - $ 78,785 - Total Cash Outflows $ 78,875 $ 78,910 (5,505) $ 31,008 $ 41,961 - - - 5/14/2019 11:27 AM Cash Flow Forecast Years 1-3 June Beginning Balance $ July 941,270 August $ 1,043,555 September $ 1,228,069 October November December Year 5 Totals $ 1,445,411 $ 1,520,154 $ 1,551,113 $ 1,582,099 Cash Inflows Cash Sales 49,447 65,930 32,965 21,977 21,977 21,977 65,930 $ 377,092 131,859 197,789 263,718 131,859 87,906 87,906 87,906 $ 1,433,019 181,306 263,718 296,683 153,836 109,883 109,883 153,836 $ 1,810,111 New Fixed Asset Purchases - - - - - - - $ - Additional Inventory - - - - - - - $ - Cost of Goods Sold 386 569 706 458 288 262 314 $ 4,272 Accounts Receivable Total Cash Inflows Cash Outflows Investing Activities Operating Activities Operating Expenses 5,744 5,744 5,744 5,744 5,744 5,744 5,744 $ 68,929 72,891 72,891 72,891 72,891 72,891 72,891 72,891 $ 874,690 - - - - - - - $ - Loan Payments - - - - - - - $ - Owners Distribution - - - - - - - $ - Line of Credit Interest - - - - - - - $ - Line of Credit Repayments - - - - - - - $ - Dividends Paid - - - - - - - $ - Payroll Taxes Financing Activities Total Cash Outflows $ 79,021 $ 79,204 $ 79,341 $ 79,093 $ 78,923 $ 78,897 $ 78,949 $ 947,891 Net Cash Flows $ 102,285 $ 184,514 $ 217,341 $ 74,743 $ 30,960 $ 30,986 $ 74,887 $ 862,219 Operating Cash Balance $ 1,043,555 $ 1,228,069 $ 1,445,411 $ 1,520,154 $ 1,551,113 $ 1,582,099 $ 1,656,986 Line of Credit Drawdown $ $ $ $ $ $ $ $ - Ending Cash Balance $ 1,043,555 $ 1,228,069 $ 1,445,411 $ 1,520,154 $ 1,551,113 $ 1,582,099 $ 1,656,986 Line of Credit Balance $ $ $ $ $ $ $ - - - - - - 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 6b-CashFlowYrs1-5 - 5/14/2019 11:27 AM Income Statement Year 1 Income Statement Year 1 Prepared By: Company Name: Team 7 Lokaal January February March April May June July August September October November December Annual Totals Revenue 10,500 21,000 42,000 42,000 63,000 94,500 126,000 63,000 42,000 42,000 42,000 126,000 $ 714,000 0 - - - - - - - - - - - - $ - 0 - - - - - - - - - - - - $ - 0 - - - - - - - - - - - - $ - 0 - - - - - - - - - - - - $ - 0 - - - - - - - - - - - - $ - 126,000 $ 714,000 Reservation Service Charge Total Revenue $ 10,500 $ 21,000 $ 42,000 $ 42,000 $ 63,000 $ 94,500 $ 126,000 $ 63,000 $ 42,000 $ 42,000 $ 42,000 $ Cost of Goods Sold 25 50 100 100 150 225 300 150 100 100 100 300 $ 1,700 0 - - - - - - - - - - - - $ - 0 - - - - - - - - - - - - $ - 0 - - - - - - - - - - - - $ - 0 - - - - - - - - - - - - $ - 0 - - - - - - - - - - - - $ - Reservation Service Charge Total Cost of Goods Sold $ 25 $ 50 $ 100 $ 100 $ 150 $ 225 $ 300 $ 150 $ 100 $ 100 $ 100 $ 300 $ 1,700 Gross Margin $ 10,475 $ 20,950 $ 41,900 $ 41,900 $ 62,850 $ 94,275 $ 125,700 $ 62,850 $ 41,900 $ 41,900 $ 41,900 $ 125,700 $ 712,300 Payroll $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 50,941 $ 611,289 Operating Expenses Advertising 5,000 4,000 4,000 4,000 4,500 4,500 3,000 3,000 2,000 2,000 2,500 3,000 $ 41,500 0 - - - - - - - - - - - - $ - Commissions and Fees - - - - - - - - - - - - $ - Contract Labor (Not included in payroll) - - - - - - - - - - - - $ - Insurance (other than health) - - - - - - - - - - - - $ - Legal and Professional Services - - - - - - - - - - - - $ - Licenses - - - - - - - - - - - 800 $ 800 Office Expense - https://sfbay.craigslist.org/sby/off/d/save-in-north-san-jose-on/6570689605.html 759 759 759 759 759 759 759 759 759 759 759 759 $ 9,108 Rent or Lease -- Vehicles, Machinery, Equipment 97 97 97 190 190 190 190 97 97 190 190 190 $ 1,815 Rent or Lease -- Other Business Property 99 - - - - - - - - - - - $ 99 - - - - - - - - - - - - $ - 20 20 20 20 20 20 20 20 20 20 20 20 $ 240 Travel, Meals and Entertainment - - - - - - - - - - - 500 $ 500 Utilities-included in office space - - - - - - - - - - - - $ - Miscellaneous - - - - - - - - - - - - $ - 0 Supplies Other Expense 1 Other Expense 2 Total Operating Expenses $ Income (Before Other Expenses) $ $ 5,469 $ 5,469 $ 3,969 $ 3,876 $ (46,441) $ 5,975 $ (34,867) $ 4,876 $ (13,917) $ 4,876 $ (14,010) $ 4,969 6,440 $ 37,865 $ 70,790 $ 8,033 $ $ 5,269 $ 54,062 (11,917) $ 1,675 1,675 1,675 1,675 1,675 1,675 1,675 161 161 161 161 161 161 161 Commercial Loan - - - - - - Commercial Mortgage - - - - - - Credit Card Debt - - - - - - 2,876 $ (12,010) $ 2,969 $ (12,510) $ 3,469 69,490 $ 46,949 1,675 1,675 1,675 1,675 1,675 $ 20,105 161 161 161 161 161 $ 1,928 - - - - - - $ - - - - - - - $ - - - - - - - $ - Other Expenses Amortized Start-up Expenses Depreciation Interest 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 7a-IncomeStatementYear1 5/14/2019 11:27 AM Income Statement Year 1 Vehicle Loans - - - - - - - - - - - - $ - Other Bank Debt - - - - - - - - - - - - $ - Line of Credit - - - - - - - - - - - - $ - - - - - - - - - - - - - $ - 1,836 1,836 1,836 1,836 1,836 1,836 1,836 1,836 1,836 1,836 1,836 1,836 $ 22,032 (14,346) $ 24,916 Bad Debt Expense Total Other Expenses Net Income Before Income Tax $ Income Tax $ Net Profit/Loss $ (48,277) $ - $ (48,277) $ (36,703) $ - $ (36,703) $ (15,753) $ - $ (15,753) $ (15,846) $ 4,604 $ 36,029 $ 68,954 $ 6,197 $ (13,753) $ (13,846) $ $ - $ - $ 947 $ 1,575 $ (2,415) $ (106) $ (15,846) $ 4,604 $ 36,029 $ 68,007 $ 4,623 $ (11,337) $ (13,739) $ - 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 7a-IncomeStatementYear1 67,654 $ $ 9,004 $ 9,004 (14,346) $ 58,650 $ 15,912 - 5/14/2019 11:27 AM Income Statement Year 1 Income Tax Calculations Monthly Taxable Income $ (46,601) $ (35,027) $ (14,077) $ (14,170) $ 6,280 Cumulative Taxable Income $ (46,601) $ (81,629) $ (95,706) $ (109,877) $ (103,597) $ $ $ 70,630 $ 7,873 $ (12,077) $ (12,170) $ (12,670) $ 69,330 (65,892) $ 37,705 4,737 $ 12,610 $ 532 (11,638) $ (24,309) $ 45,021 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 7a-IncomeStatementYear1 $ 5/14/2019 11:27 AM Income Statement Years 1-3 Income Statement Years 1-3 Prepared By: Company Name: Team 7 Lokaal Revenue 2019 2020 2021 2022 2023 714,000 821,100 1,026,375 - 0 - - - - - 0 - - - - - 0 - - - - - 0 - - - - 0 - - - - Reservation Service Charge Total Revenue $ 714,000 100% $ 821,100 100% $ 1,026,375 100% $ - - 100% 100% Cost of Goods Sold 1,700 1,955 2,444 - 109 0 - - - - - 0 - - - - - 0 - - - - - 0 - - - - - 0 - - - - Reservation Service Charge Total Cost of Goods Sold 1,700 0% 1,955 100% 0% 819,145 2,444 100% 0% 1,023,931 - - 0% - 0% 100% 109 0% (109) 0% Gross Margin 712,300 Payroll 611,289 716,504 874,690 1,093,829 1,390,343 Operating Expenses Advertising 41,500 42,745 55,569 77,796 85,575 0 - - - - - Commissions and Fees - - - - - Contract Labor (Not included in payroll) - - - - - Insurance (other than health) - - - - Legal and Professional Services - - - - - - 800 840 882 926 972 Office Expense - https://sfbay.craigslist.org/sby/off/d/save-in-north-san-jose-on/6570689605.html 9,108 9,381 9,663 11,595 12,175 Rent or Lease -- Vehicles, Machinery, Equipment 1,815 1,869 1,926 2,214 2,547 99 102 105 108 111 - - - - - Supplies 240 247 255 280 308 Travel, Meals and Entertainment Licenses Rent or Lease -- Other Business Property 0 500 515 530 637 828 Utilities-included in office space - - - - - Miscellaneous - - - - - Other Expense 1 Other Expense 2 Total Operating Expenses $ 54,062 8% $ 55,700 7% $ 68,929 7% Income (Before Other Expenses) $ 46,949 7% $ 46,941 6% $ 80,312 8% $ 93,556 0% ######## $ 102,516 0% 0% ######## 0% Other Expenses Amortized Start-up Expenses 20,105 20,105 20,105 - - 1,928 1,928 2,160 - - Commercial Loan - - - - - Commercial Mortgage - - - - - Credit Card Debt - - - - - Vehicle Loans - - - - - Other Bank Debt - - - - - Line of Credit - - - - - - - - - Depreciation Interest Bad Debt Expense Total Other Expenses $ 22,032 Net Income Before Income Tax $ 24,916 Income Tax $ 9,004 Net Income/Loss $ 15,912 Income Tax Calculations Month 1 3% 2% $ 22,032 $ 24,909 $ 9,003 $ 15,906 Month 2 Month 3 3% 2% $ 22,264 $ 58,048 $ 21,023 $ 37,025 Month 4 2% $ - - 0% $ ######## $ 4% Month 5 - $ 0% Month 7 ######## Month 8 Month 9 0% Month 10 Taxable Amount Year 2 $ (52,465) $ (40,418) $ (16,326) $ (16,326) $ 7,767 Total $ (52,465) $ (92,883) $ (109,209) $ (125,535) $ (117,769) $ Income Taxes $ $ - $ - $ 1,236 $ 1,553 $ (2,789) $ Taxable Amount Year 3 $ (63,741) $ (48,686) $ (18,573) $ (18,576) $ 11,536 $ 56,707 $ 101,877 $ 11,528 $ (18,591) $ Total $ (63,741) $ (112,427) $ (131,000) $ (149,576) $ (81,333) $ 20,545 $ 32,072 $ 13,481 Income Taxes $ 4,109 $ 2,306 $ (3,718) $ - - $ $ - - $ $ - - $ $ - - $ (138,040) $ - $ 43,905 0% ######## ######## Month 6 $ - Month 11 Month 12 Total $ 80,044 $ 7,767 $ (16,326) $ (16,326) $ (16,326) $ 80,044 $ 45,013 (73,863) $ 6,181 $ 13,947 $ (2,379) $ (18,705) $ (35,031) $ 45,013 $ (562,698) - 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 7b-IncomeStatementYrs1-5 $ $ $ 9,003 $ 9,003 (18,594) $ - (18,597) $ 101,862 $ 78,153 (5,113) $ (23,710) $ 78,153 $ (560,687) 15,631 $ 21,023 2,696 $ $ - - $ 5/14/2019 11:27 AM Balance Sheet Years 1-3 Balance Sheet Years 1-3 Prepared By: Company Name: Team 7 Lokaal ASSETS 2019 2020 2021 Current Assets Cash 387,434 410,296 437,689 Accounts Receivable 100,800 115,920 144,900 - - - Prepaid Expenses 6,876 3,438 - Other Initial Costs 33,333 16,667 Inventory Total Current Assets $ 528,444 $ 546,321 $ 582,589 Fixed Assets Real Estate -- Land - - - Real Estate -- Buildings - - - Leasehold Improvements - - - Equipment 5,800 5,800 8,800 Furniture and Fixtures 5,495 5,495 5,495 Vehicles - - - Other - - Total Fixed Assets $ 11,295 $ - 11,295 $ 14,295 (Less Accumulated Depreciation) $ 1,928 $ 3,855 $ 6,015 Total Assets $ 537,811 $ 553,760 $ 590,868 LIABILITIES & EQUITY Liabilities 290 334 417 Commercial Loan Balance - - - Commercial Mortgage Balance - - - Credit Card Debt Balance - - - Vehicle Loans Balance - - - Other Bank Debt Balance - - - Line of Credit Balance - - Accounts Payable Total Liabilities $ 290 $ 334 $ 417 Equity Common Stock Retained Earnings 521,609 521,609 521,609 15,912 31,818 68,843 - - Dividends Dispersed/Owners Draw - Total Equity $ 537,521 $ 553,427 $ 590,452 $ 537,811 $ 553,760 $ 590,868 - $ - $ Total Liabilities and Equity Balance sheet in or out of balance? $ Balanced! 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx 8-BalanceSheet Balanced! Balanced! 5/14/2019 11:27 AM Breakeven Analysis Breakeven Analysis Year 1 Prepared By: Company Name: Team 7 Lokaal Gross Margin % of Sales Gross Margin $ 712,300 Total Sales $ 714,000 Gross Margin/Total Sales 99.8% Total Fixed Expenses Payroll Operating Expenses Operating + Payroll $ 611,289.39 $ 55,989.57 $ 667,279 Breakeven Sales in Dollars (Annual) 99.8% Gross Margin % of Sales Total Fixed Expenses Yearly Breakeven Amount Monthly Breakeven Amount 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx BreakevenAnalysis $ 667,279 $ 668,872 $ 55,739 5/14/2019 11:27 AM Financial Ratios - Year 1 Financial Ratios - Year 1 Prepared By: Company Name: Team 7 Lokaal Ratios Year One Year Two Year Three Industry Norms Notes Liquidity Current Ratio 1822.2 1638.1 1397.5 Quick Ratio 1683.6 1577.9 1397.5 0.0 0.0 0.0 61.5 53.5 94.0 Sales Growth 0.0% 15.0% 25.0% COGS to Sales 0.2% 0.2% 0.2% Gross Profit Margin 99.8% 99.8% 99.8% SG&A to Sales 93.2% 94.0% 91.9% Net Profit Margin 2.2% 1.9% 3.6% Return on Equity (ROE) 3.0% 2.9% 6.3% Return on Assets 3.0% 2.9% 6.3% 43.7% 45.6% 43.8% 50.8 50.8 50.8 Accounts Receivable Turnover 7.1 7.1 7.1 Days in Inventory 0.0 0.0 0.0 Inventory Turnover 0.0 0.0 0.0 Sales to Total Assets 1.3 1.5 1.7 Safety Debt to Equity Ratio Debt-Service Coverage Ratio - DSCR Profitability Owner's Compensation to Sales Efficiency Days in Receivables 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx FinancialRatios 5/14/2019 11:27 AM Diagnostic Tools - Year 1 Diagnostic Tools - Year 1 Prepared By: Company Name: Team 7 Lokaal General Financing Assumptions Value Owner's Cash Injection into the Business 100.0% Owner's injection is reasonable Cash Request as percent of Total Required Funds Loan Assumptions Findings 86.3% Cash request exceeds 20% which might be too high Value Commercial Loan Interest rate Findings 6.0% Interest rate seems reasonable Commercial Loan Term in Months 84 Loan term seems within range for this type of loan Commercial Mortgage Interest rate 6.0% Interest rate seems reasonable Commercial Mortgage Term in Months 240 Loan term seems within range for this type of loan Debt-Service Coverage Income Statement 6151.6% Calculated loan payments relative to operating proft seem reasonable Value Gross Margin as a Percent of Sales Owner's Compensation Lower Limit Check 99.8% Gross margin percentage seems reasonable $ Owner's Compensation Upper Limit Check 5.8% Advertising as a percent of sales seems reasonable $ Profitability as a Percent of Sales Cash Flow Statement 312,000 An owner's compensation amount has been established 1960.8% Owner's compensation may be too high relative to profitability of business Advertising Expense Levels as a Percent of Sales Profitability Levels Findings 15,912 The business is showing a profit 2.2% The projection does not seem highly unreasonable Value Findings Desired Operating cash Flow Levels $ - The financial projection provides the desired level of cash flow Line of Credit Drawdowns $ - The business doesn't seem to require a line of credit Accounts Receivable Ratio to Sales Balance Sheet Does the Year 1 Balance Sheet Balance? 14.1% Accounts receivable amount as a percent of sales seems reasonable Value $ Debt to Equity Ratio Breakeven Analysis Do Sales Exceed the Breakeven Level? Findings - The balance sheet does balance 0.1% Very comfortable Value $ Findings 45,128 The sales projection exceeds the projected break-even sales level 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx DiagnosticTools 5/14/2019 11:27 AM COGS Calculator COGS Calculator Prepared By: Company Name: Team 7 Lokaal Variable Costs of Products Timeframe: Month Product Line: Widget Raw materials - Labor used to produce product - Costs associated with shipping and storing raw materials - Production facility expenses (use fraction of total if facility is used for other items) - Total Product Expenses $ - Number Units Sold in timeframe used 0 Cost of Goods Sold Per Unit Please enter all information. Variable Costs of Services Timeframe: Month Product Line: Project Amount spent on labor during timeframe - Amount spent on materials during this timeframe - List any other variable costs associated with the delivery of your service during this timeframe. - Total Service Expenses $ - Number Units Sold During Timeframe Cost of Goods Sold Per Unit 0 Please enter all information. Return to Sales Forecast Year 1 20180504231430s4_financial_projections_spreadsheet_11302017_0___editable.xlsx COGS Calculator 5/14/2019 11:27 AM Amortization and Depreciation Schedule Amortization & Depreciation Schedule Prepared By: Company Name: Team 7 Lokaal Return to Starting Point Commercial Loan Principal Amount $ - Interest Rate 6.00% Loan Term in Months 84.00 Monthly Payment Amount $0.00 January February March April May June July August September October November December Totals Year One Interest - - - - - - - - - - - - $ - Principal - - - - - - - - - - - - $ - Loan Balance - - - - - - - - - - - - Interest - - - - - - - - - - - - $ - Principal - - - - - - - - - - - - $ - Loan Balance - - - - - - - - - - - - Interest - - - - - - - - - - - - $ - Principal - - - - - - - - - - - - $ - Loan Balance - - - - - - - - - - - - November December Year Two Year Three Commercial Mortgage Principal Amount $ - Interest Rate 6.00% Loan Term in Months 240.00 Monthly Payment Amount $0.00 January February March April May June July August September October Totals Year One Interest - - - - - - - - - - - - $ - Principal - - - - - - - - - - - - $ - Loan Balance - - - - - - - - - - - - Interest - - - - - - - - - - - - $ - Principal - - - - - - - - - - - - $ - Loan Balance - - - - - - - - - - - - Interest - - - - - - - - - - - - $ - Principal - - - - - - - - - - - - $ - Loan Balance - - - - - - - - - - - - Year Two Year Three Amortization and Depreciation Schedule Credit Card Debt Principal Amount $ - Interest Rate 7.00% Loan Term in Months 60.00 Monthly Payment Amount $0.00 January February March April May June July August September October November December Totals Year One Interest - - - - - - - - - - - - $ - Principal - - - - - - - - - - - - $ - Loan Balance - - - - - - - - - - - - Interest - - - - - - - - - - - - $ - Principal - - - - - - - - - - - - $ - Loan Balance - - - - - - - - - - - - Interest - - - - - - - - - - - - $ - Principal - - - - - - - - - - - - $ - Loan Balance - - - - - - - - - - - - November December Year Two Year Three Vehicle Loans Principal Amount $ - Interest Rate 6.00% Loan Term in Months 48.00 Monthly Payment Amount $0.00 January February March April May June July August September October Totals Year One Interest - - - - - - - - - - - - $ - Principal - - - - - - - - - - - - $ - Loan Balance - - - - - - - - - - - - Interest - - - - - - - - - - - - $ - Principal - - - - - - - - - - - - $ - Loan Balance - - - - - - - - - - - - Interest - - - - - - - - - - - - $ - Principal - - - - - - - - - - - - $ - Loan Balance - - - - - - - - - - - - Year Two Year Three Amortization and Depreciation Schedule Other Bank Debt Principal Amount $0.00 Interest Rate 5.00% Loan Term in Months 36.00 Monthly Payment Amount $0.00 January February March April May June July August September October November December Totals Year One Interest - - - - - - - - - - - - $ - Principal - - - - - - - - - - - - $ - Loan Balance - - - - - - - - - - - - Interest - - - - - - - - - - - - $ - Principal - - - - - - - - - - - - $ - Loan Balance - - - - - - - - - - - - Interest - - - - - - - - - - - - $ - Principal - - - - - - - - - - - - $ - Loan Balance - - - - - - - - - - - - November December Year Two Year Three Depreciation Real Estate-Buildings 40 Leasehold Improvements 15 Equipment 7 Furniture and Fixtures 5 Vehicles 5 Other 5 January February March April May June July August September October Totals Year One Starting Depreciation Additional Depreciation Ending Depreciation 161 161 161 161 161 161 161 161 161 161 161 161 - - - - - - - - - - - - $ 1,928 161 161 161 161 161 161 161 161 161 161 161 161 $ 1,928 161 161 161 161 161 161 161 161 161 161 - - - - - - - - - - 161 161 $ 1,928 - - 161 161 161 161 161 161 161 161 161 161 161 161 $ 1,928 161 164 167 170 173 176 178 181 3 3 3 3 3 3 3 3 184 187 190 193 $ 2,124 3 3 3 3 164 167 170 173 176 178 181 184 187 190 193 196 $ 2,160 Year Two Starting Depreciation Additional Depreciation Ending Depreciation Year Three Starting Depreciation Additional Depreciation Ending Deprecation Amortization and Depreciation Schedule Amortization of Start-Up Costs Monthly Prepaid Expenses $ Total Expensed each Year $ 3,438 Other Initial Costs $ 50,000 Total Expensed each Year $ Prepaid Expenses 10,314 16,667 January 287 1389 February March April May June July August September October November December Totals Year One Amount Amortized 287 287 287 287 287 287 287 287 287 287 287 287 Total Amortized 287 573 860 1,146 1,433 1,719 2,006 2,292 2,579 2,865 3,152 3,438 $ 3,438 $ 3,438 $ 3,438 Year Two Amount Amortized Total Amortized 287 287 287 287 287 287 287 287 287 287 287 287 3,725 4,011 4,298 4,584 4,871 5,157 5,444 5,730 6,017 6,303 6,590 6,876 Year Three Amount Amortized Total Amortized Other Initial Costs 287 287 287 287 287 287 287 287 287 287 287 287 7,163 7,449 7,736 8,022 8,309 8,595 8,882 9,168 9,455 9,741 10,028 10,314 January February March April May June July August September October November December Totals Year One Amount Amortized 1,389 1,389 1,389 1,389 1,389 1,389 1,389 1,389 1,389 1,389 1,389 1,389 Total Amortized 1,389 2,778 4,167 5,556 6,944 8,333 9,722 11,111 12,500 13,889 15,278 16,667 $ 16,667 $ 16,667 $ 16,667 Year Two Starting Depreciation 1,389 1,389 1,389 1,389 1,389 1,389 1,389 1,389 1,389 1,389 1,389 1,389 Ending Depreciation 18,056 19,444 20,833 22,222 23,611 25,000 26,389 27,778 29,167 30,556 31,944 33,333 Year Three Starting Depreciation Ending Deprecation 1,389 1,389 1,389 1,389 1,389 1,389 1,389 1,389 1,389 1,389 1,389 1,389 34,722 36,111 37,500 38,889 40,278 41,667 43,056 44,444 45,833 47,222 48,611 50,000 Date Last Revised Revised By 6/21/2013 Heather Hendy 7/14/2013 Heather Hendy 4/23/2014 Heather Hendy 11/12/2015 Joe Clarke 2/10/2016 Joe Clarke Notes Tab 3a, cell O55: added margin from 6th product. Tab 3b, cells O49 and AD49: added margins from 6th product. Tab 7b: added % sales for lines 23 and 59 for all three years. Updated Tabs 5a, 5b, 7a, and 7b to automatically carry over debt categories from Tab 1 in case they are edited. Updated Tabs 5b, 7a and 7b to carry over expense categories from tab 5a in case they are edited. Tab 8 Balance Sheet Cell F41 comes from E41 + tab 6b cell AB24 + tab 6b cell AB27. However tab 6b cell AB27 did not have a sum total in the cell. So when paying Dividends in Year 3 the total for the year is not calculated and carried over to the Balance Sheet. This has now been updated. Comments were added to the calculation for Social Security taxes to clarify that salaries above the wage base limited ($117,000 currently) will cause the estimate to be overstated and will require manual calculation. Corrected an error (typo) in Amortization Table. 4/3/2016 Joe Clarke Corrected an error in the calculation of Income Tax for Year 3 on Tab 7b and set defaults on Tab 4 to those needed by most startups. 5/18/2016 Joe Clarke Corrected the calculation of income tax on Tabs 7a and 7b to realize interest expenses from all loan types as well as bad debt. 11/30/2017 S. Reynolds 11/30/2017 S. Reynolds 11/30/2017 S. Reynolds 11/30/2017 S. Reynolds 11/30/2017 S. Reynolds 11/30/2017 S. Reynolds 11/30/2017 S. Reynolds On 1 - Starting Point tab, Changed depreciation years, real estate buildings to 39.5 and leaseholdl improvements to 15 On 1 - Starting Point tab, changed commercial loan and mortgage rate to 6% from 9% On tab 2a - changed social security wage base limit to $128,400 (2018 limit) On tab 2a - changed pension program to 3% (most companies use a 3% 401k match) On tab 4 - changed line of credit interest rate to 6%, depreciation (years) for real estate to 39.5 years and leasehold improvements to 15 years On tab 4 - changed income tax rate assumptions to 20% On Tab 6b-CashFlowYr1-3, cell O21, included formula (similar to Year 1 and Year 3) =SUM(C21:N21) 11/30/2017 S. Reynolds In the tab for Diagnostics Tools, in Cell D16, the excel condition for Debt Service Coverage is given as: Calculated loan payments relative to operating profit seem reasonable which points to Debt-Service Coverage Ratio - DSCR in the previous tab for Financial Ratios. Changed query =IF(C16>1,"Calculated loan payments relative to operating profit seem reasonable","Calculated loan payments relative to operating proft may be too high")
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

...


Anonymous
Really helpful material, saved me a great deal of time.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags