Complete the SpreadSheet FIN4345

profilefernandon8908
SpreadsheetABC.xlsx
Home>Business & Finance homework help>Accounting homework help>Complete the SpreadSheet FIN4345

OBJ

Objective Spread ABC's Statements
Student Name Yaping Lin
ID Number 4215637

BS

Balance Sheet
Company Name: ABC, Inc
Amounts In: ($)
Period: Fiscal
Statement Type:
Date: December 31 1996 1997 1998 1999
ASSETS
Cash & Cash Equivalents 2,382 4,061
Marketable Securities 8,004 5,272
Accounts Receivable, net 8,350 8,960
Inventories 36,769 47,041
Prepaid Expenses 759 512
Other Current Assets
Total Current Assets - 0 - 0 56,264 65,846
Net Fixed Assets 18,977 29,079
Investments
Intangibles
Other Assets 668 373
Total Assets - 0 - 0 75,909 95,298
LIABILITIES & STOCKHOLDERS' EQUITY
Accounts Payable 7,591 14,294
Notes Payable - Banks 6,012 5,614
Current Maturities of Long Term Debt 1,516 1,884
Accrued Liabilities 5,313 5,669
Other Current Liabilities
Total Current Liabilities - 0 - 0 20,432 27,461
Deferred Federal Income Taxes 635 843
Long-Term Debt 16,975 21,059
Subordinated Debt
Total Liabilities - 0 - 0 38,042 49,363
Common Stock 4,594 4,803
Additional paid-in capital 910 957
Retained Earnings 32,363 40,175
Treasury Stock
Total Stockholders' Equity - 0 - 0 37,867 45,935
Total Liabilities & Stockholders' Equity - 0 - 0 75,909 95,298

IS

Statements of Earnings and Retained Earnings
Company Name: ABC, Inc
Amounts In: ($)
Period: Fiscal
Statement Type:
Date: December 31 1996 1997 1998 1999
Total Sales - Revenues 140,700 153,000 215,600
Cost of Goods Sold 81,606 91,879 129,364
Gross Profit - 0 59,094 61,121 86,236
Selling, Gen & Adm Expenses 25,498 26,382 32,664
Advertising 9,541 10,792 14,258
Lease Payments 7,267 7,111 13,058
Officers' Salaries
Bad Debt Expense
Depreciation and amortization 2,501 2,984 3,998
Repairs and maintenance 3,031 2,046 3,015
Net Operating Income (Profit) - 0 11,256 11,806 19,243
Interest Income 738 838 422
Interest Expense 1,274 2,277 2,585
Other Income
Other Expense
Net Income Before Taxes - 0 10,720 10,367 17,080
Income Taxes 4,824 4,457 7,686
Net Income Before Extra Items - 0 5,896 5,910 9,394
Extraordinary Income
Extraordinary Charges
Net Income (Loss) - 0 5,896 5,910 9,394
Beginning Retained Earnings 24,260 28,315 32,363
Net Profit 5,896 5,910 9,394
Cash Dividends 1,841 1,862 1,582
Other Deductions
Other Additions
Ending Retained Earnings - 0 28,315 32,363 40,175

CF

Statement of Cash Flow
Company Name: ABC, Inc
Amounts In: ($)
Period: Fiscal
Statement Type:
Date: December 31 1996 1997 1998 1999
Cash Flows from Operations
Net Income 5,896 5,910 9,394
Noncash revenue and expense included in net income
Depreciation and amortization 2,501 2,984 3,998
Change in Deferred Income Taxes 118 136 208
Cash provided (used) by current assets and liabilities
Change in Accounts Receivable (448) (3,339) (610)
Change in Inventory (2,331) (7,006) (10,272)
Change in Prepaid Expenses (82) 295 247
Change in Other Current Assets - 0
Change in Accounts Payable 902 (1,051) 6,703
Change in Accrued Liabilities (927) (1,696) 356
Change in Other Current Liabilities - 0
Total Cash Flows from Operations - 0 5,629 (3,767) 10,024
Cash Flows from Investing
Change in Fixed Assets (3,982) (4,773) (14,100)
Change in Other Investing Activities - 0 - 0 295
Total Cash Flows from Investing - 0 (3,982) (4,773) (13,805)
Cash Flows from Financing
Change in Short-term Borrowings 1,326 1,854 (30)
Change in Long-term Borrowings 502 6,289 4,084
Cash Dividends Paid (1,841) (1,862) (1,582)
Change in Common Stock 124 183 256
Total Cash Flows from Financing - 0 111 6,464 2,728
Net Change in Cash and Marketable Securities - 0 1,758 (2,076) (1,053)

CSBS

