Page 3 of 3 FirstFirst 123
Results 31 to 45 of 45
  1. #31
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    661
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Steve, here's a sample where there is only one entry of the labor billing with no expenses.

  2. #32
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    What do you want the %Profits to be when there are no parts? Your calculation is (Parts-Cogs)/Parts and all are zero. I think the Div/0 error is appropriate and completely accurate

    If you want it to be 0% like your intermediate calculation shows, then use:
    =IF(SUMPRODUCT(($A$5:$A$439="Parts")*($D$5:$D$439+ $I$5:$I$439))=0,0,(SUMPRODUCT(($A$5:$A$439="Parts" )*($D$5:$D$439+$I$5:$I$439))-SUMPRODUCT(($A$5:$A$439="Cogs")*($D$5:$D$439+$I$5: $I$439)))/SUMPRODUCT(($A$5:$A$439="Parts")*($D$5:$D$439+$I$5 :$I$439)))

    Steve

  3. #33
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,213
    Thanks
    46
    Thanked 231 Times in 212 Posts
    kzkz,

    Actually meant to create a pop-up confirmation form to verify before clearing sheet. What would be a very doable thing would be to place a button that will create a new tab named with the following week and the date on the form itself so that if you print it, you will know which week it refers to. By the way, what does COGS stand for?

    By the way, the nice thing about an update button is that you can verify that the entries you made have taken place by the visual change in the calculated values. You will not see that with cell formulas if you have scrolled down to where the calculated values are no longer visible. Additionally, all calculations can be tied to one update button so its a one click operation.

    Maud
    Last edited by Maudibe; 2012-09-23 at 11:46.

  4. #34
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    661
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Maud, COGS stands for Cost of Good Sold. It's the normal accounting name used for what the parts cost that you're going to sell. Sorry, I'm one of those "bean counter" accounting guys.
    That would be really cool if you could do those updates with the popup warning and also the new tab thing.

  5. #35
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    661
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Maud, What about when/if the billing parts is zero, but the Cost of Goods Sold is $50? That returns an error.

  6. #36
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,213
    Thanks
    46
    Thanked 231 Times in 212 Posts
    kzkz,

    OK, modificatios made. If you decide to use it, read on.....
    1. A warning pop-up will appear to confirm that the user wants to clear the data (not formulas)
    2. An "Add New Form" button on the sheet. When the user clicks this button, they will be presented with a calendar pop-up to select the starting date. If they select anything other than a Monday, a message will direct them to try again. If they select a date on a Monday a new worksheet will be created and named with "Week of date" and the date will automatically be entered in the left upper corner of the sheet. The calendar pop-up is a MS control. It should have been installed with Office 2010 but if you do not have it, it will fail. Let me know as there are simple alternatives
    3. I have added additional blank buttons for future upgrades that don't do anything at this time
    4. If you print the worksheet, the buttons will not print
    5. Removed the name "PR" as this caused a problem when attempting to copy the worksheet. I could not find it used in any formulas

    Things not to do:
    1. Do not add additional rows or columns as it will desynchronize the row/column lookup for values
    2. Do not add names for ranges. This will interrupt he copying of the sheets to confirm the scope of the name extended to the new sheet
    3. Do not remove or rename the Template worksheet. This is where the new sheet will be copied from. You can remove or rename any other sheet.
    4. Do not password protect the sheet as this will lock cells that the code will attempt to change the values. If you wish to lock the sheet, give me a password that you will not change and I will write it into the code to unlock the sheet on activation to change values, then relock the sheet with the same password.

    Things you should do:
    1. Make a master backup of the sheet
    2. Email me via Windows Secrets profile that I give you my real email address if you have any questions.

    HTH,
    Maud
    Attached Files Attached Files

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    kzkz (2012-09-23)

  8. #37
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    661
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Fantastic! I love it. I'll PM you now with a few questions and a pw to protect the sheet. I like to protect documents that have formulas so that others don't over-write them.

  9. #38
    New Lounger
    Join Date
    Sep 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Whoa! Stop and think about this. Averages of percentages are NOT NOT NOT statistically valid. Not ever. (OK, with a good imagination, you can think of one--but it would be very unusual circumstances.)

  10. #39
    New Lounger
    Join Date
    Mar 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello, statistically what you are asking to do is meaningless. You can perform any arithmetic operation you choose. The question to be answered is do you want meaningful data?

  11. #40
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,213
    Thanks
    46
    Thanked 231 Times in 212 Posts
    Have you ever said to yourself, I am right on an average of 75% of the time? If you have 4 tasks with each having 4 decisions. If you were right in 50 % of the decisions in task 1, 75% right in the decisions for task 2, 100% for task 3, and 95% for task 4, why would it not be correct or meaningless to say that on average, you are 80% correct when it comes to doing tasks?

  12. #41
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Wiltshire, UK
    Posts
    526
    Thanks
    1
    Thanked 45 Times in 43 Posts
    That works because it is a special case where task has 4 decisions. In any other case (ie most of the time !) your calculation will give an erroneous result

    On the other hand if you have two investments:

    One is worth £10 and grows 20% whilst the other is worth £1000 and grows 10%.

    It would not be correct to say that on, average, your investments have grown by 15%. 10.1% would be nearer the mark.

    The correct weighted formula works for both your example and mine - your formula only works for your example.

  13. #42
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,213
    Thanks
    46
    Thanked 231 Times in 212 Posts
    What if you were looking at a 10 year span? On average, my investment grew 10% per year. Sounds like a meaningful measurement to me!
    If http://www.dinkytown.net/java/ProfitMargin.html use average percentages, then it is good enough for me.
    Last edited by Maudibe; 2012-09-28 at 18:08.

  14. #43
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    661
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I'm not looking for an answer to make huge financial decisions on, but just a good rough number that tells me that the profits for that week on parts and or labor has been in a certain range and then compare it to the next week and so on. When one job you make 40% profit then another 35%...I want to know just an average of the two which sounds pretty simple to me.

  15. #44
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Wiltshire, UK
    Posts
    526
    Thanks
    1
    Thanked 45 Times in 43 Posts
    kzkz,

    One job at 40% and the other at 35% . . . the average profit is 37.5% ONLY IF both job are worth the same amount. In any other case 37.5% is NOT the true average.

    It is indeed pretty simple to get the true average as all the data is in your spreadsheet. I suspect that doing so will be easier and less error-prone than the clearly difficult route you have been going down:

    1. For each transaction calculate the Profit
    2. Add up all the individual Profits to get the Total Profit
    3. You presumably have the selling price for each transaction, so add up the selling prices ie Total Sales
    4. Divide the Profit by the Total Sales
    5. Hey presto - that is the accurate average profit.

    The beauty of simple adding up is that it is immune to oddities such as blank entries and so on.
    Last edited by MartinM; 2012-09-28 at 09:01.

  16. The Following User Says Thank You to MartinM For This Useful Post:

    ruosChalet (2012-10-15)

  17. #45
    New Lounger
    Join Date
    Sep 2012
    Posts
    1
    Thanks
    0
    Thanked 1 Time in 1 Post
    kzkz,
    As tempting as a nice simple number can be, it is misleading. It is not statistically valid because you are calculating a statistic on non-homogeneous items (apples, oranges, ...).
    One assumption buried in your logic is that the amount of labor that should be expended is proportional and consistent to the cost of the parts used. I can assure you that the designers were not trying to standardize this value in a single item, let alone across different items. It also does not account for an experience on the part of the repair personnel. eg. 'I saw this problem before, I know what to check.'
    Further, the measurement does not truly indicate when you have a problem. You may say you can see when the percentage is different, but you can't see why and it will never indicate when you truly have a problem but the percentage is in 'normal' range.
    Further, a repair that involves labor without replacing parts is truly valid in many mechanisms.
    -- Without further detail, it is a really, really bad and invalid measurement. As a continuous improvement manager and consultant I can tell you this is one of those measurements that makes someone a hero one period and a goat that gets called on the carpet the next period; without them having any real control or appropriate action to 'correct' the number. It leads people and companies to enact stupid policies to try to fix the number to CYA when the number has no real life bearing on customer satisfaction or profitability.

  18. The Following User Says Thank You to Han For This Useful Post:

    ruosChalet (2012-10-15)

Page 3 of 3 FirstFirst 123

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •