Тема: Як зробити: облік продажу обладнання оптовим клієнтам
Доброго вечора! Підкажіть будь ласка може хтось допомогти перетворити таблиці, просто у мене немає повноцінного Excel.ASSIGNMENT: ANALYSIS OF ECONOMIC INFORMATION IN SPREADSHEET SOFTWARE
You are a sales manager of a computer hardware shop. Your obligation is to maintain records about hardware sales to wholesale customers.
The manager asked you to recover the lost data about hard disk drive sales in old records from 2008 that were collected in the file “HDDSALES_2008.xlsx”. Some columns in the table are missing due to a software error, but you have additional data about customers, products, and their prices (sheets “PRODUCTS” and “CUSTOMERS”).
1. Complete the table on sheet “SALES” using the following guidelines:
Convert all the data ranges to 3 Excel tables: “SalesTable”, “ProductsTable”, “CustomersTable”.
Sort sales by date and sort other tables by IDs.
Using the product code value, fill the “Product Name” column in the “SalesTable” with data from the “ProductsTable”.
Tip: Use the XLOOKUP function or INDEX+MATCH combination
Using logical and text functions, check the HDD connector type in the product
name and fill the column “Connector type” with the relevant abbreviation (“SATA” or
“SAS”).
Tip: Use SEARCH and logical operations
Get HDD capacity (in Gigabytes) from product names. Fill the column “Volume”
with this data in numeric form.
Tip: Use text functions and a function that converts text to a number.
Populate the column “Price per unit (USD)” with the price in dollars (USD) obtained by product code from the “ProductsTable”.
Calculate the total price per transaction.
Using customer codes and available data, populate the column “Customer full
name” in “SalesTable”. The full name must be prepared in the source table using a space
as a separator.
Tip: there are at least 3 easy methods to join text values in Excel
Generate the transaction ID in the last column by the following pattern:
A/B-C-DE-F/G
where
A- 4 digits of day and month, e.g., 1301 or 1512
B- 2 digits of year, e.g., 08
C- productcode
D- connector type
E- disk capacity in GB
F- quantity
G- customer code
Example: 0505/08-42D0378-SAS146-2/W003
Tip: You can use functions from the Date & Time category to get the A and B
2. Create the following reports using pivot tables and charts. Format the charts as you wish, but with usability in mind.
Variant 1
Sum of sales, grouped by products, in units. With filtering by customer.
Sum of sales in USD (grouped by customers)