In-Class ETL Exercise - Arsh D
.docx
keyboard_arrow_up
School
University of Texas, Dallas *
*We aren’t endorsed by this school
Course
6301
Subject
Accounting
Date
Apr 3, 2024
Type
docx
Pages
5
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
Related Questions
I need help with a and b
arrow_forward
I need help with B, C, D on the attached assignment
arrow_forward
Digital learning that is synchronous
Select one:
a. involves the use of simulators that replicate the features of a work situation
O b.
seeks to change the attitudes of trainees and develop communication skills
C. is used in group training to enhance interpersonal skills
Od. provides for decision making and the development of problem-solving skills
e. allows for real-time access
arrow_forward
List the business, technological, and organizational challenges that were addressed throughout the project to implement a new learning management system, and explain the three-sphere model for systems management in your own words. Do not depend on the examples in the textbook.
arrow_forward
Ethics case: Uncollectible Accounts
Introduction
The purpose of this discussion forum is to give you an opportunity to apply your critical-thinking ability to the knowledge you’ve gained. The assignment provides you with an opportunity to develop your research, analysis, judgment, and communication skills. You also will work with other students, integrate what you’ve learned, apply it in real-world situations. This practice will broaden your knowledge and further develop your decision-making abilities. The assignment achieves learning objective 5.
The assignment information is given below;
You have recently been hired as the assistant controller for Stanton Industries, a large, publicly held manufacturing company. Your immediate superior is the controller who, in turn, is responsible to the vice president of finance. The controller has assigned you the task of preparing the year-end adjusting entries. In the receivables area, you have prepared an aging of accounts receivable and have…
arrow_forward
Part One: Indicate whether the following sentences are true or false:
In the system perspective of the learning organization, Senge's five disciplines include interrelationships between the organizational events and processes, team learning, shared vision, shared mental models, and personal mastery.
arrow_forward
an external tool
Available n
Mission
Progress
You are on question 12 of 12
Management by objectives is Peter Drucker's version of goal setting His
concept is that employees commit to goals. A manager should review
progress toward individual goals with each employee and then
O make the individual responsible for the next steps
O reward or punish the employee as appropriate
O jointly discuss the implications
Previous
arrow_forward
Case Study: Riversdale College Learning Management SystemTraditional face‐to‐face teaching still plays a critical role in teaching and learning, howeverchallenges such as COVID‐19 has necessitated the introduction of Learning Management Systems(LMS) in higher educational institutions at a large scale. An LMS is a software system designed toaid teaching and learning which operates in the World Wide Web, hence, having the flexibility ofanytime access (Ho, Higson, Dey & Xu, 2009). Riversdale College has invested in a Learning Management system in order to create, share andmanage the delivery of educational content. The LMS goal is in enhancing teaching and learning.The main aim of Riversdale College is to provide excellent educational opportunities that areresponsive to the needs of a demographically diverse student population and equip them toexceed challenges as active participants in shaping the future of our world. Riversdale Collegeuses the LMS in more innovative ways like…
arrow_forward
Industry Tools Research
Your manager has asked you to recommend an industry tool for purchase. Select one of the following tools to research: ACL, IDEA, Microsoft Visio, Oversight, Power BI, Alteryx, and Tableau.
Identify key features of each tool.
Discuss how auditors can incorporate the tool into their audits.
Additional learning opportunities: If you are interested in learning more about data analytics and emerging technologies in accounting, join the Institute of Management Accountants (IMA) for only $25 per year: Students | IMA - The association of accountants and financial professionals working in business.Links to an external site. (Use code Student22). Gain access to Free courses, training, and webinars.
arrow_forward
Depending on the way your practice works, you may need to give team members different access levels based on their roles.
What are the 3 levels of access that can be granted to Team users of QuickBooks Online Accountant?
Full access
VIP access
Basic access
Custom access
Standard access
arrow_forward
1. Journal all transactions in Part One 2. Using the chart of accounts, open ledger accounts and post journals to the ledger account. 3. Prepare a trial balance 4. Prepare the following statements: 1. Income Statement 2. Retained Earnings Statements 3. Balance Sheet You need to use Microsoft Excel. Do not use Google Docs or Apple’s numbers. Place your submission in the appropriate journal labeled final project in the learning modules tab in Blackboard. The project is due in Module 15. Three points extra credit to final grade if submitted during Module 14. Part One A. The following transaction occurred for Scrooge Inc. for the month of December 31, 1820. B. Ebenezer Scrooge invested $50,000 cash along in the company in exchange for common stock. C. The company prepaid $500 for 12 month’s rent. D. The company purchased $100 in office supplies. Payment due withing 10 days E. Scrooge Inc. completed services for a client and immediately received $2,000. F. The company completed $1,500…
arrow_forward
O d. comprehensiveness
Clear
my
choice
A project manager does not understand the meaning of a term used by a team member in a technical document
during a collaborative writing session using wiki (a collaborative writing tool), what would you advise him or her to do?
O a.
Use the highlighting feature to call the writer's attention to the term.
Ob. Delete the term using the revision feature.
O c.
As the project manager to take English training course
O d.
Use the comment feature to ask, "Will the
ader understand this term?
Clear my choice
This excerpt comes from a report on introducing new technology to company employees. Which of the suggestions
listed below will help the writer improve the excerpt?
(1) Research that was conducted for this study dealt in large measure with the means to
introduce new technology to employees who have been employed for long periods of time
and who are resistant to change. (2) In a number of interviews with managers and supervisors,
most of these…
arrow_forward
Understanding components of a computerized accounting information system
Ned Timmons, engineer, is considering using a computerized accounting system for his professional engineering business. Ned has asked that you help him understand the components of a computerized accounting information system by answering the following questions:
Requirements
1. What are the two basic components of a computerized accounting information system?
2. Provide examples of each component.
3. If Ned were interested in an entry-level software system, what software might you recommend?
arrow_forward
Part 3: Kolb's Learning style inventory
a. Briefly describe Kolb's Learning Style Model
b. Take the test and identify your preferred
learning style (Diverging, Assimilating,
Converging or Accommodating) based on the
test. Discuss whether you think that this is
true.
arrow_forward
Give reflection of this homework.
arrow_forward
Following are activities for a provider of online education. Classify each activity as unit level, batch level, service level, or
facility level.
Activity
1. Scheduling instructors
2. Controlling course data
3. Receiving supply shipments
4. Registering a student
5. Creating a new course
6. Maintaining course websites
7. Providing electricity
8. Delivering an online course
Level
arrow_forward
Don't want AI answer
arrow_forward
Project Management Engineering
arrow_forward
23
Balanced scorecard perspective focuses on all operations which lead to value creation process for customers that is customer relationship management is to evaluate_________________.
a.
Learning perceptive
b.
Internal business process perceptive
c.
Customer perceptive
d.
External business perceptive
arrow_forward
E Essay Tips: How to Paraphrase a
* PlayStation 5 Console - Walmart x
aystation 5-antonline.co x
/7355/quizzes/38887/take
O works to achieve goal congruence
O supports the use of expert knowledge
1 pts
Question 3
The performance evaluation system should provide incentives to segment managers for coordinating the
activities of the subunits and directing them toward the overall company goals. Which of the following
performance measurement goals has been described by this statement?
O motivating segment managers
O promoting goal congruence
O providing feedback
O benchmarking
Question 4
Uniox, Inc. intends to increase its profits by 50% in the next fiscal year. Which of the following is most likely
be a lag indicator in Uniox's performance renort?
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you
Pkg Acc Infor Systems MS VISIO CD
Finance
ISBN:9781133935940
Author:Ulric J. Gelinas
Publisher:CENGAGE L
Accounting Information Systems
Finance
ISBN:9781337552127
Author:Ulric J. Gelinas, Richard B. Dull, Patrick Wheeler, Mary Callahan Hill
Publisher:Cengage Learning
Auditing: A Risk Based-Approach to Conducting a Q...
Accounting
ISBN:9781305080577
Author:Karla M Johnstone, Audrey A. Gramling, Larry E. Rittenberg
Publisher:South-Western College Pub
Auditing: A Risk Based-Approach (MindTap Course L...
Accounting
ISBN:9781337619455
Author:Karla M Johnstone, Audrey A. Gramling, Larry E. Rittenberg
Publisher:Cengage Learning
Related Questions
- I need help with a and barrow_forwardI need help with B, C, D on the attached assignmentarrow_forwardDigital learning that is synchronous Select one: a. involves the use of simulators that replicate the features of a work situation O b. seeks to change the attitudes of trainees and develop communication skills C. is used in group training to enhance interpersonal skills Od. provides for decision making and the development of problem-solving skills e. allows for real-time accessarrow_forward
- List the business, technological, and organizational challenges that were addressed throughout the project to implement a new learning management system, and explain the three-sphere model for systems management in your own words. Do not depend on the examples in the textbook.arrow_forwardEthics case: Uncollectible Accounts Introduction The purpose of this discussion forum is to give you an opportunity to apply your critical-thinking ability to the knowledge you’ve gained. The assignment provides you with an opportunity to develop your research, analysis, judgment, and communication skills. You also will work with other students, integrate what you’ve learned, apply it in real-world situations. This practice will broaden your knowledge and further develop your decision-making abilities. The assignment achieves learning objective 5. The assignment information is given below; You have recently been hired as the assistant controller for Stanton Industries, a large, publicly held manufacturing company. Your immediate superior is the controller who, in turn, is responsible to the vice president of finance. The controller has assigned you the task of preparing the year-end adjusting entries. In the receivables area, you have prepared an aging of accounts receivable and have…arrow_forwardPart One: Indicate whether the following sentences are true or false: In the system perspective of the learning organization, Senge's five disciplines include interrelationships between the organizational events and processes, team learning, shared vision, shared mental models, and personal mastery.arrow_forward
- an external tool Available n Mission Progress You are on question 12 of 12 Management by objectives is Peter Drucker's version of goal setting His concept is that employees commit to goals. A manager should review progress toward individual goals with each employee and then O make the individual responsible for the next steps O reward or punish the employee as appropriate O jointly discuss the implications Previousarrow_forwardCase Study: Riversdale College Learning Management SystemTraditional face‐to‐face teaching still plays a critical role in teaching and learning, howeverchallenges such as COVID‐19 has necessitated the introduction of Learning Management Systems(LMS) in higher educational institutions at a large scale. An LMS is a software system designed toaid teaching and learning which operates in the World Wide Web, hence, having the flexibility ofanytime access (Ho, Higson, Dey & Xu, 2009). Riversdale College has invested in a Learning Management system in order to create, share andmanage the delivery of educational content. The LMS goal is in enhancing teaching and learning.The main aim of Riversdale College is to provide excellent educational opportunities that areresponsive to the needs of a demographically diverse student population and equip them toexceed challenges as active participants in shaping the future of our world. Riversdale Collegeuses the LMS in more innovative ways like…arrow_forwardIndustry Tools Research Your manager has asked you to recommend an industry tool for purchase. Select one of the following tools to research: ACL, IDEA, Microsoft Visio, Oversight, Power BI, Alteryx, and Tableau. Identify key features of each tool. Discuss how auditors can incorporate the tool into their audits. Additional learning opportunities: If you are interested in learning more about data analytics and emerging technologies in accounting, join the Institute of Management Accountants (IMA) for only $25 per year: Students | IMA - The association of accountants and financial professionals working in business.Links to an external site. (Use code Student22). Gain access to Free courses, training, and webinars.arrow_forward
- Depending on the way your practice works, you may need to give team members different access levels based on their roles. What are the 3 levels of access that can be granted to Team users of QuickBooks Online Accountant? Full access VIP access Basic access Custom access Standard accessarrow_forward1. Journal all transactions in Part One 2. Using the chart of accounts, open ledger accounts and post journals to the ledger account. 3. Prepare a trial balance 4. Prepare the following statements: 1. Income Statement 2. Retained Earnings Statements 3. Balance Sheet You need to use Microsoft Excel. Do not use Google Docs or Apple’s numbers. Place your submission in the appropriate journal labeled final project in the learning modules tab in Blackboard. The project is due in Module 15. Three points extra credit to final grade if submitted during Module 14. Part One A. The following transaction occurred for Scrooge Inc. for the month of December 31, 1820. B. Ebenezer Scrooge invested $50,000 cash along in the company in exchange for common stock. C. The company prepaid $500 for 12 month’s rent. D. The company purchased $100 in office supplies. Payment due withing 10 days E. Scrooge Inc. completed services for a client and immediately received $2,000. F. The company completed $1,500…arrow_forwardO d. comprehensiveness Clear my choice A project manager does not understand the meaning of a term used by a team member in a technical document during a collaborative writing session using wiki (a collaborative writing tool), what would you advise him or her to do? O a. Use the highlighting feature to call the writer's attention to the term. Ob. Delete the term using the revision feature. O c. As the project manager to take English training course O d. Use the comment feature to ask, "Will the ader understand this term? Clear my choice This excerpt comes from a report on introducing new technology to company employees. Which of the suggestions listed below will help the writer improve the excerpt? (1) Research that was conducted for this study dealt in large measure with the means to introduce new technology to employees who have been employed for long periods of time and who are resistant to change. (2) In a number of interviews with managers and supervisors, most of these…arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Pkg Acc Infor Systems MS VISIO CDFinanceISBN:9781133935940Author:Ulric J. GelinasPublisher:CENGAGE LAccounting Information SystemsFinanceISBN:9781337552127Author:Ulric J. Gelinas, Richard B. Dull, Patrick Wheeler, Mary Callahan HillPublisher:Cengage LearningAuditing: A Risk Based-Approach to Conducting a Q...AccountingISBN:9781305080577Author:Karla M Johnstone, Audrey A. Gramling, Larry E. RittenbergPublisher:South-Western College Pub
- Auditing: A Risk Based-Approach (MindTap Course L...AccountingISBN:9781337619455Author:Karla M Johnstone, Audrey A. Gramling, Larry E. RittenbergPublisher:Cengage Learning
Pkg Acc Infor Systems MS VISIO CD
Finance
ISBN:9781133935940
Author:Ulric J. Gelinas
Publisher:CENGAGE L
Accounting Information Systems
Finance
ISBN:9781337552127
Author:Ulric J. Gelinas, Richard B. Dull, Patrick Wheeler, Mary Callahan Hill
Publisher:Cengage Learning
Auditing: A Risk Based-Approach to Conducting a Q...
Accounting
ISBN:9781305080577
Author:Karla M Johnstone, Audrey A. Gramling, Larry E. Rittenberg
Publisher:South-Western College Pub
Auditing: A Risk Based-Approach (MindTap Course L...
Accounting
ISBN:9781337619455
Author:Karla M Johnstone, Audrey A. Gramling, Larry E. Rittenberg
Publisher:Cengage Learning