Question

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

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

added

Answer 1

Answer:-

Invoice Tax rate Net profit Qty Invoice number ORDST1025 STDINV2251 ORDST1026 STDINV2252 ORD1002 + mm Unit cost Total cost Unit pricel Total price 9.95 39.80 75 300 9.95 39.80 879.05 2637.15 1759.95 5279.85 899 2697 1759.95 5279.85 3.29 3.29 9.95 9.95 Total Net profit Gross If tax is profit due 39.80 YES -260.20 NO 2642.70 YES 2582.85 YES 6.66 YES 10% 0% 20% 20% 10% 35.82 0.00 2114.16 2066.28 5.99 4222.25