Common Size Balance Sheet
Company Name: ABC, Inc
Amounts In: (%)
Period: Fiscal
Statement Type:
Date: December 31 1996 1997 1998 1999
ASSETS
Cash & Cash Equivalents 0.031 0.043
Marketable Securities 0.105 0.055
Accounts Receivable, net 0.110 0.094
Inventories 0.484 0.494
Prepaid Expenses 0.010 0.005
Other Current Assets
Total Current Assets - 0 - 0 0.741 0.691
Net Fixed Assets 0.250 0.305
Investments
Intangibles
Other Assets 0.009 0.004
Total Assets - 0 - 0 1.000 1.000
LIABILITIES & STOCKHOLDERS' EQUITY
Accounts Payable 0.100 0.150
Notes Payable - Banks 0.079 0.059
Current Maturities of Long Term Debt 0.020 0.020
Accrued Liabilities 0.070 0.059
Other Current Liabilities
Total Current Liabilities - 0 - 0 0.269 0.288
Deferred Federal Income Taxes 0.008 0.009
Long-Term Debt 0.224 0.221
Subordinated Debt
Total Liabilities - 0 - 0 0.501 0.518
Common Stock 0.061 0.050
Additional paid-in capital 0.012 0.010
Retained Earnings 0.426 0.422
Treasury Stock
Total Stockholders' Equity - 0 - 0 0.499 0.482
Total Liabilities & Stockholders' Equity - 0 - 0 1.000 1.000

CSIS

Common Size Income Statement
Company Name: ABC, Inc
Amounts In: (%)
Period: Fiscal
Statement Type:
Date: December 31 1996 1997 1998 1999
Total Sales - Revenues 1.000 1.000 1.000
Cost of Goods Sold 0.580 0.601 0.600
Gross Profit - 0 0.420 0.399 0.400
Selling, Gen & Adm Expenses 0.181 0.172 0.152
Advertising 0.068 0.071 0.066
Lease Payments 0.052 0.046 0.061
Officers' Salaries
Bad Debt Expense
Depreciation and amortization 0.018 0.020 0.019
Repairs and maintenance 0.022 0.013 0.014
Net Operating Income (Profit) - 0 0.080 0.077 0.089
Interest Income 0.005 0.005 0.002
Interest Expense 0.009 0.015 0.012
Other Income
Other Expense
Net Income Before Taxes - 0 0.076 0.068 0.079
Income Taxes 0.034 0.029 0.036
Net Income Before Extra Items - 0 0.042 0.039 0.044
Extraordinary Income
Extraordinary Charges
Net Income (Loss) - 0 0.042 0.039 0.044

FR

Financial Ratios
Company Name: ABC, Inc
Amounts In: ($ in Summary)
Period: Fiscal
Statement Type: Better/
Date: December 31 1996 1997 1998 1999 Worse
Financial Summary
Current Assets 56,264 65,846
Current Liabilities 20,432 27,461
Total Assets 75,909 95,298
Total Liabilities 38,042 49,363
Total Net Worth 37,867 45,935
Net Working Capital 35,832 38,385
Working Assets 45,119 56,001
Working Liabilities 12,904 19,963
Working Investment 32,215 36,038
Long Term Debt 16,975 21,059
Tangible Net Worth 37,867 45,935
Average Annual Long-term Debt Maturities 1,516 1,884
Current Maturities of LTD
Operating Cash Flow
Net Sales 140,700 153,000 215,600
Net Income 5,896 5,910 9,394
Liquidity/Activity Ratios
Current Ratio (x) 2.754 2.398
Quick Ratio (x) 0.954 0.685
Cash Flow Liquidity Ratio (x) 0.324 0.705
Sales/Working Capital (x)
Accounts Receivable (x)
Accounts Receivable (Days) 20 15
Inventory Turnover vs COGS (x) 2.5 2.8
Inventory Turnover vs Aver Sales (Days) 88 80
Accounts Payable vs COGS (Days) 30 40
Accounts Payable vs Aver Sales (Days) 18 24
Cash Conversion Cycle (Days) 90 71
Leverage Ratios
Debt Ratio (%) 50.1% 51.8%
Long-term Debt to Total Capitalization (%) 31.0% 31.4%
Debt to Worth (x) 1.00 1.07
Debt to Tangible Net Worth (x)
(Net Profit+Depr/Depl/Amort)/CMLTD (x)
Times Interest Earned (x)
Fixed Charge Coverage (x)
Cash Flow Adequacy (x)
Performance Ratios
Fixed Asset Turnover (x)
Total Asset Turnover (x)
Gross Profit Margin (%)
Operating Profit Margin (%)
Net Profit Margin (%)
Cash Flow Margin (%)
Profit Before Taxes/Total Assets (%)
Return on Assets (%)
Return on Equity (%)
Cash Return on Assets (%)
Sales Growth (%)
Earnings Growth (%)
Total Asset Growth (%)