How to Calculate (Best Practice)

T

tusharmehta

New Member
Joined
May 12, 2014
Messages
34
Dear Everyone,

While doing inventory base calculation most frequently asked questions I had seen is calculation of item wise discount from today invoice/document discount.

I am here with sharing test example in that I would like to see how to calculate Discount % and DiscountValue so please can you look into and guide which will be the best calculation method is more suitable while using PowerPivot (Best practice).

Below are two tables one is header and second is child (Summary and Detail tables), Today invoicediscount value need to be divide for each item base on it's SalesAmt (Ratio).


[TABLE="class: grid, width: 811"]
<tbody>[TR]
[TD]DocDate[/TD]
[TD]DocNo[/TD]
[TD]InvoiceAmt[/TD]
[TD]InvoiceDiscount[/TD]
[TD][/TD]
[TD]DocDate[/TD]
[TD]DocNo[/TD]
[TD]Itemcode[/TD]
[TD]SalesAmt[/TD]
[TD]Discount%[/TD]
[TD]DiscountValue[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]2000[/TD]
[TD][/TD]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]1[/TD]
[TD]ABC1[/TD]
[TD="align: right"]3000[/TD]
[TD] ??[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]1[/TD]
[TD]BEC1[/TD]
[TD="align: right"]2000[/TD]
[TD] ??[/TD]
[TD] ??[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]1[/TD]
[TD]EDG3[/TD]
[TD="align: right"]1000[/TD]
[TD] ??[/TD]
[TD] ??[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]1[/TD]
[TD]ABC1[/TD]
[TD="align: right"]4000[/TD]
[TD] ??[/TD]
[TD] ??[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]2[/TD]
[TD]ABC1[/TD]
[TD="align: right"]1000[/TD]
[TD] ??[/TD]
[TD] ??[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]2[/TD]
[TD]BEC1[/TD]
[TD="align: right"]950[/TD]
[TD] ??[/TD]
[TD] ??[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]2[/TD]
[TD]RTSF[/TD]
[TD="align: right"]50[/TD]
[TD] ??[/TD]
[TD] ??[/TD]
[/TR]
</tbody>[/TABLE]


FYI

In document number 1 (One) item is getting repeated twice (ABC1).

Thanks in Advance.
 
Last edited:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Sorry I forget to add shop code one above mentioned tables correct tables list are mentioned below:

[TABLE="class: grid, width: 422"]
<tbody>[TR]
[TD]DocDate[/TD]
[TD]DocNo[/TD]
[TD]ShopCode[/TD]
[TD]InvoiceAmt[/TD]
[TD]InvoiceDiscount[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]3000[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 539"]
<tbody>[TR]
[TD]DocDate[/TD]
[TD]DocNo[/TD]
[TD]ShopCode[/TD]
[TD]Itemcode[/TD]
[TD]SalesAmt[/TD]
[TD]Discount%[/TD]
[TD]DiscountValue[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]ABC1[/TD]
[TD="align: right"]3000[/TD]
[TD]??[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]BEC1[/TD]
[TD="align: right"]2000[/TD]
[TD]??[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]EDG3[/TD]
[TD="align: right"]1000[/TD]
[TD]??[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]ABC1[/TD]
[TD="align: right"]4000[/TD]
[TD]??[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]ABC1[/TD]
[TD="align: right"]1000[/TD]
[TD]??[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]BEC1[/TD]
[TD="align: right"]950[/TD]
[TD]??[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]RTSF[/TD]
[TD="align: right"]50[/TD]
[TD]??[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD]RTSF[/TD]
[TD="align: right"]1000[/TD]
[TD]??[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD]ABC1[/TD]
[TD="align: right"]4000[/TD]
[TD]??[/TD]
[TD]??[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Your post is very hard to understand. I realise that English is probably not your first language but I would still like to help. So are you trying to work out the line level discount in the second table based on the discount given in the header table? How will you allocate the header discount to the detail? Is it a straight allocation based on value?

This is what I would do.
1. create a unique ID for the header file. It looks to me like this is a concatenation between DocNo and ShopCode. So create a new column that concatenates these two columns into a single unique value/column.
2. Load the Header file with this unique column as a lookup table.
3. create the same concatenation for the detail table
4. Join the detail table to the header table.
5. Create new calculated fields to calculate the % discount in the header table.
6. Apply this discount to the line level in the detail table.


Here is an Excel 2013 workbook that does this. I hope you have Excel 2013 https://dl.dropboxusercontent.com/u/30711565/mrexcel 1.xlsx
 
Upvote 0
Thanks Matt Alligton,

Appreciate your efforts and cooperation to help me out.

I had gone through your response and found that it is bit different than I had thought about it.

I had also achieved require result using calculation and LASTNONBLANK.

https://drive.google.com/file/d/0B_c8gU3TDT4rUV9oTUozUVFBa1U/view?usp=sharing

I am attaching the file which I had worked on it please if anyone has suggestions please let me know.

Thanks in Advance.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Create accounts by user form to show balance by calculation for two columns
Replies
4
Views
109
abdo meghari
A
Tim_D
  • Question Question
VBA Structure Best Practices and Code Review
2
Replies
13
Views
6K
dmt32
D
T
  • Question Question
Using a dynamic formula to calculate value of reinvested dividends over changing time periods
Replies
2
Views
3K
Tuckbox
T
T
  • Question Question
Calculating Sums over two tabs
Replies
1
Views
498
trafficzombie
T
T
  • Question Question
Fundamental calculation understanding Issue
Replies
4
Views
703
tusharmehta
T

Forum statistics

Threads
1,224,099
Messages
6,176,343
Members
452,722
Latest member
lexalux

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top