locked
Architecture on how to solve re-edit averaging problem RRS feed

  • Question

  • Dear All,

                I am trying to build simple pos application where I receive goods. The problem each time I receive goods I will average the overall cost price inclusive of the new stock. Then based on this new cost price I would transfer my goods over to another outlet. My problem is described below with an example.

    Ok let me explain the data first.

    1) ReceiveOrderID=1 so now the average price is 50.00

    2)ReceiveOrderID=2 the price is now 45.00 so the average become 47.50

    3)TransferID=1 so the average price is the transfer price that is 47.50

    4)RecieveOrderID=3 here where say there is a mistake happened instead of 45.00 then it was entered as 450.00 so the whole average price goes wrong it becomes 198.44

    5)TransferID=2 so the average price is now wrong and transfered as 198.44 so there after all the next stock which goes into the outlet will be wrong

    6)ReceiveOrderID=3 the price is now 50 so the average become 167.04 due to earlier mistake.

    So can you see one mistake will lead to all next mistakes? So do you have any idea how to solve this kind of mistake? Thank you.

    ** if this not the appropriate forum please advice where can I ask this question

    Friday, March 26, 2010 3:48 AM

Answers

  • to really simplify the entire problem presented here, it would be easier to consult your users for a deviation threshold limit (by percentage) if the input price would increase/decrease the average price by a given %, add another prompt to users to let them have a confirmation.

    let's put the threshold @ 20% and 1 item for easier calculation.

    Scenario 1:

    price input is within the 20% limit, for e.g. the current price is $20, the new price input is $22 and that will cause the price to be (22+20)/2 = $21. 

    (1/20)*100 is only a 5% increase in price thus it's still within the threshold.

    Show a preview page highlighting, bold or any other way to make the new figures significant.

    Allow users to submit their changes.

     

    Scenario 2:

    price input exceeds the 20% limit, for e.g. the current price is $20 and the new item is keyed in wrongly to $220 which caused the average price to be (220+20)/2 = $120. 

    Show a preview page and a BIG and eye catching message showing that the new price input will cause the price to increase by ((120-20)/20)*100 = 500%, you can decide to show % or $ which ever makes it clearer to the users.

    An intuitive approach will be to recommend a figure of <input value>/<% increase> (for e.g a 500% prompts an additional 0 being keyed in and you can compute a $22 instead of $220, if it's negative then add a 0. if it's $2200 then recommend $22 by finding out how much % jump is caused due to the erroneous entries.

    if the % increase is 4 digit (real number) then truncate the input by 2 digit, if it's 3, then truncate by 1. (Hope you can visualise what i'm trying to say here)


    Never stop learning.
    • Marked as answer by a.net sukbir Saturday, April 3, 2010 2:38 AM
    Tuesday, March 30, 2010 5:53 AM
  • A collection is a fancy sort of an array.  How fancy depends on what sort of collection you use but it can just be a var with a bit of linq and orderby to get them in chronological order.

    Then foreach through, calculating.  When date >= problem date you write the corrections.

    Assuming you use linq to sql or entity framework or some other orm then this ought to be pretty quick to write.  You're talking like 12 lines of code or something. 

    Please don't forget to mark posts which helped you.

    • Marked as answer by a.net sukbir Saturday, April 3, 2010 2:38 AM
    Friday, April 2, 2010 10:35 AM

