DATA ANALYTICS USING EXCEL PROJECT

Information technology plays a significant role in the accounting profession. As a result, accountants with information technology skills possess an advantage in the job market today. In this project, you are given opportunities to gain and enhance your skills in certain areas of information technology. You will be exposed to ways to (1) collect, (2) gather, and (3) arrange raw data for analysis. You are required to use Microsoft Excel to accomplish your work in this project because Excel has been found to be the number one mostly used tool in the workplace for accountants.

Please be aware that you may want to read through the whole project and its requirements a few times before you think through how you are going to fulfill the requirements. Getting the bottom lines are not the only goal in this project. You must also document your processes and provide the detail in your memo.

Project Description:
TJ Table Tennis Company employs four sales associates, i.e., Adam, Becky, Chris, and Debbie. Every month, these four sales associates will send their monthly sales records in certain formats to the headquarters of the Company. The top management of the TJ Table Tennis Company asks you to come up with an algorithm (i.e., a process) in generating a worksheet for consolidated sales records and a worksheet for a master customer list each month.

Now, you are given their monthly sales records from December, 2020 (i.e., Adam.txt, Becky.txt, Chris.txt, and Debbie.txt).

Data stored in these text files follow the following formats:

  1. Adam.txt (tab is used to separate each field): A) Customer’s name with first name followed by a space and followed by last name; B) mailing address with street address followed by a comma and a space, by city name, by a comma and a space, by a two-digit State abbreviation, by a space, and then by a 5-digit zip code; C) gender; and D) Sales amount from a sale transaction

NAME ADDRESS GENDER SALES
First LastName Street, City, AL 99999 M 1000

  1. Becky.txt (tab is used to separate each field): A) Customer’s name with last name followed by a comma and a space, and then followed by first name; B) mailing address with street address followed by a comma and a space, by city name, by a comma and a space, by a two-digit State abbreviation, by a space, and then by a 5-digit zip code; C) gender; and D) Sales amount from a sale transaction

NAME ADDRESS GENDER SALES
LastName, First St, City, AL 99999 M 1000

  1. Chris.txt (tab is used to separate each field): A) Customer’s name with last name followed by a comma and a space, and then followed by first name; B) Telephone number; and C) Sales amount from a sale transaction

Sample Solution

ACED ESSAYS