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.
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
2. 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
3. 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
NAME TELEPHONE SALES
LastName, First 19085555555 1000
4. Debbie.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) Telephone number; and C) Sales amount from a sale transaction
NAME TELEPHONE SALES
First LastName 19085555555 1000
Note: customers may have multiple sales records/rows and have more than one sales associates
By looking at the formats of these files, you will see that
a) Only Adam.txt and Becky.txt contain Address and Gender (but without Telephone) information.
b) Only Chris.txt and Debbie.txt contain Telephone (but without Address and Gender) information.
c) With the name, Adam.txt and Debbie.txt record first name, a space, and last name, while Becky.txt and Chris.txt record Last name, a comma and a space, and first name format.
From these four files with different formats, the top management of the TJ Table Tennis Company would like you to create a consolidated Sales Record and a master Customer list for the month of December.
So, you, the Chief Accountant of the Company, are asked to perform the following three tasks:
(1) Document the algorithm in a business memo to me
It is preferred that you come up with a conceptual plan for the process of composing sales records and master customer list before you actually implement it. You may need to revise it when you carry out your plan. If so, revise your plan. Once you are done with the tasks, you may want to re-do the whole process to ensure that your recorded process works. Of course, you may want to carry out your actions without having a complete plan. In that case, you need to keep track of what works one at a time and repeat the whole thing once you can make it to work.
Please keep in mind that you have to perform this process every month in the future. So, it is preferable that you can just copy and paste the functions over after you have imported the data. You should avoid any human key in entries since there may be more records than a human can handle.
In this case, we assume that a combination of the first name and last name will be unique.
(2) Compose sales records for the month of December, 2020
Align sales data into one Excel worksheet with the following columns (you may also have other columns created): Customer’s First Name, Customer’s Last Name, Street Address, City, State, Zip Code, Gender, Telephone Number, Sales Associates, and Sales Amount. There may be multiple records/rows per customer and multiple Sales Associates per customer.
In order to make sure that you know how to use certain Excel functions, there are restrictions on what you can use.
a. You are allowed to import the text data using Get External Data feature in Excel, but you are limited to use ONLY the “tab”, not the “comma”, delimiters.
b. “Text to Columns” function is not allowed in this project.
c. You need to demonstrate your ability to use the following functions in Excel: LEFT, RIGHT, MID, SEARCH, LEN, IF, CONCATENATE, and VLOOKUP. You can use other functions if you want.
d. Do NOT leave any NULL cell. That means, you must have complete information for each record, such as address, telephone number, etc.
(3) Compose a Master Customer List
a. List all customers alphabetically, regardless of their sales associates, with their First Name, Last Name, Street Address, City, State, Zip Code, Gender, and Telephone Number. Do NOT list a customer more than once.
b. You need to demonstrate your ability to use the following functions in Excel: LEFT, RIGHT, MID, SEARCH, LEN, IF, CONCATENATE, and VLOOKUP. You can use other functions if you want.
Project Requirements:
Submit the following to me via Blackboard. You have to submit twice; one for the business memo and another the Excel file. Please use your full name and assignment name as the file name, e.g., Wang-TJ-DataAnalytics-Memo.docx, Wang-TJ-DataAnalytics-Excel.xlsx.
1. (100 points) A formal business memo detailing procedures that you take to gather sales records and master customer list. Please follow the Scoring Rubric: Business Letter/Memo (i.e., Memo Writing Rubric), and