All replies

  • Sorry I below is my sample data tables

    Receive Order Table

     

     

    ReceiveOrderID

    ProductID

    Outlet

    ID

    Quantity

    Cost Price

    Average Price

    Balance

    Stock

    Date Received

    1

    1234

    1

    5

    50

    50.00

    5

    1/3/10

    2

    1234

    1

    5

    45

    47.50

    10

    2/3/10

    3

    1234

    1

    3

    450

    198.44

    8

    4/3/10

    4

    1234

    1

    5

    50

    167.04

    11

    5/3/10

     

     

     

    Transfer Table

     

     

    TransferID

    ProductID

    Outlet From

    ID

    Outlet To

    ID

    Quantity

    Transfer Price

    Date Transferred

    1

    1234

    1

    2

    5

    47.50

    3/3/10

    2

    1234

    1

    2

    2

    198.44

    5/3/10

     

     

    Friday, March 26, 2010 3:49 AM
  • Validation on data entry is your first layer of protection.  Give a warning if the new cost price is more than say 20% different from the last and train staff to pay attention if the warning comes up.  Then they correct big mistakes at point of entry.

    If there's a person involved in the transfer then show them the last price as well as the new when they raise the delivery note or whatever.  Then encourage them to go fix problems.

    Friday, March 26, 2010 9:36 AM
  • Dear Andy,

                   Thank you for your reply. So ok say I have done the validation. Then when you say ask them to go n fix the problem.Any idea how to fix this problem? Is it a need of log file will help that I what is in my mind now? Thank you.

    Friday, March 26, 2010 5:02 PM
  • Well the validation should stop them entering the data wrong in the first place.  You should use inotifyerror to stop the transaction committing - so they correct in the text box or whatever and then commit the data. 

    For the second level where they create the shipping document or whatever they're doing then you probably don't want that guy able to just change the numbers.  So I would have a manual procedure.  They see the problem and don't ship the goods.  They instead report the issue to their supervisor who has an admin screen allowing him to edit the base data and recalculate the average.

    Hope that helps.

    Please mark my post(s) as answer if they help.

    Friday, March 26, 2010 6:01 PM
  • As a further thought.  You might want 2 sort of levels - a warning persentage change and a totally unacceptable error level of change.  Maybe 20% + is a warning allowing optional re-edit and 100% is error and reject.
    Friday, March 26, 2010 6:20 PM
  • Dear Andy,

                    I am not too sure about your inotifyerror method I have tried to google but did not find suitable material. But back to my original problem. So when you say allowing the supervisor to edit the base data and recalculate the average u mean to say do it at the database level manually is it? Thank you.

    Saturday, March 27, 2010 1:51 AM
  • Dear Andy,

                    I am not too sure about your inotifyerror method I have tried to google but did not find suitable material. But back to my original problem. So when you say allowing the supervisor to edit the base data and recalculate the average u mean to say do it at the database level manually is it? Thank you.

    Saturday, March 27, 2010 1:51 AM
  • Ah yes,   It's an interface I use and language specific.

    Whichever language you're writing in you can presumably throw an error that will stop changes committing to the database.  This would be the 100% or whatever amount you view is not going to happen except by mistake.

    You can also presumably put up a warning and have the user click OK to commit the changes or No to not commit and instead re-edit.  This would be the lower limit which is likely an error but potentially still valid.

     

    Saturday, March 27, 2010 1:35 PM
  • Dear Andy,

                   Thank you yes I am currently also capturing as howyou said by throwing an error and stop all the commit.  So I guess I am going to come with an idea of high limit and low limit pricing. So with your idea one part is solved. But again another part is having problem. Say for example the high limit is 100 and low limit 80. Suppose the use should key in 90 but he key in 95 any idea how to solve this type of problem. Thank you.

    Saturday, March 27, 2010 1:57 PM
  • Put up a message box that says "This is outside expected parameters are you sure 95 is right?"  On the messagebox are yes and no buttons.  The user clicks yes you update, he says no you don't and he must edit and retry ( or close your screen and abandon I suppose ).

    The exact details of how you do that are down to whichever development language you're using.  Some sort of modal form might be necessary if you can't make a messagebox have yes and no buttons.

    Saturday, March 27, 2010 3:56 PM
  • Dear Andy,

                   Sorry I guess I confused you. What I mean is that say for instance the cap is between 80 and 100. So supposingly the price is 90 but accidently he key in 95 but here when I check 95 is still within the upper and lower limit so I wont be albe to capture it as something out of the bound rite? Thank you.

    Saturday, March 27, 2010 4:30 PM
  • You could set your warning percentage lower to reduce the possible impact.

    Maybe.

    When the goods come in the guy enters the price.  This data is put into some other table or marked as entered but unprocessed.  Averaging is not yet applied.

    Some supervisor then has a screen where they see these incomplete receipts.  Goods incoming hand this guy the delivery note after they enter the data.  He then checks the numbers match, corrects any he doesn't like before commiting the transaction.  The data is then written to your current table, the averages recalculated and the entry deleted out of incomplete receipts.  When he finds a problem, after he fixes it he goes and disciplines goods incoming.

    That way you get two people checking the numbers. 

    The feedback to goods incoming might encourage them to be a bit more careful entering the numbers.

    An added plus is that two people would have to be bribed bu any buyer wishing to manipulate your prices.  Although this of course might not be much of an issue if those numbers of yours are real world.

    Saturday, March 27, 2010 4:52 PM
  • Dear Andy,

                   I agree with your idea is good idea and is suitable for establish business. The problem my solution is for small business where they wany things to be simple n fast. I was thinking another way is that when data comes in before they submit show them an overview before they press submit to confirm things what you think? What is your opinion about automating the process of edit of wrong average do you feel is a tedious job right cause require to call back lots of things and change each of it? Is there anybody done this solution before?

    Saturday, March 27, 2010 5:05 PM
  • I assumed that the cost was not already known. 

    If you already have an order raised with  a price then you can just have a screen where goods inwards enter the order number and confirm receipt.

    That kind of runs the risk of moving the problem to wherever the order is raised.  Office staff tend to be less error prone than goods incoming who are often sort of manual workers.

    Saturday, March 27, 2010 5:15 PM
  • Dear Andy,

                    I agree the problem this people dont even issue purchase order cause they just pick up a call n order their item from their supplier. So your earlier suggestion of keep into separate table to be approved sure they dont want to work that is why I suggested overview. What is your opinion on working back wards on the average problem is it very tough?

    Saturday, March 27, 2010 5:29 PM
  • Dear Andy,

                    I agree the problem this people dont even issue purchase order cause they just pick up a call n order their item from their supplier. So your earlier suggestion of keep into separate table to be approved sure they dont want to work that is why I suggested overview. What is your opinion on working back wards on the average problem is it very tough?

    Saturday, March 27, 2010 5:29 PM
  • If there's potentially one person involved and he can enter the number when the goods arrived, you're down to my initial suggestion.  There is nobody else can double check. 

    Warn at so many percent, reject at some higher percent different, allow later re-entry and recalculation.

    That's all you can do.

    Saturday, March 27, 2010 7:09 PM
  • i don't recommend using a hardcoded 20% to determine if the value is acceptable, what if the price of the goods indeed increased by 500%? there can't be an absolute failsafe %, unless decided by your stakeholders.

     

    Anyway, can't the average price be derived from the dates, price and quantity? If u normalize your table further then the amount will flex together with the price.

     

    Anyway when the users enter the price values, can't you show a "Preview" page with the figures generated and seek his confirmation? I know users don't always read instructions and confirmation, but during your user training, it's your trainer's duty to imbue the users the importance and impact.

     

    IMHO, there's no best way to prevent a human "mistake" programmatically, it all boils down to the user themselves to ensure the integrity of the data, we can only provide this much validation and dependencies etc. How many times have you tried to beat the registration page's validation? Email attachment extensions? The checks are there but there are a million and one ways to go around it.


    Never stop learning.
    Monday, March 29, 2010 10:18 AM
  • The warning percentage need not be hardcoded, I just pulled 20% out the air for illustrative purposes.

    You could design some algorithm that calculated a warning percentage based on real variation in a product or product type.  This could be seasonal.  Such an algorithm is probably more complex than recalculating an average though.  Simple is often best.

     

    Y

    Monday, March 29, 2010 2:27 PM
  • Dear Lee,

                  Yes offcourse I am planning to put the "Preview" page as one step to stop further problem. Besides that do you have any idea how to implement the recalculating average from the whole data starting from a particular point where the error of pricing happen. Just to clarify my given exmaple is just between 2 outlets but in reality will be between more than just 2 outlets. So when need to recaclulate need to take into consideration all this.

    Monday, March 29, 2010 5:12 PM
  • The details of how you do it depend on language and database.

    I would just have one step calculated the total Cost * stock for rows with balance > 0 up until the mistake.

    Then for each row, recalculate and update.

    Monday, March 29, 2010 5:49 PM
  • Dear Andy,

                    I am using C# as my language and my current db is mysql. So I dont get when your "totalCost*stock for rows with balance>0"? Can give me more details about this maybe an example will do? Thank you.

    Regards,

    Shai.

    Tuesday, March 30, 2010 4:56 AM
  • to really simplify the entire problem presented here, it would be easier to consult your users for a deviation threshold limit (by percentage) if the input price would increase/decrease the average price by a given %, add another prompt to users to let them have a confirmation.

    let's put the threshold @ 20% and 1 item for easier calculation.

    Scenario 1:

    price input is within the 20% limit, for e.g. the current price is $20, the new price input is $22 and that will cause the price to be (22+20)/2 = $21. 

    (1/20)*100 is only a 5% increase in price thus it's still within the threshold.

    Show a preview page highlighting, bold or any other way to make the new figures significant.

    Allow users to submit their changes.

     

    Scenario 2:

    price input exceeds the 20% limit, for e.g. the current price is $20 and the new item is keyed in wrongly to $220 which caused the average price to be (220+20)/2 = $120. 

    Show a preview page and a BIG and eye catching message showing that the new price input will cause the price to increase by ((120-20)/20)*100 = 500%, you can decide to show % or $ which ever makes it clearer to the users.

    An intuitive approach will be to recommend a figure of <input value>/<% increase> (for e.g a 500% prompts an additional 0 being keyed in and you can compute a $22 instead of $220, if it's negative then add a 0. if it's $2200 then recommend $22 by finding out how much % jump is caused due to the erroneous entries.

    if the % increase is 4 digit (real number) then truncate the input by 2 digit, if it's 3, then truncate by 1. (Hope you can visualise what i'm trying to say here)


    Never stop learning.
    • Marked as answer by a.net sukbir Saturday, April 3, 2010 2:38 AM
    Tuesday, March 30, 2010 5:53 AM
  • Whilst I agree generally with Lee.  I'm not sure that suggesting an input value is ever a good idea.  People can be very lazy and will quite likely accept whatever number.  Inevitably that will be wrong one day.  People tend to assume computer systems are kind of magic.  If the first couple of times the number the computer says is correct then some of them then put any old rubbish in, hit enter, then accept the suggestion because they decide that's quicker and they don't want to contradict the computer.  Some, people can be remarkably lazy.

    I think the simplest way to do correct the figures is to read the data into a collection of stock movements.

    A type with fields MovementQuantity, CostPrice, ExpectedAverage, ActualAverage, ReceiptOrTransfer

    Read the receipts into this using a datarreader or linq, then the transfers  with Quantity * -1 for the movementquantity.

    Sort the collection and iterate through it calculating as you go.

    When you hit the problem date, you over-write Average Price per receipt entry.

    Tuesday, March 30, 2010 9:20 AM
  • well the idea was there, my approach to tackling users is to give them the ease of use & performance & accurate transformation from business logic to usable applications. I think that there's no best way to prevent human error if they are bent to do it unless you don't accept values that deviates from the average by a given % at all.
    Never stop learning.
    Wednesday, March 31, 2010 2:01 AM
  • Users are tricky beasts.

    You can corner them using a whip and a chair but the moment your back is turned...

     

    Wednesday, March 31, 2010 8:04 AM
  • Dear All,

                I am very thankful to both of you to look into my problem. So based on what I have gather here is my idea to solve the problem first level have a preview page to reconfirm what they have entered. Second level as we know human still can make mistake so have to re-work on the average prices. What my idea is for each transaction I am planning to keep a log file the average before and after each transaction meaning for anyting be it recieve order, transfer stock and even sales? Then I guess using that I should work backwards? Can you all comment on it? Thank you.

    Wednesday, March 31, 2010 4:29 PM
  • That ought to guarantee you have the data to demonstrate your calculation is correct.  Being able to demonstrate why an algorithm produces a particular result and do so in a manner the client can understand is important.

    Wednesday, March 31, 2010 6:14 PM
  • Dear Andy,

                   So do you have any good idea how to go about the calculation? Any flow can you propose? Thank you.

    Thursday, April 1, 2010 4:12 AM
  • Isn't it even easier if you have a log table?

    I would read them into a collection and loop through in date order doing the calculation.  When log date >= problem then re-write the averages to both log and receive order table.  Then show the user the recalculated numbers I suppose.

     

    Thursday, April 1, 2010 9:01 AM
  • Dear Andy,

                    Yes I have now a log table to keep each of the average price before and after. So collection is an array is it? So I guess rewriting will be a big task rite cause we need to re-calculate for each line the average price and do the updates. Thank you.

    Friday, April 2, 2010 12:47 AM
  • A collection is a fancy sort of an array.  How fancy depends on what sort of collection you use but it can just be a var with a bit of linq and orderby to get them in chronological order.

    Then foreach through, calculating.  When date >= problem date you write the corrections.

    Assuming you use linq to sql or entity framework or some other orm then this ought to be pretty quick to write.  You're talking like 12 lines of code or something. 

    Please don't forget to mark posts which helped you.

    • Marked as answer by a.net sukbir Saturday, April 3, 2010 2:38 AM
    Friday, April 2, 2010 10:35 AM
  • Dear Andy, I am just putting a sample data below. Just say I run a query and find there are 3 lines effected by the price averaging as below.So I read the first line and say the new value for the AvgAfter is 200. So now shaii I immediately update it into my db. So how can the Id=2 pick my new updated value from the Id=1 for it to be corrected and thereafter the rest of the data. I dont know must I update immediately or shall I keep in a buffer. Any idea how to do the best storage. Id AvgBefore AvgAfter 1 200 2000 2 2000 1200 3 1200 1300
    Saturday, April 3, 2010 2:46 AM
  • Sorry my table did not come properly earlier

    Id

    AvgBefore

    AvgAfter

    1

    200

    2000

    2

    2000

    1200

    3

    1200

    1300

    Saturday, April 3, 2010 2:47 AM
  • I would expect to see date, quantity in stock, cost and Movement Quantity in your table.

    I'm not sure how your calculation works but I get the impression you need to work forward from zero stock position.

    I think you could maybe split the problem in two, but to be honest the whole idea of an average price is kind of strange.  I've worked on a number of order processing and sales systems.  None of them work like this one.

    I would think Average price is going to be a: (sum(quantityInStock * Price ))/ b:sum(quantityInStock).  I'm not sure if you can do that with linq on your dbms but you could start with one linq/sql query where date < problem date and return a and b.  The sole purpose of the a and b in there was so I can easily refer to them.

    Then I'd do another piece of linq returned all the data for >= problem date in a var.

    Declare a couple of variables to hold your totals.

    Loop through foreach, calculate and do your database updates.

    Saturday, April 3, 2010 9:53 AM
  • Dear Andy,

                    Off course I have to have the quantity and date in my log table too. What is your idea if I work from the date of the problem because working from zero stock will be from day one right? I dont get you when you say what is strange about my average isnt it that I am doing the same as you what I do is take my (current stock value + new stock value)/(current stock quantity + new stock quantity) can you please highlight to me where my problem is? I am using mysql I dont know what is is the a n b you are refrencing too? Hope you can give me more hints really thank you for helping me all this moment. 

    Saturday, April 3, 2010 5:09 PM
  • I assume at some point they may sell out and have zero stock or you have some sliding cut off date.

    An average price which goes back to system start is meaningless.

    To illustrate this.

    Think about a company which has been in business 50 years selling mars bars.  None of the mars bars they bought 50 years ago can be on sale now because they will have gone off long ago.  The price they were paying for mars bars 50 years ago will bear no relation to current prices because of inflation.  So including that product from 50 years ago in a pricing algorithm makes no sense.   

    This would be complicated further if they pay in a foreign currencies for wholesale and further again if they retail in different currencies due to exchange rate fluctuations.

    Maybe 50 years just sounds a bit silly.

    How about last year?  They got a bargain price on mars bars and sold a lot.  Whilst they never went out of stock all the cheap stuff was gone in a month or two.  Apply your algorithm and it would appear their average cost would still be dragged down.

    But last years cost has nothing to do with this year's.

    Or are you going to track specific batches of the stuff?   Because if you do that then if you think the current calculation complicated you'll be going into meltdown if you try to match which specifically costed product sold when.

     

     

    Sunday, April 4, 2010 10:12 AM
  • Dear Andy,

                   So based on your experience what is the best solution price scheme to be followed maybe I can try out. Thank you.

    Sunday, April 4, 2010 10:33 AM
  • Retail price has to be linked somehow to wholesale, otherwise working out profit will be rather difficult.

    I would use a sliding cut off, how long you go back depends on the factors I mentioned.  Shelf life, turnover and how dynamic exchange rates are should come into it.  

    Depends if your software is aimed at a vertical market or not as to how sophisticated you should make it and whether you need one entered per product per branch or what.

    Find the last amount in stock and cost price before the cut-off and roll forward.

    Sunday, April 4, 2010 2:15 PM