The Business Case Renowned physician, Dr. May Kwell, is considering purchasing the medical practice of the only other physician in the city with her specialty, who has announced his retirement. She has formed a new entity, Dr. May Kwell, MD, LLC, opened a bank account, and deposited $100,000 as seed money for the venture. Dr. Kwell will be able keep the patients of the practice, and to use the same medical office space; however, she will need to purchase new office computers and network, develop a web presence, and purchase some updated laboratory and diagnostic equipment. She is attempting to secure a loan of $400,000 at estimated 6% annual interest to purchase the practice and equipment. The current office staff consists of Office/Financial manager at a salary of $84,000 per year, administrative support at $48,000 annually, and a nurse at $60,000 per year. However, the current financial manager has left the organization to take a position in a different city. Dr. Kwell has decided not to take a salary until there is enough cash to do so. You have been hired as the new Business manager and will be responsible generating the necessary reports for aiding Dr. Kwell in securing a loan for the practice, along with managing and reporting all the financial activities of the practice. The practices financial records have been kept by on personal computer running a rudimentary accounting system, which records accounting transactions, manages account balances, and creates files which can be uploaded into Excel for developing reports. Before leaving, the Office/Financial manager created a summary of all transactions by debit/credit accounts for each quarter for the last 4 years. The summary also includes the end-of-year closing adjustments, recognizing the profit and retained earnings for each of the years. The data is stored in an Excel format file. The Exercise As part of the loan application process, the bank has asked Dr. Kwell to submit a business plan for the medical practice. She will address most of the strategic issues; however, you are assigned the tasks of describing the operations of the business along with associated technical requirements and providing financial forecasts. 1. Lets focus first on the operations: a. What would be the major operational areas in the clinic (for example, supply chain)? b. Identify at least two major processes within each operational area. c. Format your response (in a Word or PowerPoint document), assuming Dr. Kwell and/or bank loan processing staff is/are the intended audience. You must now create a management reporting workbook, which will include quarterly basic financial statements (Trial Balance, Balance Sheet, Profit/Loss, etc.). You will need to utilize the data in the Excel file to create the reports. (Note that the data is summarized by debit/credit accounts within each quarter/reporting period.) 2. Create a normalized database of the quarterly transaction summary by identifying repeating fields, creating the appropriate tables (worksheets), and appropriate primary and foreign keys. a. Create a Chart of Accounts Table, and name it COA b. Create a Transactions Table, and name it TRANS, eliminating repeating fields which are stored in the Chart of Accounts Table. c. BONUS: Create an ACCESS database, by importing the two Excel tables/worksheets above. d. Create a single reporting database/worksheet, given the chart of accounts and the newly created transaction table. This will be raw source information for report Pivot Tables, so be sure to include detail account information, such as account name, type, etc. Also, utilize the relationship between the accounts in the Trans table to the chart of accounts, using the VLOOKUP function. (Hint: This will look almost identical to the original imported worksheet, except for the lookup formulas, instead of text description of the account) e. BONUS: Create an ACCESS query, showing all the transaction information, along with the related (and linked) account information. 3. Create the Management Book of year-end financial statements from the raw source in the previous exercise, utilizing Excel pivot tables. These are management reports, so make the formats and titles appropriate for the management audience. a. Trial Balance, showing all accounts and their balances through each accounting period. b. Profit/Loss Statement, showing the activity during each accounting period, before the year-end adjustment. c. Balance Sheet, showing the year-end balances. d. Chart/Graph showing revenues, expenses, and profit by quarter. e. Create a Table of Contents Page for the reports and analyses, along with other titles and formatting for a professional presentation. 4. BONUS: Financial forecasts are created by applying a set of assumptions to current data, to project a future state. With the following assumptions and processes, create a pro forma report: a. Create a new worksheet called FORECAST. b. Copy the transactions of the previous year from the TRANS table into the FORECAST table, and changing amounts in the individual transactions to reflect the following assumptions: (Hint: Copy the amount values into unused adjacent cells, and use formulas to calculate the increases/decreases) i. Patient revenues will increase 18%. Discounts will thus increase 18% as well. ii. Office expenses will increase by 3%. iii. Dr. Kwell would like to begin taking a salary of $100,000 per year ($25,000 per quarter). iv. Remember to change the reporting Year to reflect next year in the FORECAST table. c. Create a Pro Forma Profit/Loss Report, utilizing a pivot table based on the newly created FORECAST table/worksheet. This report can be included in Management Book that you created in the above step. 5. To limit the risk and exposure of losing the clinics corporate knowledge, it is good business practice to document the major processes, including the process steps/activities involved in generating the quarterly report book (above). You can also use this information to automate the process, and hopefully minimize the chances for variation or error. a. Create a combination process flow map (swim lane) of the process of producing the Management Book, beginning with downloading the summary transactions from the accounting system, to the creation and presentation of the report workbook. (Create pdf files or bitmaps of the diagrams and embed into a worksheet in the reporting workbook, or other MS Word submission document). b. Create a Data Flow/Relationship Diagram of the tables involved, showing scenarios, key players, and data stores. Hint: the data stores will include (but not limited to) the original accounting system, chart of accounts (COA), quarterly transactions (TRANS), and pivot tables. c. BONUS: Since this process will be repeated every quarter, create a macro in Excel to automate the creation (appending records to) the pivot table source table. You may test your process by appending the data residing in FORECAST back into the TRANS table. (Hint: the more you can automate with your macro, the greater the amount of bonus credit.)

