Question

from the table "X" (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

Table "X" please write the formula for excel

InvoiceDate	InvoiceNumber	CustomerID	Customer name	Country	Invoice Quantity	Unit Cost	Unit price
5/8/05	ORDST1025	BAKERSEM0001	Baker's Emporium Inc.	USA	4	0	9.95
4/12/07	STDINV2251	BAKERSEM0001	Baker's Emporium Inc.	USA	4	75	9.95
5/8/05	ORDST1026	AARONFIT0001	Aaron Fitz Electrical	USA	3	879.05	1759.95
4/12/07	STDINV2252	AARONFIT0001	Aaron Fitz Electrical	USA	3	899	1759.95
5/7/04	ORD1002	METROPOL0001	Metropolitan Fiber Systems	USA	1	3.29	9.95
2/10/04	INV1024	AARONFIT0001	Aaron Fitz Electrical	USA	1	59.29	119.95
2/15/04	INV1025	AARONFIT0001	Aaron Fitz Electrical	USA	1	50.25	109.95

Answer 1

Invoice Quantity Net profit/(ass) (d) 36 4 InvoiceDate Invoice Number Customers 5/9/2005 ORDST1025 BAKERS[MODO1 4/12/2007/STDINV2251 BAKERSEMOD01 5/8/2005 ORDST1026 4/12/2007 STOINV2252 ARON FIT0001 5/7/2004 ORD 1002 METROPOLOOD1 2/10/2004 INV1024 AARONFIT0001 2/15/2004 INV1025 AARON FIT0001 Icustomer name Baker's Emporium Inc Baker's emporium in Iharon Fitz Electrical Paron Fitz Flectrical Metropolitan Fiber Systems Aaron Fitz Electrical Aaron Fitz Electrical Country USA JUSA JUSA USA USA TUSA USA 1 Gross Tax on Unit Cost unit price Total Sale Total Cost Iprofit(a) (Yes/No) (Tax Rate Profits 9.9540 - 40 Yes 101% 3.99 25.00 9.95 40300 1260) NO 879,05 1.759.95 5.200 2.637 2,643 Yes 2012 529 31 899.00 1,759.95 5.280 2,697 2,582 Yes 209517 il 3.299 .95 10 31 7 Yes 10% 1 11 59.29 119.95 120 59 6 1 Yes 104 50.25 109.95 110 50 60 Yes 10 6 Total Profits le) (260) 2.114 2.066 1 4,07D Tax Slab Chart 1000 1001 50001 5000 1002100 tulars Formula Table Formula IFK]>0,"Yes","No") IF(L3-"yes",IFIK3 $C$12,$D$12, IFK32-50$13,$0$13,1 FIK3C-$C$14.$D$14.0110.0) Tax Yes/No) Tax Rate