Need a GOOGLE DOC / EXCEL Market Analysis Tool

I have database in Excel that I need to analyze to generate three report list. The data is a list of Vehicles sold in a given market and it captures the Year Make, Model and Trim Level(MMT) for each sale. From this data I need to generate three list. First one is a volume list calculating how many units were sold for each make, model and trim. This list would be sorted by Volume with the largest volume in the TOP ROW (after headers obviously) and the lowest one at the bottom. The second report would be a list of the average time it takes to sell each vehicle on thst list. This would be done by adding up they days it took to sell each unit and dividing by the number of units sold from the first list. This list would be sorted from Lowest Average days sold to highest. The only caveat is this, when calculating average days sold the Formula should include an IF/THEN rule. IF Volume Sold# < 3 THEN AVERAGE AGE = ((Total days on market for all vehicles sold in that MMT divided by Volume SOld for tht MMT)+15days))

Once both list are generated, a value will be assign to each MMT based on its' ROW position on each list. The over all rankings will be determined by adding the ROW position for volume to the ROW position of Average days to sell. For example, a vehicle that sold 10 units and placed 20th on the Volume List and sold in an average of 37.24 days and placed 29th on the Average Turn List would get 49 Points for overall ranking, and a vehicle that sold 6 units and placed 40 th on Volume but sold in 18 days on average placing 7th on the list would get a 47 rating and be ahead of the one that did more volume.

To adjust for lack of data any vehicles showing less than 3 sales get an automatic 20 day add to there Average Days to sell. The goal is to create a Sheet that I can drop raw data into and have it automativcally create the Pivot table or databases to generate said output instantly/ The attached Excell SHeet has Two Datasets label RAW data. 1 is for Honda Dealers only (this will change depending on brand we are analysing) Second Data set in the entire market. Though this data set is small the formulas should be set up to allow me to drop in Raw data up to 50,000 records deep or as large as possible. Once you've set up the spreadsheet tool todo the calculations for the Direct competitor it is basically just a copy paste exercise to replicate the analysis for the Entire Market data set. Time is of the essence on the and I am hoping it is something that can be completed in a few hours by an experience database programer.

Vnos podatkov Podatkovno rudarjenje Obdelava podatkov Excel Raziskave

ID projekta: #18797453

Več o projektu