Budget worksheet w/ Excel

profileKendra8
aas.xlsx
Home>Business & Finance homework help>Accounting homework help>Budget worksheet w/ Excel

NCU AMC Financials

NCU Academic Medical Center NCU Academic Medical Center NCU Academic Medical Center
Income Statemen / P&L Statement / Statement of Operations (in thousands) Balance Sheet (in thousands) Ratios
for the Years Ended December 31, 2015 - 2017 for the Years Ended December 31, 2015 - 2017 (in '000) 12/31/17 12/31/16 12/31/15 2017 2017 $2,016 $2,016 2015 2015
[X/Y] [AA/AB] [AD/AE] Numerator Denominator Numerator Denominator Numerator Denominator
2017 2016 2015 ASSETS 2017 2016 2015
REVENUES Current assets Current ratio 1.38 1.30 1.34 $5,450 $3,955 $4,975 $3,836 $5,200 $3,872
Net patient services revenue $23,000 $22,750 $21,250 Cash and cash equivalents $700 $675 $600 Collection period ratio 63.478 56.154 64.412 $4,000 $63.014 $3,500 $62 $3,750 $58.219
Premium revenue 600 300 400 Receivables, net 4,000 3,500 3,750 Days Cash on hand (short-term sources) 12.494 11.704 10.306 $700 $56.027 $675 $58 $600 $58.219
Other operating revenue 725 680 710 Inventory 750 800 850 Days cash on hand (all sources) 17.224 14.912 13.226 $965 $56.027 $860 $58 $770 $58.219
Total operating revenue $24,325 $23,730 $22,360 Total current assets 5,450 4,975 5,200 Average payment period (days) 70.591 66.515 66.507 $3,955 $56.027 $3,836 $58 $3,872 $58.219
Operating margin ratio 5.653 2.866 (3.086) $1,375 $24,325 $680 $23,730 ($690) $22,360
EXPENSES Noncurrent assets Total margin ratio 6.742 3.645 (2.326) $1,640 $24,325 $865 $23,730 ($520) $22,360
Salaries, wages and benefits 13250 14150 14900 Land, plant and equipment 26,500 24,000 23,000 Retrun on net assets ratio 9.650 7.766 (5.035) $1,640 $16,995 $865 $11,139 ($520) $10,328
Supplies and other expenses 7000 6750 6250 Accumulated depreciation (18,000) (17,000) (16,000) Total asset turnover ratio 0.896 1.041 1.015 $24,590 $27,450 $23,915 $22,975 $22,530 $22,200
Depreciation 2500 2000 1800 Long-term investments 5,000 4,000 3,000 Age of plant ratio 7.200 8.500 8.889 $18,000 $2,500 $17,000 $2,000 $16,000 $1,800
Interest 200 150 100 Other noncurrent assets 8,500 7,000 7,000 Fixed asset turnover ratio 2.893 3.416 3.219 $24,590 $8,500 $23,915 $7,000 $22,530 $7,000
Total operating expenses 22950 23050 23050 Total noncurrent assets 22,000 18,000 17,000 Current asset turnover ratio 4.512 4.807 4.333 $24,590 $5,450 $23,915 $4,975 $22,530 $5,200
Inventory ratio 32.787 28.669 26.506 $24,590 $750 $23,915 $800 $22,530 $850
OPERATING INCOME $1,375 $680 ($690) Total assets 27,450 22,975 22,200 Net asset financing ratio 61.913 48.483 46.523 $16,995 $27,450 $11,139 $22,975 $10,328 $22,200
NONOPERATING INCOME 265 185 170 Long-term debt to capitalization ratio 27.665 41.799 43.649 $6,500 $23,495 $8,000 $19,139 $8,000 $18,328
LIABILITIES AND NET ASSETS Debt service coverage ratio 11.302 9.192 5.476 $4,340 $384 $3,015 $328 $1,380 $252
EXCESS OF REVENUE OVER EXPENSES $1,640 $865 ($520) Current liabilities Cash flow to debt ratio 39.598 24.206 10.782 $4,140 $10,455 $2,865 $11,836 $1,280 $11,872
Accounts payable 2,000 2,500 2,300
Total change in net assets $1,640 $865 ($520) Notes payable 900 750 800
Accrued expenses payable 871 408 620
Current portion of long-term debt 184 178 152
Total current liabilities 3,955 3,836 3,872
Noncurrent liabilites
Long-term debt, net of current portion 6,500 8,000 8,000
Total noncurrent liabilities 6,500 8,000 8,000
Total liabilities 10,455 11,836 11,872
NET ASSETS 16,995 11,139 10,328
Total liabilities and net assets $27,450 $22,975 $22,200