Scribd Logo
Upload

Welcome to Scribd!

  • 34 views

    Astral - XLS: Assumptions / Inputs

    Uploaded by

    Narinder

    Copyright:

    © All Rights Reserved
    Download as XLS, PDF, TXT or read online from Scribd

    Astral - XLS: Assumptions / Inputs

    Uploaded by

    Narinder
    34 views6 pages

    Original Title

    Astral(2)

    Copyright

    © © All Rights Reserved

    Available Formats

    XLS, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as XLS, PDF, TXT or read online from Scribd
    Download as xls, pdf, or txt
    34 views6 pages

    Astral - XLS: Assumptions / Inputs

    Uploaded by

    Narinder

    Copyright:

    © All Rights Reserved
    Download as XLS, PDF, TXT or read online from Scribd
    Download as xls, pdf, or txt
    of 6

    ASTRAL.

    XLS

    This Spreadsheet supports STUDENT analysis of the case,


    "Astral Records Ltd., North America: Some Financial
    Concerns." (UVA -F-1065, v. 1.5 )

    Please note:
    1) This is a working model. Assumptions / Inputs presented
    can be changed to vary the results.

    2) As long as default spreadsheet calculation is "automatic"


    the impact of changing assumptions will be computed in
    real time. If calculation is set as "manual" you should press
    the F9 function key to
    recalculate results. To set numerical calculation settings to
    automatic look under tools, options, calculations menu.

    3) This model intentionally incorporates a circular reference


    in its logic. In order to resolve this circularity, please instruct
    Excel to "iterate" 20 or so times in recalculating the model.
    This may be done by clicking on Tools/Options/Calculation,
    and then on Iteration.

    Revised: 7/7/98.
    Copyright (C) 1998, by the University of Virginia Darden School Foundation.
    Exhibit 1
    ASTRAL RECORDS
    Historical and Projected Income Statements
    Fiscal Year Ended August 23
    1990 1991 1992 1993
    (Actual) (Actual) (Actual) (Actual)
    1 Sales 26,202 28,822 34,010 39,792
    Operating Expenses:
    2 Production Costs and Expenses 11,950 13,380 17,847 22,335
    3 Admin. and Selling Expenses 5,734 5,967 7,020 7,970
    3 Depreciation 2,376 2,367 2,667 2,667
    4 Total Operating Expenses (20,060) (21,714) (27,534) (32,972)
    5 Operating Margin 6,142 7,109 6,476 6,820
    6 Interest Expense (2,427) (2,535) (3,265) (3,222)
    7 Earnings Before Taxes 3,715 4,574 3,212 3,598
    8 Income Taxes (1,647) (1,845) (1,269) (1,403)
    9 Net Earnings 2,068 2,729 1,943 2,195

    Dividends on :
    15 Dividends to All Common Shs 1,000 1,000 1,000 1,000
    16 Retentions of Earnings 1,068 1,729 943 1,195
    Exhibit 2
    ASTRAL RECORDS
    Historical and Projected Balance Sheets
    (fiscal year ended August 23; all figures in $ thousands)

    1990 1991 1992 1993


    (Actual) (Actual) (Actual) (Actual)
    Assets
    1 Cash 1,764 2,040 2,905 1,540
    2 Accounts Receivable 8,113 9,125 10,311 13,316
    3 Inventories 15,861 17,147 25,643 34,717
    4 Total Current Assets 25,738 28,312 38,859 49,573
    5 Gross Property Plant & Equipt. 23,667 26,667 26,667 26,667
    6 Accumulated Depreciation (2,505) (4,872) (7,538) (10,205)
    7 Net Property Plant & Equipt. 21,162 21,795 19,129 16,462
    8 Total Assets 46,900 50,107 57,988 66,035

    Liabilities and Stockholders' Equity:


    9 Short Term Borrowings (Bank) 12,060 13,042 19,680 25,802
    10 Accounts Payable 4,511 4,607 4,705 5,328
    11 Other Current Liabilities 9,014 9,414 9,616 9,723
    12 Total Current Liabilities 25,585 27,063 34,001 40,853
    13 Long Term Debt 10,000 10,000 10,000 10,000
    14 Shareholders' Equity 11,315 13,044 13,987 15,182
    15 Total Liabs. & Stkhldrs' Eq. 46,900 50,107 57,988 66,035
    Exhibit 3
    ASTRAL RECORDS
    Ratio Analyses of Historical and
    Projected Financial Statements
    (fiscal year ended August 23)

    1990 1991 1992 1993


    (Actual) (Actual) (Actual) (Actual)
    Profitability
    1 Operating Profit Margin 23.4% 24.7% 19.0% 17.1%
    2 Average Tax Rate 44.3% 40.3% 39.5% 39.0%
    3 Return on Sales 7.9% 9.5% 5.7% 5.5%
    4 Return on Equity 18.3% 20.9% 13.9% 14.5%
    5 Return on Assets 4.4% 5.4% 3.4% 3.3%

    Leverage
    6 Debt/Equity Ratio 1.95 1.77 2.12 2.36
    7 Debt/Total Assets 0.47 0.46 0.51 0.54
    8 EBIT/Interest (x) 2.53 2.80 1.98 2.12

    Asset Utilization
    9 Sales/Assets 55.9% 57.5% 58.7% 60.3%
    10 Sales Growth Rate 4.0% 10.0% 18.0% 17.0%
    11 Assets Growth Rate 6.0% 6.8% 15.7% 13.9%
    12 Days in Receivables 113.0 115.6 110.7 122.1
    13 Payables to COGS 37.7% 34.4% 26.4% 23.9%
    14 Inventories to COGS 132.7% 128.2% 143.7% 155.4%

    Liquidity
    15 Current Ratio 1.01 1.05 1.14 1.21
    16 Quick Ratio 0.39 0.41 0.39 0.36
    Exhibit 5
    ASTRAL RECORDS
    Data on Comparable Companies and Capital Market Conditions

    Number of
    % of Sales Market Shares
    from CD Price/EarningsValue Line Book Book Value Price Outstanding
    Name Production Ratio Beta D/E per Share per Share (millions)
    Dickenson Inc. 20% 9 1.5 0.45 5.5 10 5
    Harris-Beshel 95% 8 1.3 0.7 12.75 14 7.5
    Donaldson Inc. 90% 12 1.2 1.4 5.25 18 10
    IBBEX Corp. 40% 16 1.45 0.1 16.8 20 15
    ZEPORT 60% 10 1.6 1.3 25 40 10
    Value Line
    5-Year
    Last Annual
    Earnings Growth Bond
    Dividend Forecast Rating
    $0.95 4% A
    $1.00 6% AA
    $0.75 8% AA
    $0.00 10% Baa
    $2.20 7% B

    You might also like