Question

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

Answer 1

This is very crude form of submitting questions (Screen Shot) which is not helpful in answering the response & specifically it will be very difficult for reader as well. The reader should have provided excel table.

The solution depends on simple excel formula like Vlookup, Referencing cells, Sum, If with nesting formula. It will be difficult to explain this excel formula functionalities and syntax since this is altogether difference subject.

Since the screen shots only available i am trying to explain the things with minimal sample data :

Sample Invoice Header
Invoice Date	Invoice Number	Customer ID
5/8/2005	ORDST1025	BAKERSEM0001
4/12/2007	STDINV2251	BAKERSEM0001
5/27/2004	INV1012	KELLYCON0001

Sample Customer Header
Customer ID	Customer Name	Address	Country	City	State	PIN
BAKERSEM0001	Baker's Emporium Inc	789 University Ave	USA	Fort Waync	IN	46802-3918

Sample Invoice Data
Invoice Number	Quantity	Unit Cost	Unit Price
ORDST1025	5	879.05	1759.95

For Solution we need following fields :

Invoice Date

Invoice Number

Customer ID

Customer Name

Country

Invoice Quantity

Unit Cost

Unit Price

Gross Profit per unit

Gross profit per Invoice

Tax due (Yes / No)

Tax Rate

Net Profit

5/8/2005

ORDST1025

BAKERSEM0001

Baker's Emporium Inc

USA

5

879.1

1759.95

881

4405

Yes

20%

3523.6

3523.6

- The first three fields is need to copied from Invoice header filed

- Customer name and customer country is fetched through "Vlookup" from "Customer Header" data / table considering customer code which is unique field in each table

- Invoice Quantity, Unit cost and Unit price is fetched through "vlookup" from Invoice detail data/table considering "Invoice Number" which is unique filed in each table

- Gross profit per unit is derived --> Unit price - Unit Cost

- Gross Profit Per Invoice is derived --> Gross Profit * Invoice Quantity

- The tax rate is derived through "IF" formula with nest i.e. =IF(J5<0,0,IF(J5<1000,10%,IF(J5<5000,20%,25%)))

I am showing excel formula for the above results :

Invoice Date

Invoice Number

Customer ID

Customer Name

Country

Invoice Quantity

Unit Cost

Unit Price

Gross Profit per unit

Gross profit per Invoice

Gross Profit due (Yes / No)

Tax Rate

Net Profit

=A14

=B14

=C14

=VLOOKUP(C5,A20:G21,2,0)

=VLOOKUP(C5,A20:G20,4,0)

=VLOOKUP(B5,A24:D25,2,0)

=VLOOKUP(B5,A24:D25,3,0)

=VLOOKUP(B5,A24:D25,4,0)

=H5-G5

=I5*F5

=IF(J5>0,"Yes", "No")

=IF(J5<0,0,IF(J5<1000,10%,IF(J5<5000,20%,25%)))

=J5*(1-L5)

=SUM(M5)

Where :

- The current row in excel worksheet is 5, hence Customer name refers to C5 which is actual refering to Customer ID

- Cell A14-C14 refers to Sample Invoice Header table

- Cell A20:G20 refer to Customer Header data table

- Cell A24:D25 refers to Invoice detail table