The Business Case Renowned physiclan, Dr. May Kwell, is opening a new cinic in the area. She has formed a new entity, Dr. May Kwell, MD, LLC, opened a bank account, and deposited S100,000 as seed money for the new clinic. Dr. Kwell was able to lease some med cal office space, however, she had to purchase some officre computers, as wel as some laboratory and diagnost c equ pment, for which she secured a loan of $200,000 at 6% interest. A financial manager, Cal Clate, was hired at a sary of $84,000 per year, and a nurse was hredat S60,000 per year. Dr. Kwell has decided not to take a salary until there s enoush cash to do so. Until there s enough cap tal to purchase an ERP system, Mr. Clate created a rudimentary accounting aystem to develop a chart of accounts, record accounting transactons, and to manage account balances. He sawes the summary ofa transact ons and accounts at the end of each quarter. He has also included an end of year closing adjustment, recognizing the profit and retained earnings for the year The Exercise The clinic has been in operation for just over a year, and Mr. Cate has hired you to develop a management report workbook, which will include quarterly financlal statements Balance Sheet, Prof tLoss, etc.l, alongw th some anass and forecasting Create a normallzed database of the transactions by creating the appropriate tables workshees, and appropriate primary and fore gn keys. 1. a. (20 points) Create a Chart of Accounts Table, and nae t "cOA b 10 points) Create a Transactons Table, and name it Trans. Be sure to utilze the relationship between the accounts in the Trans table to the chart of accounts c Bonus [20 po nts: Create an ACCESS database, by importing the two Excel besworksheets above 2. Create asngle reporting database/worksheet, en the chart of accounts and the newy crezted tra15action table. 10 points) This will be source informatio detal account information, such as account name, type, etc. (int: This will look almost dentical to the original imported worksheet, except for the references Bon s Excel to automate the creation (appending records tol the pwot table source table. You may test your process by creating the 1" period of 2018 by copying the 4h accounting 2. for report Plot Tabis, so be sure to include b. points: Since ths process will be repeated every quarter, create macro in period transactons and changing the period and year. Hint: the more you can automate with your macro, the greater the amount of bonus credit.l c Bonus [20 po nts: Create an ACCESS query, show ngall the transact on information, along w th the re ated (and linked account information 3. Create ear end financlal statements from the database above. These are management reports, so make the formats and titles appropriate for the management audience. a. 10 Points Trial Balance, showing all accounts and their balances through each accounting per od b. (5 Pontsl Balance Sheet, showing the year end balances (5 Ponts: Profit/Loss Statement, showing the activity during before the year end adjustment. ach accounting perod 4. Focus ng on operat onal costs, create a serles of charts and graphs to show the performance of key ind cators, by quarter, over the course of the year. a. 5 pointsl Revenues, Expenses, and Profit (Hint: Combination graph of bars and lines) quarter b. 5 pointsl Sources and Uses of Cash-you can assume all expenses are cash-based 5. (10 points) Create a Table of Contents for the reports and analyses, alon wth other Tinles and Formattng for a Profess onal Presentation. (20 points) Create a combination Process Flow map of the entire process, beginning with entering detail transactions into MrCt Excel system, to the creat on of the report workbook. Also, createa Re ationship Dagram of the two tables inwobed. Hint: the data flow will include tout not lim tedtol the onginal Excel system, chart of accounts, quarterly transactions, excel data and pivot tables. Create pdf f es or bitmaps) of the diagrams and embed into a worksheet in the reporting workbook. 6 TX_Acct_Desc Cash Paid In Capital Notes Payable TX_AcctIDTX_DrAmt TX CrAmt TXDescription Acct_Type Initial Cash In 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 101 100,000.00 901 100,000.00 I Initial Cash In 200,000.00iSecure Loan Equity Liability 101 200,000.00 180 200,000.00 101 500 7,000.00 500 5,000.00 Equipment Cash Salaries Expense Salaries Expense Cash Rent Expense Purchase Equipment 200,000.00 Purchase Equipment Asset Clate Salary Nurse Salary 560 5,000.00 101 570 4,000.00 101 620 1,000.00 210 20,000.00 12,000.00 Payroll 5,000.00i Rent 4,000.00 Office Supplies Rent Office Supplies Interest Payment Asset Expense Asset Office Expense Interest Expense Notes Payable Principle Payment 21,000.00 Loan Payment 100,000.00 Patient Charge Revenue Asset Revenue 410 415 65,000.00 101 10,000.00 120 25,000.00 750 4,000.00 Patient Revenue Contractual Discounts Cash Accounts Receivable Depreciation Expense Accumulated Depreciation Salaries Expense Salaries Expense Contractual Discounts Cash Collections Receivables Recognition Equipment Depreciation Asset 4,000.00 Equipment Depreciation Asset Clate Sala Nurse Salary 500 7,000.00 500 5,000.00 101 560 5,000.00 101 570 5,000.00 101 12,000.00 Payroll 5,000.00 i Rent 5,000.00,Office Supplies Asset Expense Asset Rent Expense Office Expense Cash Interest Expense Notes Payable 12 900.00 Interest Payment Principle Payment Liability 210 20,000.00 101 20,900.00 Loan Payment 200,000.00 Patient Charge Revenue 13 Patient Revenue Contractual Discounts Revenue 415 150,000.00 Contractual Discounts ue 101 25,000.00 120 25,000.0 750 4,000.00 185 500 7000.00 500 5,000.00 101 560 5,000.00 101 570 5,000.00 101 620 210 20,000.00 Cash Collections Receivables Recognition Equipment Depreciation Cash Accounts Receivable Depreciation Expense Accumulated Depreciation Salaries Expense Salaries Expense Cash Rent Expense Cash Office Expense Cash Interest Expense Notes Payable Cash Asset 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 Expense Asset Expense Expense 4,000.00 Equipment Depreciation clate Salary Nurse Salary 20 20 12,000.00 Payroll 5,000.00 Rent 5,000.00iOffice Supplies Rent Expen se Office Supplies Expense Asset Expense Liability 23 800.00 Interest Payment Principle Payment 20,800.00 Loan Payment 200,000.00 Patient Charge Revenue Patient RevenueAsset 2017 2017 2017 2017 2017 2017 2017 2017 Revenue Revenue Asset Asset Expense 4 415 150,000.00 101 20,000.00 120 30,000.00 750 4,000.00 185 99 55,300.00 910 Contractual Discounts Cash Collections Receivables Recognition Equipment Depreciation Contractual Discounts Cash Accounts Receivable Depreciation Expense Accumulated DepreciationAsset Net Income Summary Retained Earnings 25 4,000.00 Equipment Depreciation Year-End Income Close Revenue Equity 55,300.00 Year-End Income Close The Business Case Renowned physiclan, Dr. May Kwell, is opening a new cinic in the area. She has formed a new entity, Dr. May Kwell, MD, LLC, opened a bank account, and deposited S100,000 as seed money for the new clinic. Dr. Kwell was able to lease some med cal office space, however, she had to purchase some officre computers, as wel as some laboratory and diagnost c equ pment, for which she secured a loan of $200,000 at 6% interest. A financial manager, Cal Clate, was hired at a sary of $84,000 per year, and a nurse was hredat S60,000 per year. Dr. Kwell has decided not to take a salary until there s enoush cash to do so. Until there s enough cap tal to purchase an ERP system, Mr. Clate created a rudimentary accounting aystem to develop a chart of accounts, record accounting transactons, and to manage account balances. He sawes the summary ofa transact ons and accounts at the end of each quarter. He has also included an end of year closing adjustment, recognizing the profit and retained earnings for the year The Exercise The clinic has been in operation for just over a year, and Mr. Cate has hired you to develop a management report workbook, which will include quarterly financlal statements Balance Sheet, Prof tLoss, etc.l, alongw th some anass and forecasting Create a normallzed database of the transactions by creating the appropriate tables workshees, and appropriate primary and fore gn keys. 1. a. (20 points) Create a Chart of Accounts Table, and nae t "cOA b 10 points) Create a Transactons Table, and name it Trans. Be sure to utilze the relationship between the accounts in the Trans table to the chart of accounts c Bonus [20 po nts: Create an ACCESS database, by importing the two Excel besworksheets above 2. Create asngle reporting database/worksheet, en the chart of accounts and the newy crezted tra15action table. 10 points) This will be source informatio detal account information, such as account name, type, etc. (int: This will look almost dentical to the original imported worksheet, except for the references Bon s Excel to automate the creation (appending records tol the pwot table source table. You may test your process by creating the 1" period of 2018 by copying the 4h accounting 2. for report Plot Tabis, so be sure to include b. points: Since ths process will be repeated every quarter, create macro in period transactons and changing the period and year. Hint: the more you can automate with your macro, the greater the amount of bonus credit.l c Bonus [20 po nts: Create an ACCESS query, show ngall the transact on information, along w th the re ated (and linked account information 3. Create ear end financlal statements from the database above. These are management reports, so make the formats and titles appropriate for the management audience. a. 10 Points Trial Balance, showing all accounts and their balances through each accounting per od b. (5 Pontsl Balance Sheet, showing the year end balances (5 Ponts: Profit/Loss Statement, showing the activity during before the year end adjustment. ach accounting perod 4. Focus ng on operat onal costs, create a serles of charts and graphs to show the performance of key ind cators, by quarter, over the course of the year. a. 5 pointsl Revenues, Expenses, and Profit (Hint: Combination graph of bars and lines) quarter b. 5 pointsl Sources and Uses of Cash-you can assume all expenses are cash-based 5. (10 points) Create a Table of Contents for the reports and analyses, alon wth other Tinles and Formattng for a Profess onal Presentation. (20 points) Create a combination Process Flow map of the entire process, beginning with entering detail transactions into MrCt Excel system, to the creat on of the report workbook. Also, createa Re ationship Dagram of the two tables inwobed. Hint: the data flow will include tout not lim tedtol the onginal Excel system, chart of accounts, quarterly transactions, excel data and pivot tables. Create pdf f es or bitmaps) of the diagrams and embed into a worksheet in the reporting workbook. 6 TX_Acct_Desc Cash Paid In Capital Notes Payable TX_AcctIDTX_DrAmt TX CrAmt TXDescription Acct_Type Initial Cash In 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 101 100,000.00 901 100,000.00 I Initial Cash In 200,000.00iSecure Loan Equity Liability 101 200,000.00 180 200,000.00 101 500 7,000.00 500 5,000.00 Equipment Cash Salaries Expense Salaries Expense Cash Rent Expense Purchase Equipment 200,000.00 Purchase Equipment Asset Clate Salary Nurse Salary 560 5,000.00 101 570 4,000.00 101 620 1,000.00 210 20,000.00 12,000.00 Payroll 5,000.00i Rent 4,000.00 Office Supplies Rent Office Supplies Interest Payment Asset Expense Asset Office Expense Interest Expense Notes Payable Principle Payment 21,000.00 Loan Payment 100,000.00 Patient Charge Revenue Asset Revenue 410 415 65,000.00 101 10,000.00 120 25,000.00 750 4,000.00 Patient Revenue Contractual Discounts Cash Accounts Receivable Depreciation Expense Accumulated Depreciation Salaries Expense Salaries Expense Contractual Discounts Cash Collections Receivables Recognition Equipment Depreciation Asset 4,000.00 Equipment Depreciation Asset Clate Sala Nurse Salary 500 7,000.00 500 5,000.00 101 560 5,000.00 101 570 5,000.00 101 12,000.00 Payroll 5,000.00 i Rent 5,000.00,Office Supplies Asset Expense Asset Rent Expense Office Expense Cash Interest Expense Notes Payable 12 900.00 Interest Payment Principle Payment Liability 210 20,000.00 101 20,900.00 Loan Payment 200,000.00 Patient Charge Revenue 13 Patient Revenue Contractual Discounts Revenue 415 150,000.00 Contractual Discounts ue 101 25,000.00 120 25,000.0 750 4,000.00 185 500 7000.00 500 5,000.00 101 560 5,000.00 101 570 5,000.00 101 620 210 20,000.00 Cash Collections Receivables Recognition Equipment Depreciation Cash Accounts Receivable Depreciation Expense Accumulated Depreciation Salaries Expense Salaries Expense Cash Rent Expense Cash Office Expense Cash Interest Expense Notes Payable Cash Asset 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 Expense Asset Expense Expense 4,000.00 Equipment Depreciation clate Salary Nurse Salary 20 20 12,000.00 Payroll 5,000.00 Rent 5,000.00iOffice Supplies Rent Expen se Office Supplies Expense Asset Expense Liability 23 800.00 Interest Payment Principle Payment 20,800.00 Loan Payment 200,000.00 Patient Charge Revenue Patient RevenueAsset 2017 2017 2017 2017 2017 2017 2017 2017 Revenue Revenue Asset Asset Expense 4 415 150,000.00 101 20,000.00 120 30,000.00 750 4,000.00 185 99 55,300.00 910 Contractual Discounts Cash Collections Receivables Recognition Equipment Depreciation Contractual Discounts Cash Accounts Receivable Depreciation Expense Accumulated DepreciationAsset Net Income Summary Retained Earnings 25 4,000.00 Equipment Depreciation Year-End Income Close Revenue Equity 55,300.00 Year-End Income Close