Question

In: Accounting

Guidelines: Create an excel workbook with two tabs: Statement of Operations (1st tab) Balance Sheet (2nd...

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

Solutions

Expert Solution

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


Related Solutions

Part I We’ll use the “Debt and Taxes” tab in the Lab 5 Excel Workbook The...
Part I We’ll use the “Debt and Taxes” tab in the Lab 5 Excel Workbook The Economic Data Runs from 1946 (1st year post WW2) to 2016 Note: This issue is tremendously more complicated than the two variables presented here. This is only a partial look at the issue and there is ample room for debate as causes of the issues at hand. 1) Examining the Relationships               Create and copy in the following Charts                              1) Line Chart with...
Using Microsoft Excel, create a balance sheet and an income statement using the relevant information below...
Using Microsoft Excel, create a balance sheet and an income statement using the relevant information below Calculate the Net Working Capital and the Net Operating Working Capital for both years Perform vertical analysis on both statements, for both years Perform Horizontal analysis on both statements for the most current year What was the Net Operating Profit After Taxes for both years? 2016 Accounts Receivable 400 Sales 5000 Accounts Payable 650 Inventory 1400 COGS 3000 Common Stock 1500 Cash 1600 Oper....
Create a Fictitious balance sheet and income statement.
Create a Fictitious balance sheet and income statement.
Create an Excel workbook containing two depreciation schedule templates, each on a separate spreadsheet. One template...
Create an Excel workbook containing two depreciation schedule templates, each on a separate spreadsheet. One template should be a SL depreciation schedule and the other template should be a DDB depreciation schedule. You should construct the spreadsheets using the formulas and cell referencing so that when the value of input variables are altered the calculations which automatically adjust. The spreadsheet columns should include depreciation expense, accumulated depreciation, and book value end of year (see textbook 575-576 for examples). Please note,...
Create an Excel workbook containing two depreciation schedule templates, each on a separate spreadsheet. One template...
Create an Excel workbook containing two depreciation schedule templates, each on a separate spreadsheet. One template should be a SL depreciation schedule and the other template should be a DDB depreciation schedule. You should construct the spreadsheets using the formulas and cell referencing so that when the value of input variables are altered the calculations which automatically adjust. The spreadsheet columns should include depreciation expense, accumulated depreciation, and book value end of year. Please note, you can not depreciate assets...
Create an Excel workbook containing two depreciation schedule templates, each on a separate spreadsheet. One template...
Create an Excel workbook containing two depreciation schedule templates, each on a separate spreadsheet. One template should be a SL depreciation schedule and the other template should be a DDB depreciation schedule. You should construct the spreadsheets using the formulas and cell referencing so that when the value of input variables are altered the calculations which automatically adjust. The spreadsheet columns should include depreciation expense, accumulated depreciation, and book value end of year (see textbook 575-576 for examples). Please note,...
Create an Excel workbook containing two depreciation schedule templates, each on a separate spreadsheet. One template...
Create an Excel workbook containing two depreciation schedule templates, each on a separate spreadsheet. One template should be a SL depreciation schedule and the other template should be a DDB depreciation schedule. You should construct the spreadsheets using the formulas and cell referencing so that when the value of input variables are altered the calculations which automatically adjust. The spreadsheet columns should include depreciation expense, accumulated depreciation, and book value end of year (see textbook 575-576 for examples). Please note,...
Create your own Income Statement, Retained Earnings Statement and Balance Sheet. Do not create a Statement...
Create your own Income Statement, Retained Earnings Statement and Balance Sheet. Do not create a Statement of Cash Flows. Make up the name of the company the accounts and the numbers. You will need to plug the cash (or other account) amount to make the Balance Sheet Balance. Assets = Liabilities + Owner's Equity.
Prepare the Income Statement, Balance Sheet and Statement of Owner's Equity in an Excel File. (1)....
Prepare the Income Statement, Balance Sheet and Statement of Owner's Equity in an Excel File. (1). Mr. Sheng sought an attorney for legal advice to help him start a company, The Sheng Enterprise, which sells native rattan bags. On January 1, 2010, Mr. Sheng invested P2,000,000.00 cash in the business. The attorney advised him to hire an accountant for his bookkeeping. Mr. Sheng paid P1,000.00 for the legal services. However, Mr. Sheng was preoccupied with the product development that was...
Create a new workbook in Excel. Please include an input area and an answer area for...
Create a new workbook in Excel. Please include an input area and an answer area for each problem. You will have an answer for each bullet point. Formatting is up to you. Just make sure it is easy to tell where your answers are, and what equation you used in excel to get the answer. 5. Assume you have won the lottery and have the choice of (1) receiving $1,000,000 now or (2) receiving $120,000 each year for 20 years....
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT