Excel file Homework1 - invoices.xlsx contains data about invoices of a reseller of office equipment that ships its products to customers in several countries. The tables in the workbook refer to data collected from different systems within the company.

a) Copy worksheet InvoiceHeader into a new worksheet. For each invoice in the new worksheet, show the matching information from the other tables: Invoice date, Invoice Number, Customer ID, Customer Name, Country, Invoice Quantity, Unit Cost, Unit Price.

b) In the worksheet in (a), calculate gross profit of each invoice as total sale minus total cost of all units of the invoice.

c) Calculate tax on profits for each invoice. Create two columns. One column must show if tax is due for the order (YES or NO). Tax is due if the gross profit of the order is greater than $0. The other column must show the tax rate. The rate is 0% for negative gross profit. For positive gross profit, the tax rate depends on profit according to the following table:

Gross Profit	Tax rate
$0 to $1,000	10%
$1,000.01 to $5,000	20%
$5,000.01 or more	25%

(Tip: you may need to specify a maximum profit in the table. You can use $100,000.)

d) Calculate Net profit as Gross profit * ( 1 tax rate ) of each order.

e) Calculate Total net profit as the sum of Net profit of all orders.

Excel functions you may want to use: Vlookup, IF

how can I add an excel file, i am allowed just for pictures ?

can you do it for just two customer ID

Customers Invoice Header Invoice Details Ch State Country USA USA Customer AARONFITO001 ADAMPARKO ADVANCED ADVANCED002 ALTONMANO AMERICANO001 AMERICAN0002 ASSOCIATODOL ASTORS Customer Name Aaron Fitz Electrical Adam Park Resort Advanced Paper Co A dvanced Tech Satellite System Alan Manufacturing American Science Museum Anrican Electrical Contractor Associated inwarance Company AN Address One Micros Way Suits 9876 456h Street 8765 66 Ave. PO, 334 789 North Carlton Place 3456 North Cat Av 12 Garden Mall 97 Wes Alaska Ave USA Canada USA USA Redmond Indianapolis Chicago Toronto Detroit St. Louis lowa City Lincoln 98052-6399 46206-1391 6060191 MAD TRS 42311141 63115 52240-1827 68502.3000 46401-1455 USA USA USA Invoice Date InvoiceNumber 5/8/05 ORDST1025 4/12/07 STDINV2251 5/8/05 ORDST1026 4/12/07 STDINV2252 5/7/04 ORD1002 2/10/04 INV1024 2/15/04 INV1025 5/10/04 ORDPH1005 5/8/04 ORD1000 5/10/04 ORD1001 5/10/04 ORDST1008 5/10/04 ORD1007 CustomerID BAKERSEM0001 BAKERSEM0001 AARONFIT0001 AARONFIT0001 METROPOL0001 AARONFIT0001 AARONFIT0001 LECLERC0001 MAGNIFIC0001 HOLLINGC0001 MAHLERST0001 ASTORSUI0001 InvoiceNumber BKO1001 BK01003 INV1010 INV1011 INV1012 INV1013 INV1014 INV1015 INV1016 INV1017 INV1018 Quantity UnitCost Unit Price 34550 69109.95 10 41.98 89.95 674.5 1349.95 91.25 189.95 303.85 609.95 93.55 189.95 3.29 9.95 1 179.85 359.95 3.29 9.95 1 2998.15 5999.95 0.16 0.35 Customers Invoice Header Invoice Details USA USA Canada Adam Park Resort Advanced Paper Co. Advanced Tech Satellite System Alton Manufacturing American Science Museum American Electrical Contractor Associated Insurance Company USA ADAMPARKO001 ADVANCED001 ADVANCED0002 ALTONMANO001 AMERICANO001 AMERICANO002 ASSOCIATO001 ASTORSUTOOO! ATMOREREDO BAKERSEM0001 Indianapolis Chicago Toronto Detroit St. Louis Iowa City Lincoln USA MO 46206.1991 60603-911 MSD TRS 48233-3343 63113 $2240-1827 68502. 1090 46401-3455 61701.9192 46102.1918 54935.9990 67201 USA USA Gary IN USA USA USA USA Australia Canada Canada USA BLUEYONDX0001 BOYLESCO0001 BREAKTHROOI CASTLEIN0001 Bloomington Fort Wayne Fond Du Lac Wichita Melbourne Montreal Ottawa VIC VO MN USA USA WI USA USA USA Canada Canada USA Suite 976 456 19th Street S. 765 66 Ave PO Box 3343 789 North Carlton Place 3456 North Cal Ave 121 Garden Mall 957 West Alaska Ave 567 W 7th St. 789 University Ave Suite 123 PO Box 1234 1214 Burke 3456 Arbutus St. 234 103 Ave. 5678 Horseshoe Way 8765 51 Aves 2145 Aukland St West Suite 13 765 Wout24h Street 234 Hickman St Place 121. Suite 3 765 Curtis 21456 Mendan SE NE 456 17 SES 9876 Bernard St 123 Churchill Rd 21456 2 Ave. 67% Falcon 456 Mineral Point Road 121 Albert Ave 321 Park Drive 9576 112 St. 1234 SE Marine 8765 95 W 765 Kingway 987 Chestnut Avenue 123 Alfred St 145 Industrial Drive 456 Sherwood Park South Atmore Retirement Center Baker's Emporium Inc Berry Medical Center Blue Yonder Airlines Boyle's Country Inn's Breakthrough Telemarketing Castle Inn Resort Cellular Express Center Suite Hotel Central Communications LTD Central Distributing Central Illinois Hospital Cobo Wintery Communication Connections Communication Connections Compu-Tech Solutions Computerized Phone Systems Computers Unlimited Computer Equipment Leasing Comic Pope Continental Properties Contosa, Country View Estates Crawfords, Ins Data Communications Inc. Dial Direct Paging Inc. Direct Marketers Dellis Cove Resort Downtown Hotel Executive Resources Fourth Coffee Franchise Onice Machines H3R 3T2 KATXW2 60507.3322 55401-76894 60601-2333 54601-9975 61601-4542 62703 RIM 2G6 V6 319 54305-5303 53701-6:52 TSJ 25 2000 50010-3976 MB CENTERSU0001 CENTRALCOOL CENTRAL D0001 CENTRAL10001 COHWINE0001 COMMUNIC0001 COMMUNIC0002 COMPUTEC0001 COMPUTER0001 COMPUTER0002 COMPUTER0003 COMTELPA0001 CONTINEN001 CONTOSOL0001 COUNTRY VOI CRAWFORD0001 DATACOMMODO DLALDIRF0001 DIRECTMA0001 DOLLISC00001 DOWNTOWN001 EXECUTIVOODI FOURTHICO0001 FRANCHISO001 USA NSW Canada Australia USA Australia USA New Zealand USA Canada Minneapolis Chicago La Crosse Peoria Springfield Winnipeg Vancouver Green Bay Madison Edmonton Sydney Ames Sydney Omaha Palmerston North Columbia Edmonton St. Louis St. Paul Charlottetown Chicago Sydney Beatrice Arlington Heights NE 68145-2401 USA 65201-322 TSP ITI 63155.2343 55101011 CIA IW3 60618-2991 MN USA Canada Australia NSW 2000 USA 68390.1010 6004-2922 USA Customers Invoice Header Invoice Invoice Date InvoiceNumber 5/8/05 ORDST1025 4/12/07 STDINV2251 5/8/05 ORDST1026 4/12/07 STDINV2252 5/7/04 ORD1002 2/10/04 INV1024 2/15/04 INV1025 5/10/04 ORDPH1005 5/8/04 ORD1000 5/10/04 ORD1001 5/10/04 ORDST1008 5/10/04 ORD1007 5/4/04 ORDPH1001 5/10/04 ORDST1016 5/10/04 ORDPH1006 5/10/04 ORDPH1007 4/12/00 STDINV2262000 4/12/00 STDINV2265000 4/12/00 STDINV2264000 4/12/00 STDINV2263000 4/12/05 STDINV2263005 4/12/05 STDINV2264005 4/12/05 STDINV2265005 4/12/03 STDINV2264003 4/12/03 STDINV2262003 4/12/05 STDINV2262005 4/12/02 STDINV2264001 4/12/02 STDINV2265001 4/12/03 STDINV2265002 4/12/01 STDINV2265 4/12/02 STDINV2262001 4/12/02 STDINV2263001 4/12/01 STDINV2262 4/12/01 STDINV2263 4/12/01 STDINV2264 4/12/07 STDINV2254 5/8/07 ORDST2223 4/12/07 ORDST2230 COME TAIAA CustomerID BAKERSEM0001 BAKERSEM0001 AARONFIT0001 AARONFIT0001 METROPOL0001 AARONFIT0001 AARONFIT0001 LECLERC0001 MAGNIFIC0001 HOLLINGC0001 MAHLERST0001 ASTORSU10001 CONTOSOL0001 BAKERSEM0001 BOYLESCO0001 CONTOSOL0001 ADVANCED0002 COUNTRY V0001 ADVANCED0001 CENTRAL D0001 CENTRAL D0001 ADVANCED0001 COUNTRY V0001 ADVANCED0001 ADVANCED0002 ADVANCED0002 ADVANCED0001 COUNTRYV0001 COUNTRYV0001 COUNTRYV0001 ADVANCED0002 CENTRALD0001 ADVANCED0002 CENTRALD0001 ADVANCED0001 BAKERSEM0001 AMERICAN0001 EDITANAMAI Customers Invoice Header Invoice! 4/12/07 STDINV2253 5/10/04 INVPS1006 5/8/05 ORDST1024 4/12/07 STDINV2250 5/19/04 ORDSPEC1001 4/12/07 INVSP1004 5/10/04 INV1020 4/12/07 INVSP1003 5/10/04 BK01001 4/12/07 STDINV2249 5/18/04 ORD1004 4/12/07 STDINV2248 5/26/04 ORDSPEC1000 4/12/07 STDINV2245 4/12/07 STDINV2246 4/12/07 STDINV2247 4/12/07 STDINV2240 5/29/04 ORD1005 5/19/04 ORDST1013 4/12/07 INVPS1007 4/12/07 STDINV2237 4/12/07 STDINV2241 5/27/04 ORDST1012 5/19/04 ORDST1014 5/28/04 ORDST1015 5/24/04 ORDRP1000 4/28/04 ORDST1002 5/20/04 ORDST1011 5/17/04 ORDPH1002 5/16/04 ORDPH1003 5/22/04 ORDPH1004 5/18/04 INVSP1001 5/7/04 ORD1003 5/16/04 ORD1006 5/27/04 INV1013 5/20/04 INVSP1000 5/22/04 INVSP1002 5/10/04 INVPS1005 5/19/04 INV1011 5/27/04 INV1012 AMERICAN0001 CONTOSOL0001 ADAMPARK0001 ADAMPARK0001 COUNTRY V0001 COUNTRYV0001 ASTORSU10001 COMPUTER0001 CONTOSOL0001 CONTOSOL0001 VISIONIN0001 VISIONIN0001 COMPUTER0001 METROPOL0001 COMMUNIC0002 COMMUNIC0001 ASSOCIAT0001 COMMUNIC0002 COMMUNIC0001 WESTCENT0001 OFFICEDE0001 LAWRENCE0001 WESTSIDE0001 LASERMES0001 METROPOL0001 ATMORERE0001 ASSOCIAT0001 LAWRENCE0001 WESTCENT0001 CENTRALI0001 MIDLANDC0001 LONDONBE0001 OFFICEDE0001 KENSINGT0001 ADVANCED0002 PLAZAONE0001 MIDLANDC0001 BOYLESCO0001 MENDOTAU0001 KELLYCON0001 Customers Invoice Header Invoicel InvoiceNumber BKO1001 BKO1003 INV1010 INV1011 INV1012 INV1013 INV1014 INV1015 INV1016 INV1017 INV1018 INV1019 INV1020 INV1022 INV1023 INV1024 INV1025 INVPS1001 INVPS1002 INVPS1003 INVPS1004 INVPS1004 INVPS1005 INVPS1006 INVPS1007 INVSP1000 INVSP1001 INVSP1002 INVSP1003 INVSP1004 ORD1000 ORD1001 ORD1002 ORD1003 ORD1004 ORD1005 ORD1005 ORD1006 ORD1007 ORD1008 Quantity UnitCost Unit Price 1 34550 69109.95 10 41.98 89.95 1 674.5 1349.95 1 91.25 189.95 1 303.85 609.95 5 93.55 189.95 3 3 .29 9.95 1 179.85 359.95 1 3 .29 9.95 1 2998.15 5999.95 0.16 0.35 674.5 1349.95 4.55 9.95 93.55 189.95 10 1197 2399.95 59.29 119.95 50.25 109.95 92.59 189.95 4.55 9.95 27.98 59.95 93.55 189.95 395.1 854.5 303.85 609.95 50.25 109.95 9.81 19.95 93.55 189.95 50.25 109.95 5.98 19.95 59.29 119.95 18.65 39.95 179.85 359.95 27.98 59.95 3.29 9.95 35000 70009.95 34550 69109.95 303.85 609.95 3799 .95 1 3290.55 6589.95 34.55 9.95 50.25 109.95 Customers InvoiceHeader Invoicel ORDPH1007 ORDRP1000 ORDSPEC1000 ORDSPEC1001 ORDST1002 ORDST1002 ORDST1008 ORDST1009 ORDST1010 ORDST1011 ORDST1012 ORDST1013 ORDST1013 ORDST1014 ORDST1014 ORDSTI015 ORDST1016 ORDST1017 ORDST1018 ORDST1018 ORDST1019 ORDST1019 ORDST1020 ORDST1021 ORDST1022 ORDST1023 ORDST1023 ORDST1024 ORDST1024 ORDST1025 ORDST1025 ORDST1025 ORDST1026 ORDST1026 ORDST1026 ORDST2000 ORDST2001 ORDST2002 ORDST2003 ORDST2004 ORDST2005 ORDST2006 50.25 18.65 59.29 18.65 303.85 18.65 2998.15 0.16 674.5 115.85 89 50.25 94.55 91.59 1 149.95 1 3290.55 10 35 91.59 59.29 4 4.55 2 38.59 1 41.98 59.29 59.29 59.29 5.98 41.98 4.55 41.98 SO 109.95 39.95 119.95 39.95 609.95 39.95 5999.95 0.35 1349.95 239.95 179.95 109.95 9.95 189.95 295.65 6589.95 34.95 189.95 119.95 9.95 79.95 89.95 119.95 119.95 119.95 19.95 89.95 9.95 89.95 9.95 1759.95 19.95 1759.95 9.95 79.95 609.95 189.95 189.95 119.95 189.95 959.95 70 QS 5 6 6 4 879.05 5.98 879.05 4.55 38.59 303.85 93.55 92.59 27.98 92.59 479.05 25.80 1