How to calculate 'Cost of Sale'

[Andrew_Stock1]

Hello, I am working on an app for my mattress stores that allows the Sales Consultants to quickly calculate Gross Profit in either $$ or %.

So far I have been able to calculate the GP$ which is a simple equation of ‘Sale Price’ less ‘Cost of Goods’.

But the cost of the sale can also affect the GP$ significantly because customers can pay for the goods in a variety of ways, here are a few different examples.

• If paying by Visa the Cost of Sale is 1.05%
• If paying by Amex the Cost of Sale is 1.75%
• If paying by Interest-Free Finance the Cost of Sale has to be calculated like this
  Sale Price - Deposit (less the method of deposit payment i.e. Visa 1.05%) = Balance (Amount Financed) - Cost of Finance (i.e. 10% Deposit over 18months costs my business 12.29%)

At the moment the app draws data from the ‘Product List’ table which holds, description, cost and sale prices of goods. ‘Cost Of Sale’ Table holds data listing each method of payment (there are 25 possible variations) and the associated costs of payment by that method.

What is the best way to calculate this allowing for the many variations of deposit amount finance period and payment method?

Any assistance would be greatly appreciated.

[1minManager]

SUM(Select(Product List[Sale Price]))

SUM(Select(Product List[Cost of Goods]))

(
Any(Select(Cost Of Sale[Percentage],[Payment Method]=[_ThisRow].[Payment Method]))
*
SUM(Select(Product List[Sale Price]))
)

That’s the simple version that would work for Visa & Amex

[Andrew_Stock1]

Hi Simon, thanks for that, so where do I put this code, do I put it in the Payment method column or GP$ column? Does it go in the formula field?

[1minManager]

This formula gives you the cost of using Visa & Amex. So either put it in its own column. Or if you want to change the formula to give you Gross Profit then:

SUM(Select(Product List[Sale Price]))

Any(Select(Cost Of Sale[Percentage],[Payment Method]=[_ThisRow].[Payment Method]))
*
SUM(Select(Product List[Sale Price]))
)

I’ve mentioned in another message to contact me directly if you need help getting this started - Simon@1minManager.com