0
$\begingroup$

The dataset that I'm working with has 2 independent variables (qty, volume) and 1 dependent variable (cost). When I plot individual X with Y, it turns out qty vs cost gives an exponential decay trend line while volume vs cost gives a linear relationship.

I'm trying to come up with a linear model (since it's beginner-friendly) to predict the cost when a new input, volume set is given. I tried to excel and while the statistic output looks reasonable, the predicted values are off quite a lot. I read up on some articles and there is something regarding log transformation so that both qty and volume have a linear relationship with cost. I'm not sure if log transformation applicable in this situation. What would be the best way to approach this problem?

I plan to use R instead of excel next.

Here are some outputs of what I've done

enter image description here enter image description here

enter image description here

enter image description here

Note: Thickness, width, and height essentially become volume so it doesn't affect the data much

$\endgroup$
1
$\begingroup$

I would suggest transforming "qty" into the log space. Even, you can do this using Excel. You can make a new column (for example qty_log), which is equal to the log of "qty". Then, you can fit a linear regression as follows:

$cost = a1 * log(qty) + a_2 * volume + a_0$

You will get much better results in this case.

This equation simply means that try to fit a model using volume and log of qty for predicting cost. You did so before but you used qty instead of log(qty)

$\endgroup$
  • $\begingroup$ Thank you, can you explain in your equation what the term after a1 is? It looks like qtylog, not sure what it means. $\endgroup$ – Fierid Neil Jun 17 '20 at 16:04
  • $\begingroup$ I add two more lines. Just try to transform qty to log space as I explained using excel. Then, fit a model as you did before. You just need to use the new column (which is log of qty) instead of using qty directly. $\endgroup$ – nimar Jun 17 '20 at 17:08
  • $\begingroup$ I tried it and the predicted values come out much closer to the expected. There's one issue with taking the log(qty) however is that if qty is 1, then log(qty) becomes 0 and it throws off the prediction. I read a discussion saying to do log(qty+1) instead however it gives higher SE. Do you have any suggestions? $\endgroup$ – Fierid Neil Jun 17 '20 at 20:27
  • $\begingroup$ It depends on your data. In most cases, log(qty+1) is the best solution. $\endgroup$ – nimar Jun 17 '20 at 23:18

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.