• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help Please!! how to make this type of analysis?

danielitaiyta

danielitaiyta

New Member
Hello,

My name is Daniel and I am new to this forum, I am not an expert in using excel and I will really appreciate your help. My problem is, in my internship my boss asked me to run a report of shipping from last week and get the the real price, discount, and final price of that report. What she really wants is to see if is there any consistency in the prices, discounts, and final price. Her main goal is to negotiate the price with new vendors, and she will use that information to get the products at the lowest price as possible. My question, is how I can find the consistency of prices, is there any formula, or process, or what type of calculations in excel I can do in order to find the estimate or "appropriate" price. Please, I will really appreciate your help, it is my first internship and I want to do my best.!!

Thank you
 
Thank you for replying my post, honestly I do not know if is okay if I share the information of the report in here. I just do not want to get in trouble for displaying the information of the company. However, I attached the file and it is only showing the shipping costs. Please let me know if is enough or if you need more data, thank you again.
 

Attachments

  • Report.xlsx
    14.3 KB · Views: 6
Hi ,

The data that you have uploaded shows a tremendous amount of variation !

The values in column A have a MINIMUM value of 19 and a MAXIMUM value of 745 , with some rounding off.

Similar results for the other columns are :

Column B - MINIMUM = 0.31 , MAXIMUM = 99.47

Column C - MINIMUM = -469 , MAXIMUM = -10.5

Column D - MINIMUM = 12 , MAXIMUM = 376

Other than this , there is nothing much that can be said , except for other measures such as the MEDIAN value ; other measures such as STANDARD DEVIATION can be obtained , but one important factor in such charges is to relate it to distance , which is not present in the uploaded file.

Narayan
 
Apart from what NARAYNK991 said - freight cost would also depend on carrier so will vary
 
Don't forget weight and size of the package. Lots of difference shipping a metric ton versus a pound or a pallet versus a small box.
 
Again thank you for your replies and suggestions, here is the file with more information. Also, I found the % of discount and freight cost and used a pivot table. What I noticed was that they give more discount if they used the service type SO and PO. However, seems that is not enough information and try to use the regression analysis for weight and price but I do not understand it, seems that my model is not significant.
Thank you, I really appreciate your answers
 

Attachments

  • Book1.xlsx
    22.6 KB · Views: 9
You must log in or register to reply here.
Back
Top