In-Class ETL Exercise - Arsh D.

docx

School

University of Texas, Dallas *

*We aren’t endorsed by this school

Course

6301

Subject

Accounting

Date

Apr 3, 2024

Type

docx

Pages

Uploaded by CommodoreNeutron102

ITSS 4351 In-Class Exercise and Assignment This project will give you a chance to show your knowledge and mastery of the 4 major learning objectives of the course: Relationship of IT and Business Strategies, Excel and other business tools, major business processes, and design/operation of systems that perform/automate them. Your assignment documents will include Excel worksheets (wo, and any other supporting information, which will be submitted and graded in eLearning Arsh Durrani ITSS 4351.001 Professor Timothy Stephens 3/3/24 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 practice’s 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, in a worksheet tab called ‘Imported’. 1

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. 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.) 1. From the normalized database of the quarterly transaction summary by identifying repeating fields, identify and document the appropriate tables (worksheets), by creating an entity relationship diagram, showing appropriate primary and foreign keys. Are there any levels further than 1NF? Entity Relationship Diagram (ERD) 2. Identify fields in each table as Dimension or Fact. 2

Dimensions: Journal – Journal_Date, Journal_Description Transaction – Transaction_Description Account(s) – Account_Description Account Type – Account_Type_Description Facts:  Journal_ID  Transaction_ID  Transaction_Account_ID  Account_ID  Account_Type_ID 3. Create a high-level Star Schema at the 1NF. This might require a transformation of an intermediate table (see Accounts and AcctType). Remember that a Star Schema is defined as a single fact table with two or more dimension tables in the 1NF. (Transformation of intermediate table(s) Accounts and Account Type) Star Schema 4. Create a new worksheet called DWCube, which will contain your data warehouse. Note that you will include both facts and dimensions in the data warehouse. Use the VLOOKUP function 3

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

(or XLOOKUP) to display any dimensional attributes. This ‘transformation’ brings your data warehouse into a cube structure. This is also a check to make sure your schema is 1NF. 5. From the DWCube, you can build Trial Balance (Debits=Credits) using a pivot table. 4

6. **For extra credit, you can build a Balance Sheet and Profit/Loss. Remember that you might have to transform the amounts to appropriate +/-, within the pivot table.** 5