Scribd Logo
Upload

Welcome to Scribd!

  • Larrys Bicycle Shop - Annual Financial Statements - Original Hardcoded

    Uploaded by

    Larry Mai
    1K views4 pages
    Hard coded financial model for skill assessment

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    XLS, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Report this Document
    Hard coded financial model for skill assessment

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as XLS, PDF, TXT or read online from Scribd
    Flag for inappropriate content
    1K views4 pages

    Larrys Bicycle Shop - Annual Financial Statements - Original Hardcoded

    Uploaded by

    Larry Mai
    Hard coded financial model for skill assessment

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as XLS, PDF, TXT or read online from Scribd
    Flag for inappropriate content
    of 4

    Larry Bicycle Shop - Financial Statements

    Year 1 Year 2 Year 3 Year 4


    Income Statement

    Revenue $2,000.0 $2,100.0 $2,205.0 $2,315.3


    Cost of Goods Sold 1,820.0 1,932.0 2,030.1 2,131.7
    Gross Profit $180.0 $168.0 $174.9 $183.6

    Operating Expenses
    Salaries $60.0 $60.0 $60.0 $60.0
    Rent 30.0 30.0 30.0 30.0
    Utilities 10.0 10.0 10.0 10.0
    Depreciation 50.0 50.0 50.0 50.0
    Total $150.0 $150.0 $150.0 $150.0

    Operating Income $30.0 $18.0 $24.9 $33.6

    Other (Expense) / Income


    Interest Expense ($20.0) ($20.0) ($20.0) ($20.0)
    Interest Income 4.7 5.6 6.1 6.2
    Total Other Income ($15.3) ($14.4) ($13.9) ($13.8)

    Income before taxes $14.7 $3.6 $11.0 $19.8


    Tax $0.8 $2.7
    Net Income $14.7 $3.6 $10.2 $17.1

    EBITDA $80.0 $68.0 $74.9 $83.6

    Balance Sheet

    Current Assets
    Cash $499.7 $601.3 $663.5 $681.6
    Account Receivable 75.0 54.0 65.0 90.0
    Inventory 20.0 21.0 15.0 19.0
    Prepaid Expenses 25.0 34.0 50.0 65.0
    Total Current Assets $619.7 $710.3 $793.5 $855.6

    Long Term Assets


    Plant, Property & Equipment, Gr 1,000.0 1,000.0 1,000.0 1,000.0
    Accumulated Depreciation 50.0 100.0 150.0 200.0
    Plant, Property & Equipment, Net 950.0 900.0 850.0 800.0

    Total Long Term Assets $950.0 $900.0 $850.0 $800.0

    Total Assets $1,569.7 $1,610.3 $1,643.5 $1,655.6

    Current Liabilities
    Account Payable $30.0 $58.0 $65.0 $45.0
    Accrued Expenses 25.0 34.0 50.0 65.0
    Total Current Liabilities $55.0 $92.0 $115.0 $110.0

    Long Term Liabilities


    Long Term Debt 500.0 500.0 500.0 500.0
    Total Long Term Liabilities $500.0 $500.0 $500.0 $500.0

    Total Liabilities $555.0 $592.0 $615.0 $610.0

    Shareholder's Equity
    Common Equity $1,000.0 $1,000.0 $1,000.0 $1,000.0
    Retained Earnings 14.7 18.3 28.5 45.6
    Total Shareholder's Equity $1,014.7 $1,018.3 $1,028.5 $1,045.6
    Total Liabilities and Shareholder's E $1,569.7 $1,610.3 $1,643.5 $1,655.6

    Cash Flow
    Cash Flow from Operations
    Net Income $14.7 $3.6 $10.2 $17.1
    Less: Deprecation 50.0 50.0 50.0 50.0
    Account Receivable (75.0) 21.0 (11.0) (25.0)
    Inventory (20.0) (1.0) 6.0 (4.0)
    Prepaid Expenses (25.0) (9.0) (16.0) (15.0)
    Account Payable $30.0 $28.0 $7.0 ($20.0)
    Accrued Expenses 25.0 9.0 16.0 15.0
    Cash Flow from Operations ($0.3) $101.6 $62.2 $18.1

    Cash Flow from Investing


    Plant, Property & Equipment, Gr ($1,000) $0 $0 $0
    Cash Flow from Investing ($1,000) $0 $0 $0

    Cash Flow from Financing


    Long Term Debt $500.0 $0.0 $0.0 $0.0
    Common Equity 1,000.0 0.0 0.0 0.0
    Cash Flow from Financing $1,500.0 $0.0 $0.0 $0.0

    Cash Beginning $0.0 $499.7 $601.3 $663.5


    Net Cash Change 499.7 101.6 62.2 18.1
    Ending Cash $499.7 $601.3 $663.5 $681.6

    MODEL INSTRUCTIONS / ASSUMPTIONS

    Build Year 5 - 10
    Revenue Growth of 10% a year
    CoGS drop by 1% a year
    OPEX is % of revenue (except for Dep)
    Interest Rate is 4%
    Interest Income is 1% of Cash
    For now, assume Deprecation stays at $50.
    Tax is 35%
    Calculate DSO, DSP, and Inventory Turns
    No additonal Debt
    No additional equity
    Some additional employees were added after year 4

    Extra Credit - added a second store in year 8


    CapEx of $100; straightline dep over 5 years
    Sold some equity ($500) and took on more debt ($300) to finance the new store
    Added services business in both stores (higher margin business)
    The services business was 25% of total revenue
    Year 5 Year 6 Year 7 Year 8 Year 9 Year 10

    You might also like