Part 2 of Financial Analysis Research Project using Excel & Tableau.
dhriase
ProjectFinancialDataTemplate.xlsxFinancial Statements
| ABC Hospital | |||||
| Center City, TX 11111 | |||||
| CMS Certification Number: 100000 | |||||
| Financial Statements | |||||
| Balance Sheet | |||||
| Period ending date | 12/31/18 | 12/31/17 | 12/31/16 | 12/31/15 | 12/31/14 |
| Number of months in period | 12 | 12 | 12 | 12 | 12 |
| Assets | |||||
| Current Assets | $265,779,336 | $243,718,788 | $217,141,619 | $223,579,480 | $197,141,667 |
| Fixed Assets | $596,166,511 | $616,350,881 | $595,730,396 | $512,767,047 | $508,765,419 |
| Other Assets | $854,785,820 | $580,542,351 | $425,717,892 | $382,566,446 | $270,584,786 |
| Total Assets | $1,716,731,667 | $1,440,612,020 | $1,238,589,907 | $1,118,912,973 | $976,491,872 |
| Liabilities and Fund Balances (Equity) | |||||
| Current Liabilities | $120,159,373 | $101,785,629 | $96,551,519 | $66,089,816 | $52,917,296 |
| Long-Term Liabilities | $0 | $0 | $0 | $0 | $0 |
| Total Liabilities | $120,159,373 | $101,785,629 | $96,551,519 | $66,089,816 | $52,917,296 |
| Total Fund Balances (Equity) | $1,596,572,294 | $1,338,826,391 | $1,142,038,388 | $1,052,823,157 | $923,574,576 |
| Total Liabilities & Fund Balances (Equity) | $1,716,731,667 | $1,440,612,020 | $1,238,589,907 | $1,118,912,973 | $976,491,872 |
| Income Statement | |||||
| Period ending date | 12/31/18 | 12/31/17 | 12/31/16 | 12/31/15 | 12/31/14 |
| Number of months in period | 12 | 12 | 12 | 12 | 12 |
| Revenue | |||||
| Inpatient Revenue | $3,352,814,176 | $3,047,442,262 | $2,663,867,539 | $2,675,161,960 | $2,427,740,493 |
| Outpatient Revenue | $2,963,849,688 | $2,694,689,636 | $2,287,032,586 | $2,244,979,664 | $1,853,091,968 |
| Total Patient Revenue | $6,316,663,864 | $5,742,131,898 | $4,950,900,125 | $4,920,141,624 | $4,280,832,461 |
| Contractual Allowance (Discounts) | $4,604,460,855 | $4,156,055,065 | $3,484,066,505 | $3,378,946,746 | $2,859,953,931 |
| Net Patient Revenues | $1,712,203,009 | $1,586,076,833 | $1,466,833,620 | $1,541,194,878 | $1,420,878,530 |
| Total Operating Expense1 | $1,596,004,262 | $1,399,901,765 | $1,365,798,200 | $1,355,376,824 | $1,264,744,556 |
| Operating Income | $116,198,747 | $186,175,068 | $101,035,420 | $185,818,054 | $156,133,974 |
| Other Income (Contributions, Bequests, etc.) | $6,891,214 | $7,499,339 | $5,558,043 | $4,493,004 | $1,679,910 |
| Income from Investments | $1,744,466 | $0 | $0 | $8,064 | $8,411 |
| Governmental Appropriations | $0 | $0 | $0 | $0 | $0 |
| Miscellaneous Non-Patient Revenue | $12,528,126 | $16,355,528 | $25,630,081 | $24,284,201 | $14,407,769 |
| Total Non-Patient Revenue | $21,163,806 | $23,854,867 | $31,188,124 | $28,785,269 | $16,096,090 |
| Total Other Expenses | $0 | $22,672,312 | $593,063 | $1,189,446 | $77,393 |
| Net Income or (Loss) | $137,362,553 | $187,357,623 | $131,630,481 | $213,413,877 | $172,152,671 |
| ____________ | |||||
| 1 Depreciation Expense (included above) | $59,292,107 | $62,375,167 | $61,718,179 | $60,114,081 | $65,355,889 |
| Source: https://www.ahd.com/financial.php?hcfa_id=0e6ed911d02223fd12ca9d585a2c3af1&ek=6403c22aa2c1fb49d9a76994b5ccbc51 |
Common Size
| Common Size Financial Data | ||||||||||||
| Balance Sheet | Period ending date | 12/31/18 | ||||||||||
| Period ending date | 2018 | 2017 | 2016 | 2015 | 2014 | Number of months in period | 12 | |||||
| Assets | Assets | |||||||||||
| Current Assets | 15% | 17% | 18% | 20% | 20% | Current Assets | $265,779,336 | |||||
| Fixed Assets | 35% | 43% | 48% | 46% | 52% | Fixed Assets | $596,166,511 | |||||
| Other Assets | 50% | 40% | 34% | 34% | 28% | Other Assets | $854,785,820 | |||||
| Total Assets | 100% | 100% | 100% | 100% | 100% | Total Assets | $1,716,731,667 | |||||
| Liabilities and Fund Balances (Equity) | Liabilities and Fund Balances (Equity) | |||||||||||
| Current Liabilities | 7% | 7% | 8% | 6% | 5% | Current Liabilities | $120,159,373 | |||||
| Long-Term Liabilities | 0% | 0% | 0% | 0% | 0% | Long-Term Liabilities | $0 | |||||
| Total Liabilities | 7% | 7% | 8% | 6% | 5% | Total Liabilities | $120,159,373 | |||||
| Total Fund Balances (Equity) | 93% | 93% | 92% | 94% | 95% | Total Fund Balances (Equity) | $1,596,572,294 | |||||
| Total Liabilities & Fund Balances (Equity) | 107% | 100% | 100% | 100% | 100% | Total Liabilities & Fund Balances (Equity) | $1,716,731,667 | |||||
| Income Statement | ||||||||||||
| Income Statement | ||||||||||||
| Period ending date | 2018 | 2017 | 2016 | 2015 | 2014 | Period ending date | 12/31/18 | |||||
| Revenue | Number of months in period | 12 | ||||||||||
| Inpatient Revenue | 53% | 53% | 54% | 54% | 57% | Revenue | ||||||
| Outpatient Revenue | 47% | 47% | 46% | 46% | 43% | Inpatient Revenue | $3,352,814,176 | |||||
| Total Patient Revenue | 100% | 100% | 100% | 100% | 100% | Outpatient Revenue | $2,963,849,688 | |||||
| Total Patient Revenue | $6,316,663,864 | |||||||||||
| Contractual Allowance (Discounts) | $4,604,460,855 | |||||||||||
| Net Patient Revenues | $1,712,203,009 | |||||||||||
| Total Operating Expense1 | $1,596,004,262 | |||||||||||
| Operating Income | $116,198,747 | |||||||||||
| Other Income (Contributions, Bequests, etc.) | $6,891,214 | |||||||||||
| Income from Investments | $1,744,466 | |||||||||||
| Governmental Appropriations | $0 | |||||||||||
| Miscellaneous Non-Patient Revenue | $12,528,126 | |||||||||||
| Total Non-Patient Revenue | $21,163,806 | |||||||||||
| Total Other Expenses | $0 | |||||||||||
| Net Income or (Loss) | $137,362,553 | |||||||||||
| ____________ | ||||||||||||
| 1 Depreciation Expense (included above) | $59,292,107 |
Ratios
| ABC Hospital | ||||||||||
| 2018 | 2017 | 2016 | 2015 | 2014 | ||||||
| CURRENT RATIO: | ||||||||||
| Current assets | 265,779,336 | 2.212 | 243718788.000 | 2.394 | 217141619.000 | 2.249 | 223579480.000 | 3.383 | 197141667.000 | 3.725 |
| Current liabilities | 120,159,373 | 101785629.000 | 96551519.000 | 66089816.000 | 52917296.000 | |||||
| DEBT TO TOTAL ASSETS: | ||||||||||
| Total debt | 120,159,373 | 0.070 | 101785629.000 | 0.071 | 96551519.000 | 0.078 | 66089816.000 | 0.059 | 52917296.000 | 0.054 |
| Total assets | 1,716,731,667 | 1440612020.000 | 1238589907.000 | 1118912973.000 | 976491872.000 | |||||
| DEBT TO TOTAL EQUITY: | ||||||||||
| Total debt | 120,159,373 | 0.075 | 101785629.000 | 0.076 | 96551519.000 | 0.085 | 66089816.000 | 0.063 | 52917296.000 | 0.057 |
| Total equity | 1,596,572,294 | 1338826391.000 | 1142038388.000 | 1052823157.000 | 923574576.000 | |||||
| NET PROFIT ON SALES: | ||||||||||
| Net income | 137,362,553 | 0.080 | 187357623.000 | 0.118 | 131630481.000 | 0.090 | 213413877.000 | 0.138 | 172152671.000 | 0.121 |
| Net sales | 1,712,203,009 | 1586076833.000 | 1466833620.000 | 1541194878.000 | 1420878530.000 | |||||
Job Costing
| This tool demonstrates a basic job costing system. The following assumptions apply: (1) There are three specific jobs: X (low care), Y (mid care), and Z (high care) (2) There are two employees: Assistant A and Nurse B (3) There are three days of activity: 1, 2, and 3 (4) There are three types of direct material: AA, BB, and CC. The amount of material used is equal to the number of hours worked by each employee (5) A is paid $15 per hour and B is paid $25 per hour (6) AA costs $11 (mid), BB costs $23 (high), and CC costs $9 (low) (7) Overhead is applied at $20 per direct labor hour (8) Each employee is only allowed to requisition one item of direct material daily | |||||||||||||||
| Complete each time card below by assigning exactly 8 hours of work to each day for each employee (use the pick lists accessible from within the boxed areas; it is up to you to choose how many hours are worked on each job . Administrative time that is not charged to a specific job but is 1 hour for Assistant A and 2 hours for Nurse B each day). As you select the hours keep in mind the Nurse would be mainly assigned to the high care patients where the assistant would mainly be assigned to the low care patients. Similarly, use the pick lists within the material requisition cards to input the materials used by each employee on each day. The job costs sheets will be automatically prepared based on your selections. Discuss what you observed. | |||||||||||||||
| Employee A: Time Card | Employee B: Time Card | ||||||||||||||
| Day | Job | Hours | Daily Total | Day | Job | Hours | Daily Total | X | 0 | ||||||
| 1 | X | 2 | 1 | X | 3 | Y | 1 | ||||||||
| 1 | Y | 4 | 1 | Y | 2 | Z | 2 | ||||||||
| 1 | Z | 2 | 1 | Z | 3 | Admin | 3 | ||||||||
| 1 | Admin | 8 | 1 | Admin | 8 | 4 | |||||||||
| 2 | X | 3 | 2 | X | 4 | 5 | |||||||||
| 2 | Y | 2 | 2 | Y | 3 | 6 | |||||||||
| 2 | Z | 3 | 2 | Z | 1 | 7 | |||||||||
| 2 | Admin | 8 | 2 | Admin | 8 | 8 | |||||||||
| 3 | X | 4 | 3 | X | 1 | ||||||||||
| 3 | Y | 3 | 3 | Y | 5 | ||||||||||
| 3 | Z | 1 | 3 | Z | 2 | ||||||||||
| 3 | Admin | 8 | 3 | Admin | 8 | ||||||||||
| Employee A: Material Requisition | Employee B: Material Requisition | - | |||||||||||||
| Day | Job | Item | Qty | Day | Job | Item | Qty | AA | |||||||
| 1 | X | AA | 1 | X | BB | BB | |||||||||
| 1 | Y | BB | 1 | Y | AA | CC | |||||||||
| 1 | Z | AA | 1 | Z | BB | ||||||||||
| 2 | X | BB | 2 | X | BB | ||||||||||
| 2 | Y | AA | 2 | Y | AA | ||||||||||
| 2 | Z | BB | 2 | Z | CC | ||||||||||
| 3 | X | BB | 3 | X | CC | ||||||||||
| 3 | Y | AA | 3 | Y | BB | ||||||||||
| 3 | Z | CC | 3 | Z | AA | ||||||||||
| JOB Low Care Cost Sheet | |||||||||||||||
| Direct Labor | Materials | Overhead | |||||||||||||
| Day 1 | |||||||||||||||
| Employee | Hours | Rate | Total | Item | Qty | Cost Per | Total | $20 per DL hour | |||||||
| A | 2 | $ 15.00 | $ 30.00 | AA | 0 | $ 11.00 | $ - 0 | $ 40.00 | |||||||
| B | 3 | 25.00 | 75.00 | BB | 0 | 23.00 | - 0 | 60.00 | |||||||
| Day 2 | |||||||||||||||
| A | 3 | 15.00 | 45.00 | BB | 0 | 23.00 | - 0 | 60.00 | |||||||
| B | 4 | 25.00 | 100.00 | BB | 0 | 23.00 | - 0 | 80.00 | |||||||
| Day 3 | |||||||||||||||
| A | 4 | 15.00 | 60.00 | BB | 0 | 23.00 | - 0 | 80.00 | |||||||
| B | 1 | 25.00 | 25.00 | CC | 0 | 9.00 | - 0 | 20.00 | |||||||
| $675.00 | = | $ 335.00 | $ - 0 | $ 340.00 | |||||||||||
| JOB Mid Care Cost Sheet | |||||||||||||||
| Direct Labor | Materials | Overhead | |||||||||||||
| Day 1 | |||||||||||||||
| Employee | Hours | Rate | Total | Item | Qty | Cost Per | Total | $20 per DL hour | |||||||
| A | 4 | $ 15.00 | $ 60.00 | BB | 0 | $ 23.00 | $ - 0 | $ 80.00 | |||||||
| B | 2 | 25.00 | 50.00 | AA | 0 | 11.00 | - 0 | 40.00 | |||||||
| Day 2 | |||||||||||||||
| A | 2 | 15.00 | 30.00 | AA | 0 | 11.00 | - 0 | 40.00 | |||||||
| B | 3 | 25.00 | 75.00 | AA | 0 | 11.00 | - 0 | 60.00 | |||||||
| Day 3 | |||||||||||||||
| A | 3 | 15.00 | 45.00 | AA | 0 | 11.00 | - 0 | 60.00 | |||||||
| B | 5 | 25.00 | 125.00 | BB | 0 | 23.00 | - 0 | 100.00 | |||||||
| $765.00 | = | $ 385.00 | $ - 0 | $ 380.00 | |||||||||||
| JOB High Care Cost Sheet | |||||||||||||||
| Direct Labor | Materials | Overhead | |||||||||||||
| Day 1 | |||||||||||||||
| Employee | Hours | Rate | Total | Item | Qty | Cost Per | Total | $20 per DL hour | |||||||
| A | 2 | $ 15.00 | $ 30.00 | AA | 0 | $ 11.00 | $ - 0 | $ 40.00 | |||||||
| B | 3 | 25.00 | 75.00 | BB | 0 | 23.00 | - 0 | 60.00 | |||||||
| Day 2 | |||||||||||||||
| A | 3 | 15.00 | 45.00 | BB | 0 | 23.00 | - 0 | 60.00 | |||||||
| B | 1 | 25.00 | 25.00 | CC | 0 | 9.00 | - 0 | 20.00 | |||||||
| Day 3 | |||||||||||||||
| A | 1 | 15.00 | 15.00 | CC | 0 | 9.00 | - 0 | 20.00 | |||||||
| B | 2 | 25.00 | 50.00 | AA | 0 | 11.00 | - 0 | 40.00 | |||||||
| $480.00 | = | $ 240.00 | $ - 0 | $ 240.00 | |||||||||||
&"Myriad Web Pro,Bold"&20I-17.03
