Please help with microsoft excel.

companys products includes foods, beverages, personal care products and cleaning agents. You were asked to develop a

framework in Excel to calculate the final total cost of products purchased based on different delivery method, discount

levels and price guide of products purchased by distributors.

Consider and study the three tables below carefully, the first table is the PRICING TABLE, the second table is

the DELIVERY TABLE and the third table is the SALES TABLE.

The Pricing Table is placed in Columns A and B respectively while the Delivery Table is placed in Columns D and E of

the Excel sheet. The Sales Table has 7 columns in Columns A, B, C, D, E, F and G respectively.

Exercise 1

What will be the Total Cost of products purchased for each delivery method, having been given the number of Units

Sold in column B of the Sales Table? Using the LOOKUP Function tool in Excel 2013/365, write a formula in the TOTAL

COST column (C12:C21) on the Sales Table to determine the Total Cost of products purchased in Rand.

Column A lists the delivery method. Using the LOOKUP Function, write another formula to determine the Unit

Delivery values.

Discussion 2

After working through exercise 2, reply to this Discussion with your answer (Display the formula you used and Unit

Delivery values in Rand). Reply to the answer provided by three other students who have one or more different answers to

yours. Continue your discussions until all differences are resolved.

Exercise 3

Work out the Total Delivery cost of products purchased.

Discussion 3

After working through exercise 3, reply to this Discussion with your answer (Display the formula you used and Total

Delivery values in Rand). Reply to the answer provided by three other students who have one or more different answers to

yours. Continue your discussions until all differences are resolved.

Exercise 4

Your company offers various discounts based on the total cost of products sold. You are required to create a minimum of

three different levels of discount. Create a lookup table for the delivery Discounts in order for you to use a lookup function

to calculate the delivery discount for each order. Use the discount table below:

Write a formula in Delivery Discount column to lookup the correct delivery discount using the Total Delivery lookup table.

You must try the different discount options for each row in the Delivery Discount column and indicate the discount choice

you have used (i.e. either Discount 1, 2 or 3).

Discussion 4

After working through exercise 4, reply to this Discussion with your answer (Display the formula you used and Delivery

discount values in Randto yours. Continue your discussions until all differences are resolved.

Exercise 5

Now, calculate the Final Total

Pricing Table			Delivery Table
No of Units	Price/Unit		Method	R/unit
1	70		ship	1
10	60		Truck	1.5
100	50		Road	2
200	40		Rail	2.5
500	30		Air	3
1000	20

Sales Table
Delivery Method	Units Sold	Total Cost	Unit Delivery	Total Delivery	Delivery Discount 1	Final Total
Ship	2	R 140.00	70	R 70.00	R -	R 140.00
Rail	12	R 720.00	60	R 150.00	R -	R 720.00
Road	150	R 7,500.00	50	R 100.00	R 10.00	R 7,490.00
Air	260	R 10,400.00	40	R 120.00	R 24.00	R 10,376.00
Truck	580	R 17,400.00	30	R 45.00	R 9.00	R 17,391.00
Truck	1010	R 20,200.00	20	R 30.00	R 6.00	R 20,194.00
Ship	679	R 20,370.00	30	R 30.00	R 6.00	R 20,364.00
Road	230	R 9,200.00	40	R 80.00	R 8.00	R 9,192.00
Rail	125	R 6,250.00	50	R 125.00	R 12.50	R 6,237.50
Air	25	R 1,500.00	60	R 180.00	R -	R 1,500.00

Discount Table
Total Cost	0	5000	10000
Discount 1	0%	10%	20%
Discount 2	5%	10%	15%
Discount 3	10%	15%	20%