80.2K

Verified Solution

Link Copied!

Please i need this ASAP step by step process of how to do it, Thank you

I need to know how to do this in excel, a step by step process thank you very much

Sheet 1: Rename a New Sheet with the name Products. o Create a Table with 6 columns and 20 Products. The names for this columns are: Product Number Product Name Description Retail Price Qty Stock Store = Sheet 2. Rename a New Sheet with the name Invoice. You must create an Invoice format as presented in the image below. o In this Invoice you must bring the information of the Products Sheet using Data Validation in the Product Name and complete the rest of the information in the Product # Column and in the Unit Price column. Using VLookUp in almost 10 products (Product Name, Code, Price) o Also, include a Quantity (Qty) column and a Total column to calculate the total (Unit Price x Qty). o Include in a cell the Invoice subtotal, a cell to calculate the IVU of 11.5% and a cell of the Total (Sum of Subtotal and IVU calculated) => You must create a button related to a Macro to clean the fields. Considerate that this action will not eliminate the Data Validation and the formulas related. D E G M 1 2 Nombre de su compaa PO Box 191618, San Juan, PR 00919 Tel. 787-685-7600 3 Invoice XXXX 4 Misc Nome Universidad Politecnica de Puerto Rico Addre Date Order No. Rep FOB 5 6 7 8 $ 10 11 12 13 14 15 City Phone State Fax ZIP Qty Products 2254 2236 Product Name Producto 1 Producto 2 Unit Price $720.00 $720.00 TOTAL $720.00 $720.00 1 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Sub Total 1440.00 Por Other Tax Rate() IVU 15 1 Total 1 1440.00 34 35 36 3 38 33 40 Clear Fecha 43 Sheet 3: In a Sheet named Data you must create 4 Pivot Table with the appropriate Chart for each one. These are: a. Sum of the Sales Units per Representant and Region b. Sum of Sales Unit per Month and Region c. Sum of Total Sales per Month and Representant d. Sum of Total Sales per Representant and Region 47 B D E F G H 1 Date Product Region SalesRep Customer Units Sales COGS 2 1/23/13 Quad West Sioux DFR 23 $621.00 $333.50 3 1/18/13 Quad West Pham TTT 65 $1,235.00 $520.00 4 6/19/13 Quad West Pham FM $987.00 $434.75 5 6/13/13 Quad MidWest Smith PLOT 24 $648.00 $348.00 6 4/6/13 Quad South Pham JAQ 58 $1,102.00 $464.00 7 3/2/13 Quad West Franks ET 22 $594.00 $319.00 8 3/24/13 Quad East Smith JAQ 8 $216.00 $116.00 9 2/27/13 Quad South Smith ET 29 $551.00 $232.00 10 6/19/13 Quad West Chin ZAT 55 $1,265.00 $605.00 11 5/17/13 Quad MidWest Franks ZAT $897.00 $429.00 12 4/11/13 Quad MidWest Pham DFGH 49 $931.00 $392.00 13 2/15/13 Quad MidWest Pham KBTB 63 $1,449.00 $693.00 14 2/20/13 Quad South Smith ITTW 19 $513.00 $275.50 15 1/9/13 Quad MidWest Chin LOP 40 $1,080.00 $580.00 16 4/4/13 Quad East Smith YTR 60 $1,620.00 $870.00 17 4/17/13 Quad South Chin WT 24 $504.00 $222.00 18 1/22/13 Bellen West Pham BBT 10 $220.00 $100.00 19 2/9/13 Sunshine East Smith GRR 26 $494.00 $208.00 20 5/15/13 Sunshine West Pham WEMI 65 $1,235.00 $520.00 39 A Annan CAnn 1 Product Price List 2 Product Number Product Name Description Retail Price Qty Stock Store 4 5 6 8 2 3 4 5 6 9 7 8 9 10 11 12 13 14 15 16 17 18 19 20 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Products Invoice Data + Sheet 1: Rename a New Sheet with the name Products. o Create a Table with 6 columns and 20 Products. The names for this columns are: Product Number Product Name Description Retail Price Qty Stock Store = Sheet 2. Rename a New Sheet with the name Invoice. You must create an Invoice format as presented in the image below. o In this Invoice you must bring the information of the Products Sheet using Data Validation in the Product Name and complete the rest of the information in the Product # Column and in the Unit Price column. Using VLookUp in almost 10 products (Product Name, Code, Price) o Also, include a Quantity (Qty) column and a Total column to calculate the total (Unit Price x Qty). o Include in a cell the Invoice subtotal, a cell to calculate the IVU of 11.5% and a cell of the Total (Sum of Subtotal and IVU calculated) => You must create a button related to a Macro to clean the fields. Considerate that this action will not eliminate the Data Validation and the formulas related. D E G M 1 2 Nombre de su compaa PO Box 191618, San Juan, PR 00919 Tel. 787-685-7600 3 Invoice XXXX 4 Misc Nome Universidad Politecnica de Puerto Rico Addre Date Order No. Rep FOB 5 6 7 8 $ 10 11 12 13 14 15 City Phone State Fax ZIP Qty Products 2254 2236 Product Name Producto 1 Producto 2 Unit Price $720.00 $720.00 TOTAL $720.00 $720.00 1 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Sub Total 1440.00 Por Other Tax Rate() IVU 15 1 Total 1 1440.00 34 35 36 3 38 33 40 Clear Fecha 43 Sheet 3: In a Sheet named Data you must create 4 Pivot Table with the appropriate Chart for each one. These are: a. Sum of the Sales Units per Representant and Region b. Sum of Sales Unit per Month and Region c. Sum of Total Sales per Month and Representant d. Sum of Total Sales per Representant and Region 47 B D E F G H 1 Date Product Region SalesRep Customer Units Sales COGS 2 1/23/13 Quad West Sioux DFR 23 $621.00 $333.50 3 1/18/13 Quad West Pham TTT 65 $1,235.00 $520.00 4 6/19/13 Quad West Pham FM $987.00 $434.75 5 6/13/13 Quad MidWest Smith PLOT 24 $648.00 $348.00 6 4/6/13 Quad South Pham JAQ 58 $1,102.00 $464.00 7 3/2/13 Quad West Franks ET 22 $594.00 $319.00 8 3/24/13 Quad East Smith JAQ 8 $216.00 $116.00 9 2/27/13 Quad South Smith ET 29 $551.00 $232.00 10 6/19/13 Quad West Chin ZAT 55 $1,265.00 $605.00 11 5/17/13 Quad MidWest Franks ZAT $897.00 $429.00 12 4/11/13 Quad MidWest Pham DFGH 49 $931.00 $392.00 13 2/15/13 Quad MidWest Pham KBTB 63 $1,449.00 $693.00 14 2/20/13 Quad South Smith ITTW 19 $513.00 $275.50 15 1/9/13 Quad MidWest Chin LOP 40 $1,080.00 $580.00 16 4/4/13 Quad East Smith YTR 60 $1,620.00 $870.00 17 4/17/13 Quad South Chin WT 24 $504.00 $222.00 18 1/22/13 Bellen West Pham BBT 10 $220.00 $100.00 19 2/9/13 Sunshine East Smith GRR 26 $494.00 $208.00 20 5/15/13 Sunshine West Pham WEMI 65 $1,235.00 $520.00 39 A Annan CAnn 1 Product Price List 2 Product Number Product Name Description Retail Price Qty Stock Store 4 5 6 8 2 3 4 5 6 9 7 8 9 10 11 12 13 14 15 16 17 18 19 20 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Products Invoice Data +

Answer & Explanation Solved by verified expert

See Answer