We need a price calculator extension as per attached excel sheet. In our store we have two types of customers Private and Company. The logic in excel sheet is as follows:
1. Cell A6: Net cost: This is cost to store from product supplier. This will be filled manually.
2. Cell A7: Cost mark-up: The cost mark-up %age will be filled manually and the value will be calculated as netcost*costmarkup %age (C6*D7)
3. Cell A8: Net out price: it is sum of net cost and cost mark-up (C7+C8)
4. Cell A9: VAT: This is tax applicable on net out price. The tax %age (D9) will be filled manually and it will calculate VAT value in C9 as C8*D9
5. Cell A10: Out Price: it is total price to client including VAT i.e. sum of net out price and VAT (C8+C9)
The prices to a private customer group is Out price i.e. cell C10. Whereas prices to company customers will be shown net out price (i.e. without VAT). Cell C8.
Now if the product is on sale the prices will be readjusted based on how much %age off on those products. As soon as admin selects the product is on sale for a particular period it will work as follows:
1. First admin will activate that product is on sale. In this case it will gray out all the rows for normal prices.
2. Cell A11: Sale Price: Admin will fill sale %age off in cell D11 manually and it will calculate how much would be the out price including VAT in C11. And formula is C10*D11
3. Cell A12: Readjusted Net price w/VAT: It will calculate the net price without VAT. (C11/(1+D9) in cell C12
4. Cell A13: Re-adjusted VAT: In cell C13 the system will calculate new Vat value based on re-adjusted net value i.e. C12*D9
5. Cell A14: Readjusted mark-up: In cell C14, system will calculated adjusted mark-up automatically as C12-C6 and in D14 it will show same in %age i.e. (C14/C6)%
6. Cell A15: Savings to company customer group if product is on sale in C15 as C8-C12
7. Cell A16: Savings to private customer group if product is on sale in C16 as C10-C11
The prices to a private customer group will be shown Sale price i.e. cell C11. Whereas prices to company customers will be shown Readjusted Net price w/VAT i.e Cell C11