Landing Cost Without Tax: Formula and Explanation

To explain the complete process, I have prepared the MIT sheet as shown in the screenshot below, which includes four line items. For the first two line items, I have left the Landing Cost field as "BLANK".

- For the first line item, the value of "Rate_is_inclusive_of_all_Taxes" is set to "Y".
- For the second line item, the value is set to "N".
- For the third line item, the value of "Rate_is_inclusive_of_all_Taxes" is also "Y".
- For the fourth line item, the value is set to "N".

The purchase price for all line items is set to 20/-. For the third and fourth line items, I have entered a landing cost of 25/-.

The GST rate for all items is 5%.

After successfully uploading the data, the report generated was as follows:

- For the first line item, the landing cost was recorded as 20/-, with the "Landing Cost Without Tax" amounting to 19.05/-. (Note: "Rate_is_inclusive_of_all_Taxes" is "Y" for this item.)
- For the second line item, the landing cost was recorded as 21/-, and the "Landing Cost Without Tax" was 20/-. (Note: "Rate_is_inclusive_of_all_Taxes" is "N" for this item.)

From this, we can conclude that if a customer desires their "Landing Cost Without Tax" to equal the purchase price, the "Rate_is_inclusive_of_all_Taxes" value must be set to "N." This setting ensures that the calculations are performed correctly.

For the third and fourth line items, where the landing costs were manually set to 25/-, the system retained these landing costs for both items.

- For the third line item, the landing cost was recorded as 25/-, and the "Landing Cost Without Tax" became 24.05/-. (Note: "Rate_is_inclusive_of_all_Taxes" is "Y" for this item.)
- For the fourth line item, the landing cost was recorded as 25/-, and the "Landing Cost Without Tax" became 24.00/-. (Note: "Rate_is_inclusive_of_all_Taxes" is "N" for this item.)

The conclusion drawn here is that if a customer provides landing costs in the sheet, the "Landing Cost Without Tax" will be automatically calculated by our software.

Landing Cost Without Tax formula: 

Cost Without Tax = (Purchase Price - (Purchase Tax/Receipt Unit)

mid_sale_tax= case when MIH_INCLUSIVETAX = 'N' then mid_mrpMID_SALE_TAX_PERC/100 else (mid_mrpmid_sale_tax_perc)/(100+mid_sale_tax_perc) end , mid_pur_tax=MID_PUR_PRICE - (MID_PUR_PRICE*100/(100+MID_PUR_TAX_PERC))

Thank you!