In: Accounting
Guidelines:
Create an excel workbook with two tabs:
Statement of Operations (1st tab)
Balance Sheet (2nd tab)
Use the word bank provided to create the rows of your statements. For the statement of operations, create columns for 2017 (current year) -2020. The ASC is projected to open in January 2018. Use the financial statement exhibits from your textbook reading as a guideline.
Once you have sorted out the word list into financial statements in Excel, insert formulas to show how you would calculate various items (such as Current Assets or Total Personnel Expense). You do not need to plug in values, but show how you would calculate them in Excel.
Submit your assignment as an excel (.xlsx) file.
Grading:
100 points based on the correctness of your answer.
Due Date: This part of the Course Project is due on the last night of week 2.
Word Bank: Section headings are in Mixed Case Italics Bold, totals are in UPPERCASE BOLD.
Contractual Adjustment |
Gross Patient Services Revenue |
Admin Salaries Expense |
Admin Benefits Expense |
NET PROPERTY PLANT & EQUIPMENT |
Long Term Investment |
TOTAL LONGTERM ASSETS |
Charity Care Adjustment |
Bad Debt Adjustment |
TOTAL OPERATING EXPENSES |
General & Administrative Expense |
Other Accounts Payable |
Salaries Payable |
Interest Expense |
Property Plant & Equipment |
Less: Accumulated Depreciation |
TOTAL OPERATING INCOME |
EXCESS OF REVENUES OVER EXPENSES |
NET PATIENT REVENUE |
Other Revenue |
TOTAL CURRENT LIABILITIES |
Long Term Liabilities |
Clinical Benefits Expense |
Revenues |
Allocated Overhead Salaries And Benefits Expense |
TOTAL PERSONNEL EXPENSE |
TOTAL LIABILITIES |
Net Assets |
Drugs & Supply Expense |
TOTAL ASSETS |
TOTAL CURRENT ASSETS |
Current Liabilities |
TOTAL OPERATING REVENUES |
TOTAL LIABILITIES AND NET ASSETS |
Clinical Salaries Expense |
Expenses |
Facilities & Equipment Expense |
Net Patient Accounts Receivable |
Inventory |
Depreciation Expense |
Current Assets |
Cash |
Particulars | Budget | Actual | Particulars | Budget | Actual | |
Revenues: | ||||||
Online Sales Revenue | Current Assets: | |||||
Sales Return | Cash | |||||
Net Revenue | =B5-B6 | =C5-C6 | Net Accounts Receivable | |||
Instore Sales Revenue | Prepaid Expenses | |||||
Total OperatingRevenue | =B7+B8 | =C7+C8 | Short term investments | |||
Inventory | ||||||
Cost of Goods Sold | Total Current Assets | =SUM(F6:F10) | =SUM(G6:G10) | |||
Contribution Margin | =B9-B11 | =C9-C11 | Long Term Assets: | |||
Expenses: | Property Plant and Equipment | |||||
Managerial Salaries Expense | Less: Accumulated Depreciation | |||||
Managerial Benefit Expense | Net Property Plant and Equipment | =F13-F14 | =G13-G14 | |||
Employee Benefit Expense | Long Term Ivnestments | |||||
Contributions | ||||||
Employess Wages Expense | Total Long Term Assets | =F15+F16 | =G15+G16 | |||
Total Personnel Expenses | =SUM(B14:B18) | =SUM(C14:C18) | Total Assets | =F11+F18 | =G11+G18 | |
Bad Debt Adjustments | ||||||
Utilities Expense | Current Liabilities: | |||||
Drugs and Supplies Expenses | Accrued Expense Payble | |||||
Depreciation Expense | Salaries Payable | |||||
Other Accoutns Payable | ||||||
Total Operating Expense | =SUM(B19:B23) | =SUM(C19:C23) | Total Current Liabilities | =SUM(F22:F24) | =SUM(G22:G24) | |
Long Term Liabilities | ||||||
Total Operating Income | =B12-B25 | =C12-C25 | Long Term Loan Payable | |||
Total Long Term Liabilities | =F25+F26+F27 | =G25+G26+G27 | ||||
Interest Expense | Total Liabilities | =F28+F25 | =G28+G25 | |||
Income before income Tax | =B27-B29 | =C27-C29 | Owner's Equity | |||
Income Tax | Retained Earning | |||||
Net Income | =B31-B32 | =C31-C32 | ||||
Total Liabilities and Net Assets | =F29+F31+F32 |
=G29+G31+G32 |
please rate this effort if you are satisfied