Excel good enough or some other programming language?

[Siyu]

Hi all,

Like to seek some advice. use a screenshot.

Any experts can give some suggestions?



TIA

 

[davidktw]

Hi all,

Like to seek some advice. use a screenshot.

Any experts can give some suggestions?



TIA

Seems like your needs can be as simple as pull down menus for each selection with some formula involved.

You might want to clarify on what does "previous selection" means. You mean based on the last combination of selections, after which you then re-select the combination and then you wanted to calculate the differences ?

That is not a problem either. You can also achieve this with some VBA, storing a "total cost" cell that perform a simple summation of the other cells' values.

 

[SiaoAngMoh]

Seems like your needs can be as simple as pull down menus for each selection with some formula involved.

Exactly.

 

[Siyu]

Hi,

Actually this is the 2nd part of the program required.

1st part -

Colleague enter the product cost (in USD, EUR, etc)
The program takes the live rates and convert into SGD

2nd part takes into consideration of the Freight Cost (as in my previous post)

3rd part (the manager will check if the shipment is regulated or not - this is a separate step as the manager must look)

4th part (Hidden from the staff)
Add a margin eg. 33% (but the staff cannot know)

The final selling price is calculated and given to the staff.

Can all these be done in excel? The formulations should be hidden from the person who input them (except when she input the name of the product and cost in USD).

 

[davidktw]

Hi,

Actually this is the 2nd part of the program required.

1st part -

Colleague enter the product cost (in USD, EUR, etc)
The program takes the live rates and convert into SGD

2nd part takes into consideration of the Freight Cost (as in my previous post)

3rd part (the manager will check if the shipment is regulated or not - this is a separate step as the manager must look)

4th part (Hidden from the staff)
Add a margin eg. 33% (but the staff cannot know)

The final selling price is calculated and given to the staff.

Can all these be done in excel? The formulations should be hidden from the person who input them (except when she input the name of the product and cost in USD).

The straight forward answer is YES, VBA can do what you wanted.

For the 1st part, you sure your company doesn't need to hedge against the currency and wanted the global exchange rate ? VBA allows for HTTP connection to external sites to retrieve data, so you can invoke a HTTP API to serve your needs. I have written a very simple VBA script that fetches latest exchange rates via a free currency exchange service from http://fixer.io

These are some references which might be useful to you.
http://stackoverflow.com/questions/17063550/equivalent-curl-in-vba
http://stackoverflow.com/questions/19360440/how-to-parse-json-with-vba-without-external-libraries

You can download the excel from https://www.dropbox.com/s/5kd1lstraj42zze/demo_alpha.xlsm?dl=0 to see how it works.

2nd part, I have already answered you

3rd part, this is not something you want to do in your excel. What you are describing is a workflow.

4th part is harder depending on what level of secrecy you are talking about. Having a VBA macro in your excel which is to be distributed will be hard to ensure secrecy. The only way I know which is safe is to export the formula functionality outside of the excel sheet. Your excel sheet can invoke an external API call to provide the data and the API compute the result and put back into your excel sheet. The same way how I have demonstrated in the demo alpha excel sheet that I have provided to you.

I will take a screenshot of the codes in case you have problem opening up the macro of the excel.

 

[Siyu]

Thanks. I will probably hire some freelancer to do help me.

Describing this project will be a challenge.

Like to check with you on 2nd part.

As there are 4 factors a) Source/Origin b) Weight c) Shipping conditions d) Hazardous

The permutations/combinations are so many, it 2304? (4!4!2!2! - is my maths correct)?

for eg. If SEA, 1-50kg, dry ice, Not harzardous = X cost
If I change SEA to AP, 1-50kg's cost is change so are other factors ---> which are dependent on each other. How do i describe this portion to my freelancer?

Is it the programming language of IF, OR, etc?

Almost like saying, each choice i make has different costs, for subsequent choices it also has different costs. One choice affects the other. So it makes "2304" permutations/combinations

correct? Hope you can understand...

 

[Shalomp]

4th part (Hidden from the staff)
Add a margin eg. 33% (but the staff cannot know)

The final selling price is calculated and given to the staff.

Hi i would like to understand your problem better as i myself am learning and find this interesting

My question is won't the staff find out the margin themselves by calculating backwards as long as they are given the final selling price number and they know the other numbers in the intermediate steps?

 

[davidktw]

Thanks. I will probably hire some freelancer to do help me.

Describing this project will be a challenge.

Like to check with you on 2nd part.

As there are 4 factors a) Source/Origin b) Weight c) Shipping conditions d) Hazardous

The permutations/combinations are so many, it 2304? (4!4!2!2! - is my maths correct)?

for eg. If SEA, 1-50kg, dry ice, Not harzardous = X cost
If I change SEA to AP, 1-50kg's cost is change so are other factors ---> which are dependent on each other. How do i describe this portion to my freelancer?

Is it the programming language of IF, OR, etc?

Almost like saying, each choice i make has different costs, for subsequent choices it also has different costs. One choice affects the other. So it makes "2304" permutations/combinations

correct? Hope you can understand...

While there are 4 factors, I believe your problem is not trying to produce total number of different outcomes based on the input factors.

It's a formula that is in play here. For simplicity, we can say the same for the multiplication between 2 numbers. You are not going to tell the programmer that there are infinite permutation right ? Since your 2 real numbers will have infinite range or maybe 32/64bits of range.

What you need to relay to your developer is how the different inputs will affect the result. This is the logic that your developer need to understand. For eg: If it the goods are hazardous and hence all the cost will be multiplied by an additional 20% for logistic overhead. That's just one factor on its own. It does not affect the other factors. Hence the factor is rather independent from the rest of the factors. However if the business logic is if the goods are hazardous and thus only certain shipping conditions are allowed for the end-user to choose, then you must make known to the developer so that when the developer code the pull-down menu for "Shipping conditions", it will not include choices that does not fit in the case of a hazardous delivery.

 

[Siyu]

Totally agree with you.

 

[ykgoh]

If you can work out formula with the factors and weights in algebra form how the total cost is calculated, then your programmer can easily implement it. This is specific to your business rules how you calculate it, and the programmer will not know that.

I believe there are some trade secrets in the formula which you are unable to share in public forum, or even to a freelancer programmer you hire. That's why I think you're trying to assess the complexity of implementing it yourself?

 

[Siyu]

That is correct, not really trade secret la. it's like u are in the biz for a long time then easier to do.