Write a macro that processes the following sales data (your final code should work with data that have any number of products, different number of price breaks, different quantities for a price break, different % of extra discount, different amount for a total discount and a list with more or fewer clients)
It can be safely assumed that the data in the ‘Products” worksheet has the word “Product’ in an anchor cell (e.g., cell A4) and the “Extra Discount” and “If more than” values are two columns to the right from the last column with price breaks. Similarly, the “Total Ordered”, “Total Amount” and “Total Amount (with extra discount)” columns are after skipping one column (as shown in the example below). In the worksheet named “Products” the following example data are given.
Product
Gadgetl
Gadget2
Unit Price for different quantities ordered
Minimum Order Quantity to get unit price
$10.25 $10.20 $9.51 $9.04
$12.40 $11.72 $11.42 $11.14
$9.50 $.00 $8.92 $8.45
Extra Discount
If more than
$25,000.00
Total
Ordered
Total
Amount
Total
Amount (with extra discount)
4
5
Thus, for example if a customer orders between 100 and 499 units of Gadgetl the unit price is $10_20 for each unit, not only for the units in excess of 100. Each customer can order different quantities of each of the gadgets. If the total dollar amount ordered by a customer exceeds $25,000.00 (number specified in cell G7 for this example — three rows down from the anchor cell (i.e., cell “A4” in this example) and two columns to the right of the last column with price breaks), then the customer receives an extra discount of 5% (number in cell (35 in this example — one row down from the anchor cell and two columns to the right of the last column with price breaks) of the total purchase Note that headers in this “Products” worksheet are pre-specified
The worksheet named “Clients” has a list of 10 clients (it can have more or fewer) with different quantities of products ordered. The program will populate the columns to the right of the last column with data (including headers). For example, the “Amount Before Extra Discount’ paid by the last client for Gadgetl is $9,311 _20
that is, the amount in cell F14 (in this example) of the clients worksheet would be $9,311 _20_ The “Total Amount’ in column J in the sum of the ‘Amount Before Extra Discount” over all products and depends on the quantities ordered as indicated in the worksheet “Products.” If, however, the “Total Amount” of purchase exceeds the “if more than” in the worksheet “Products,” ($25,000.00 indicated in cell G7 in this example) then the percent “extra discount” specified in the worksheet “Products” (cell G5 in this example) applies in addition to the discounts obtained by the quantity ordered of each gadget (i.e., in this example the 5% discount applies to each unit cost of the gadget bought by the customer). Note that the headers in this worksheet are pre-specified for the Clients, and the product names but the headers tor all other columns nave to be appropriately generated and placed through VBA. That is, the cells populated by the program in all the worksheets should be cleaned out manually every time the program is run and before calculating the new values.
using the data provided, the macro will calculate the “Amount Betore Extra Discount” tor each product, “Total Amount” before extra discount, “Amount Atter Discount (it any)” tor each product, and “Atter Discount Total Amount” tor each client in the worksheet “Clients”. In the worksheet “Products” report the number tor each product under the “Total Ordered” header, the “Total Amount” before the extra discount tor each product and the “Total Amount (witn extra discount)” tor each product
(Hint: Initially use cell “A4” as anchor cell in the products worksheet and also in the Clients worksheet. Define range variables tor the products and the price breaks and assign the number ot products and the number ot price breaks to variables Assign to variables the extra discount percentage and the amount required tor the extra discount You may need to use nested For Next loops to loop through Clients, then through Gadgets and then through Price Breaks. The loop through Price Breaks may be easier to do going trom the highest to the lowest order quantity tor the price break.)
Your code should work it the data are located in worksheets witn tab names and anchor cells that could be different to the names and anchor cells given here. That is, your code will work even it the anchor cell is different than “A4” for the price breaks and different than “A4” tor the client list In addition, your code should work even it the worksheet tab names are different than “Products” and
The tab name tor worksheets witn other product lists and other client lists and the anchor cells should be read trom cells Al to A4 Ota worksheet with tab name Scenario. For this example the “Scenario” worksheet looks as tollows:
Products
Product List Worksheet tabname
A4
Product List Reference Cell
Clients
Client List Worksheet Tabname
Client List Reference Cell
Format the cells using currency tor dollar amounts with a comma-separator tor thousands.
The final answer for this particular set ot numbers should be:
Unit Price for differ&’t
Minimum Order Quantity to
unit rice
Product eti
510.20
99.04
Extra Discount
Total
Ordered
069
Total
Amount
980,612.91
Amount (with extra discount)
Ga
Slr 14
If more than
7 176
$9.00 58.45 $25,cmoo 9,241
For the Products worksheet, and
For the Clients worksheet.