Opportunity evaluation within excel

#1
I'm a student of data analytics.
I've the following raw data https://1drv.ms/x/s!Amd7BXzYs7AVhAqQGtBZLHzQ1xZ6
The main goal as for finished example :https://1drv.ms/x/s!Amd7BXzYs7AVhAsWa0e5yvqPhuRf?e=DyEmyo
Base on provided information we need to determine project revenue and cost.
  1. Revenue: Asset recovery value aka Fair Market Value (FMV), or projected sales of recovered assets
  2. Cost: Costs of removal from Customer facility and processing of the assets.
We realize
  1. Revenue based on equipment value of the models/parts number, with consideration of its technical configuration(specifications) and physical conditions: grade A (excellent), B(good), C(bad), D (e-scrap).
  2. Cost based on a. weight and b. quantity of serial numbers that needs to be processed.
Upgrading Source Data (customer provided inventory)
As a result of our data processing, we will have the following information for each asset:
  1. Manufacturer’s name (Dell, Lenovo, HP etc)
  2. Type of asset (Laptops, Desktop, LCD, Printer, HDD etc)
  3. Asset part number or model number (Latitude 7440, T460, etc)
  4. Equipment specification
  5. Number of assets of the same part, model number and/or type
  6. Number of billable serial numbers
  7. Number of hard drives to wipe
  8. Weight of each asset type
In many cases such lists do not contain full specs of PCs/Laptops that need to be retrieved using their SNs from manufacturer’s websites or other reliable resources as shown below:
HP: https://partsurfer.hp.com/search.aspx Lenovo: https://support.lenovo.com/us/en/ Dell: https://www.dell.com/support/home/en-us?c=&l=&s= Apple: https://everymac.com/ultimate-mac-lookup/
Specs must contain:
  1. CPU type (i7-2620M); 2.
  2. RAM Capacity (8GB);
    1. HDD type and capacity (256 GB SSD)
Tip: if the pattern of SNs is the same (e.x. CNU2102FH6 & CNU1260B1L) – highly likely specs will be the same so retrieving specs for every single SN is not required.
After building a detailed list of specs create a pivot table with Type of asset; Make; Model; Qty; CPU; RAM; HDD.
Populating price column
In excel you are building add columns PRICE and TOTAL SUM for FMV estimate of total value of the lot.
Based on the information provided and generated through procedure above, search same models/specs on eBay.com to find an average sale price.
Set eBay filter to show only SOLD ITEMS:

The laptop prices are built based on this excel https://1drv.ms/x/s!Amd7BXzYs7AVhAzosuoEQLjH8npj?e=HzdmMa
For example. If we are pricing used good Lenovo T460 laptop, we should not consider Lenovo T460 sold in neither in new, or refurbished, nor used defective condition. New or refurbished item will be way overpriced, while defective item will be underpriced. Used good Lenovo T460 for this example, should be compared only with another T460 in used good condition
Populating weight column
Weight of the shipment is estimated by grouping items by type and using average weight for that type of product. This information is provided on the template that you started from.
  • If you already have a list just group assets by type and assign weights
  • If you don’t have a list create ICT work product list from scratch (like shown below) using photos of equipment in bulk to count quantities.
Sample of spreadsheet with calculated weight:


Round weights up to integer numbers and total weight. Round the weight to the nearest higher number to account for packaging. Below is a list of most common types of equipment
If case of major uncertainty Google weights of item – it is usually searchable. Use common sense to validate.
Identifying serialized assets
Serialized Assets - Assets that usually accounted and tracked on the company books, capital assets, assets of value: desktop (DT) and laptop (LT) computers, Thin Clients, Printers, Copiers, Monitors (LCD or CRT), Servers, Cell phones, Tablets, Storage and Networking devices, and similar hardware. Hard Drives (HDD) are also counted as Serialized Asset regardless of whether they are loose or installed in LT/DT/Server. Unsterilized General waste - Assets removing in bulk when a serialized report of disposal is not required (e.g., keyboards, mice, phones, headsets, hotspots, docking stations, monitor stands, WIFI access points etc.). Empty Server Racks are considered as general waste.
Checking that Pricing for Customer and ICT Cost sections are correct
After you are done with finishing the product per instructions above 2 spreadsheets below called Pricing for customer and ICT Costs should get populated. I need to verify that those sections are correct.

This is what I try https://1drv.ms/x/s!Amd7BXzYs7AVhA1XFZ0Mw7DsARjx?e=miCwoa , Could someone help me to validate what I try please?