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:

enter image description here

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:

enter image description here

enter image description here

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 enter image description here 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?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.