1. Business
  2. Accounting
  3. in vba in excel the worksheet dataof the file profit...

Question: in vba in excel the worksheet dataof the file profit...

Question details

IN VBA IN EXCEL*********

The worksheet Dataof the file Profit Report.xlsm contains sales data for the products that a company sells. The data for each product is provided in a row, starting with row 2. For example, for the product with the product ID “vqz12”, the unit procurement cost c is $2.5, unit sales price p is $6, inventory I at the beginning of the period was 100.1 units, and the demand D during the period was 200.3 units.

Create a sub called ProfitReportthat does the following:

1. From worksheet Data, it reads the data for each product, one by one.

2. For each product, it calculates the number of units sold S and profit as follows: Sales S = Minimum(Inventory I, DemandD). Assume that sales is the minimum of inventory and demand for the product. Profit = (p – c)xUnits sold S

3. For each product, it reports the product ID, sales S, and profit on worksheet Output, under columns A, B, and C, respectively, starting with row For example, product ID, sales S, and profit for product “vqz12” should be reported in cells A4, B4, and C4, respectively.

4. At the end, it displays the message “The results for [number of products] products are reported.”, with the partin brackets replaced by its correct value.

Assume that the data for the first product will always be located in row 2 on worksheet Data, however we do not make an assumption about the number of products on worksheet Data (except that there will be atleast one product). Therefore, make sure that your code works for any number of products

A4 1 Output Report: Profit 3 Product ID Sales S 4 9 10 12 13 14 15 16 17 18 19 20 Output Data

A9 1 Product ID Cost c Price p InventoryI Demand D vqz12 $ 2.50$ asq56 $14.80$ 17.20 weg01 7.0010.10 plg92 $ 22.10$ 24.00 rtj46 $12.00 16.30 uid30 $ 9.00 $14.00 od86 $ 32.60 39.00 6.00 100.1 150 50 250.5 300 220 125 200.3 100 10 400.3 250 275 200 4 10 12 13 14 15 16 17 18 19 20 Output Data

Solution by an expert tutor
Blurred Solution
This question has been solved
Subscribe to see this solution