Education assignment for Aj

profileJordanjameire
Acct2400ExcelProjectFall2023-1.xlsx
Home>Education homework help>Education assignment for Aj

Instructions

ACCT 2400 - Fall 2023
Excel Project:
Problem Statement: You are a financial analyst at XYZ Company, and you have been tasked with analyzing the company's sales data for the year 2023. You have been provided with two Excel spreadsheets: one containing sales data and another containing customer information. Your goal is to create a summary report using Pivot Tables and VLOOKUP to answer specific questions.
Sales Data (Sheet Name: SalesData):
Column A: Date
Column B: Invoice Number
Column C: Product ID
Column D: Customer ID
Column E: Quantity Sold Column F: Unit Price
Customer Information (Sheet Name: CustomerInfo):
Column A: Customer ID
Column B: Customer Name
Tasks:
1. Use a VLOOKUP formula to add a "Customer Name" column to the Sales Data sheet based on the "Customer ID" from the CustomerInfo sheet. Place this new column as the last column in the Sales Data sheet.
2. Create a Pivot Table on a new sheet named "SalesSummary" to answer the following questions:
a. What is the total sales revenue for each product in 2023?
b. What is the total quantity sold for each product in 2023?
c. What is the total sales revenue for each customer in 2023?
d. What is the total quantity sold to each customer in 2023?
3. Format the Pivot Table to make the data more readable and presentable. Include relevant headers, and use number formatting to display currency and quantity appropriately. and use number formatting to display currency and quantity appropriately.
4. Create a Pivot Chart on the "SalesSummary" sheet to visualize the total sales revenue for each product in 2023.
5. Add a slicer to the Pivot Chart to allow the user to filter the chart by product ID (Stretch).
6. Provide a written analysis of your findings based on the Pivot Table and Pivot Chart. What insights can you derive from the data?
Submission Instructions:
Submit your Excel file containing the SalesData and CustomerInfo sheets with the VLOOKUP and Pivot Table/Chart on separate sheets. Include your written analysis in a separate Word or PDF document.

Sales Data

Sales Data
Date Invoice # Product ID Customer ID Quantity Unit Price
1/1/23 1001 P101 C001 10 25
1/2/23 1002 P102 C002 8 32.5
1/3/23 1003 P103 C003 15 20
1/4/23 1004 P101 C004 12 24.5
1/5/23 1005 P104 C005 20 18.75
1/6/23 1006 P102 C006 6 31.25
1/7/23 1007 P105 C007 18 16
1/8/23 1008 P103 C008 10 21.5
1/9/23 1009 P104 C009 22 17.25
1/10/23 1010 P101 C010 14 23.75
1/11/23 1011 P102 C001 9 33
1/12/23 1012 P103 C002 13 19.5
1/13/23 1013 P104 C003 19 19.75
1/14/23 1014 P105 C004 25 15.5
1/15/23 1015 P101 C005 16 25.25
1/16/23 1016 P102 C006 7 32.75
1/17/23 1017 P103 C007 11 20.75
1/18/23 1018 P104 C008 21 18.25
1/19/23 1019 P105 C009 27 14.75
1/20/23 1020 P101 C010 18 24
1/21/23 1021 P102 C001 10 31
1/22/23 1022 P103 C002 14 19.25
1/23/23 1023 P104 C003 23 17
1/24/23 1024 P105 C004 29 14
1/25/23 1025 P101 C005 20 23.5
1/26/23 1026 P102 C006 11 30.25
1/27/23 1027 P103 C007 16 18
1/28/23 1028 P104 C008 24 16.75
1/29/23 1029 P105 C009 30 13.5
1/30/23 1030 P101 C010 22 22.75
1/31/23 1031 P102 C001 12 29.5
2/1/23 1032 P103 C002 17 17.25
2/2/23 1033 P104 C003 25 15
2/3/23 1034 P105 C004 31 12.75
2/4/23 1035 P101 C005 24 22
2/5/23 1036 P102 C006 13 28.75
2/6/23 1037 P103 C007 18 16.5
2/7/23 1038 P104 C008 26 14.25
2/8/23 1039 P105 C009 32 12.25
2/9/23 1040 P101 C010 26 21.25
2/10/23 1041 P102 C001 14 27.5
2/11/23 1042 P103 C002 19 15.75
2/12/23 1043 P104 C003 27 13.5
2/13/23 1044 P105 C004 33 11.75
2/14/23 1045 P101 C005 28 20.5
2/15/23 1046 P102 C006 15 26.75
2/16/23 1047 P103 C007 20 15
2/17/23 1048 P104 C008 28 12.5
2/18/23 1049 P105 C009 34 11
2/19/23 1050 P101 C010 30 19.75
2/20/23 1051 P102 C001 16 25
2/21/23 1052 P103 C002 21 14.25
2/22/23 1053 P104 C003 29 12
2/23/23 1054 P105 C004 35 10.5
2/24/23 1055 P101 C005 32 19.25
2/25/23 1056 P102 C006 17 24.25
2/26/23 1057 P103 C007 22 13.75
2/27/23 1058 P104 C008 30 11.25
2/28/23 1059 P105 C009 36 10
3/1/23 1060 P101 C010 34 18.75
3/2/23 1061 P102 C001 18 23.5
3/3/23 1062 P103 C002 23 13.25
3/4/23 1063 P104 C003 31 10.75
3/5/23 1064 P105 C004 37 9.5
3/6/23 1065 P101 C005 36 17.5
3/7/23 1066 P102 C006 19 22.25
3/8/23 1067 P103 C007 24 12.5
3/9/23 1068 P104 C008 32 10.25
3/10/23 1069 P105 C009 38 9.25
3/11/23 1070 P101 C010 38 16.75
3/12/23 1071 P102 C001 20 21
3/13/23 1072 P103 C002 25 11.75
3/14/23 1073 P104 C003 33 9
3/15/23 1074 P105 C004 39 8.5
3/16/23 1075 P101 C005 40 15.75
3/17/23 1076 P102 C006 21 20.75
3/18/23 1077 P103 C007 26 10.25
3/19/23 1078 P104 C008 34 8.75
3/20/23 1079 P105 C009 40 8
3/21/23 1080 P101 C010 42 15.25
3/22/23 1081 P102 C001 22 20
3/23/23 1082 P103 C002 27 9.75
3/24/23 1083 P104 C003 35 8.5
3/25/23 1084 P105 C004 41 7.75
3/26/23 1085 P101 C005 44 14.75
3/27/23 1086 P102 C006 23 19.25
3/28/23 1087 P103 C007 28 9.25
3/29/23 1088 P104 C008 36 8.25
3/30/23 1089 P105 C009 42 7.5

Customer Data

Customer Information
Customer ID Customer Name
C001 John James
C002 Jane Smith
C003 Bob Johnson
C004 Alice Brown
C005 David Lee
C006 Emily Davis
C007 Susan Wilson
C008 Michael Jones
C009 Sarah Clark
C010 Robert White

Resources

You Tube Information about Vlookups and Pivot Tables
Information on Vlookup and Pivot Tables
https://www.youtube.com/watch?v=yhNKeIxIKps
https://www.youtube.com/watch?v=UsdedFoTA68