Tell me what you want to do Help Developer View Review Data Formulas Page Layout Insert Home File Cut ab Wrap Text General A A 11 Calibri Copy Paste Cell Conditional Format as 00 .0 A % Merge & Center B IU .000 Formatting Table Styles Format Painter Styles Number Alignment Font Clipboard fox C15 X 2018 H A D E G 1 TX ID TX_Yr TX_AcctID TX DrAmt TX_CrAmt TX Description 101 100,000.00 TX_Acct_Type TX Pd TX Acct Desc Cash 2 2018 Asset Initial Cash In 2018 100,000.00 Initial Cash In Paid In Capital Equity Liability 3 1 901 2018 200,000.00 Secure Loan Secure Loan 4 1 Notes Payable 210 101 200,000.00 1 2018 Asset Cash 6 2018 Equipment 3 1 180 200,000.00 Asset Purchase Equipment 200,000.00 Purchase Equipment Clate Salary Nurse Salary 7 3 2018 Asset 101 Cash 8 4 2. 2018 500 Salaries Expense 7,000.00 Expense 9 4 2 2018 500 5,000.00 Expense Salaries Expense 10 4 2. 2018 101 12,000.00 Payroll Cash Asset 11 5 2 2018 560 5,000.00 Rent Rent Expense Expense 12 5 2 2018 101 5,000.00 Rent Cash Asset 13 6 2 2018 570 4,000.00 Office Supplies 4,000.00 Office Supplies Interest Payment Office Expense Expense 14 6 2 2018 101 Cash Asset 15 2 7 2018 620 1,000.00 Interest Expense Expense 16 7 2 2018 210 20,000.00 Principle Payment 21,000.00 Loan Payment Notes Payable Liability 17 7 2 2018 101 Cash Asset 18 8 2. 2018 410 100,000.00 Patient Charge Revenue Patient Revenue Revenue 19 8 2 415 65,000.00 2018 Contractual Discounts Contractual Discounts Revenue 20 2 2018 101 10,000.00 Cash Collections Cash Accounts Receivable Asset 21 2 2018 120 25,000.00 Receivables Recognition 22 Asset 9 2 2018 750 4.000.00 Equipment Depreciation 4.000.00 Eauioment Depreciation Depreciation Expense 23 Expense 9 2018 2 185 Accumulated Depreciatior Asset Imported El Tell me what you want to do Help Developer View Review Data Formulas Page Layout Insert Home File Cut ab Wrap Text General A A 11 Calibri Copy Paste Cell Conditional Format as 00 .0 A % Merge & Center B IU .000 Formatting Table Styles Format Painter Styles Number Alignment Font Clipboard fox C15 X 2018 H A D E G 1 TX ID TX_Yr TX_AcctID TX DrAmt TX_CrAmt TX Description 101 100,000.00 TX_Acct_Type TX Pd TX Acct Desc Cash 2 2018 Asset Initial Cash In 2018 100,000.00 Initial Cash In Paid In Capital Equity Liability 3 1 901 2018 200,000.00 Secure Loan Secure Loan 4 1 Notes Payable 210 101 200,000.00 1 2018 Asset Cash 6 2018 Equipment 3 1 180 200,000.00 Asset Purchase Equipment 200,000.00 Purchase Equipment Clate Salary Nurse Salary 7 3 2018 Asset 101 Cash 8 4 2. 2018 500 Salaries Expense 7,000.00 Expense 9 4 2 2018 500 5,000.00 Expense Salaries Expense 10 4 2. 2018 101 12,000.00 Payroll Cash Asset 11 5 2 2018 560 5,000.00 Rent Rent Expense Expense 12 5 2 2018 101 5,000.00 Rent Cash Asset 13 6 2 2018 570 4,000.00 Office Supplies 4,000.00 Office Supplies Interest Payment Office Expense Expense 14 6 2 2018 101 Cash Asset 15 2 7 2018 620 1,000.00 Interest Expense Expense 16 7 2 2018 210 20,000.00 Principle Payment 21,000.00 Loan Payment Notes Payable Liability 17 7 2 2018 101 Cash Asset 18 8 2. 2018 410 100,000.00 Patient Charge Revenue Patient Revenue Revenue 19 8 2 415 65,000.00 2018 Contractual Discounts Contractual Discounts Revenue 20 2 2018 101 10,000.00 Cash Collections Cash Accounts Receivable Asset 21 2 2018 120 25,000.00 Receivables Recognition 22 Asset 9 2 2018 750 4.000.00 Equipment Depreciation 4.000.00 Eauioment Depreciation Depreciation Expense 23 Expense 9 2018 2 185 Accumulated Depreciatior Asset Imported El