You have newly been employed by a prominent multinational Fast-Moving Consumer Good (FMCG) company. The 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.

You have newly been employed by a prominent multinational Fast-Moving Consumer Good (FMCG) company. The 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.

Exercise 2

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

Exercise 3

Work out the Total Delivery cost of products purchased.

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).

Exercise 5

Now, calculate the Final Total

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.

Exercise 2

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

Exercise 3

Work out the Total Delivery cost of products purchased.

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).

Exercise 5

Now, calculate the Final Total

Pricing Table Delivery Table No. of Units Price 70 60 50 40 30 20 Method Shi Truck Road Rail Air 1.5 10 100 200 500 1000 2.5 Sales Table DeliveryFinal Total Discount Delivery Units Sold Total Cost Unit Delivery Total Delivery Shi 12 150 260 580 1010 679 230 125 25 Road Truck Truck Shi Rail Road Air Discount Total cost Discount 1 Discount 2 Discount 3 5000 10% 10% 15% 1000 0% 5% 10% 15