Solved

Compare percentages and take action??

Posted on 2004-03-29
10
306 Views
Last Modified: 2013-12-25
I am creating a calculator to test gross margin percents.  The calculator calculates and displays the percentages just fine.  Now I am trying to add an image to designate wither a margin is approved or not.  The images are only working some of the time and other times they are not working like I would expect.  

Simply put - This is what I'm after:
Margin >=20% - Smiley Pic
Margin <20% AND >10% Neutral Smiley Pic
Margin <=10% Frowning Pic

Seems to work fine when things are above 20%, but in the middle or below 10% it's hit or miss.  I think it may have something to do with rounding or else I am not comparing the decimal/percents correctly.

Here is part of my code:

'Formulas
        decTotalNet = (decSell * intQty)
        decTotalGMD = (intQty * decSell) - (intQty * decCost)
        decTotalCost = (decCost * intQty)
        'Gross Margin Percent Formula = (Selling Price - Total Cost) ÷ Selling Price
        Try
            decTotalGMP = (decTotalNet - decTotalCost) / decTotalNet
        Catch
            MessageBox.Show("The sell price and sell quantity values must both be greater than zero.  Please try again.", "Invalid Entry", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try

        'Calculate and display the extended amounts
        lblExtendedSell.Visible = True
        lblExtendedSellValue.Text = FormatCurrency(decSell * intQty, 2)
        lblExtendedCost.Visible = True
        lblExtendedCostValue.Text = FormatCurrency(decCost * intQty, 2)
        lblTotalGMD.Visible = True
        lblTotalGMDValue.Text = FormatCurrency(decTotalGMD, 2)
        lblTotalGMP.Visible = True
        lblTotalGMPValue.Text = FormatPercent(decTotalGMP, 2)

        'Calculate the margins at the various % increase & decrease levels
         'Here I go through calculations at 3,5,7,10,12, and 15 %.  I didn't include them all as they are basically the same.

        'Markup Percent formula is Selling Price = Total Cost x (1 + Mark-Up Percent)
        'Example: if you want a mark-up of 27%
        'Would be Selling Price = $2.00 x (1 + 0.27)

        '3% Increase
        Dim dec3Sell As Decimal
        Dim dec3GMD As Decimal
        Dim dec3GMP As Decimal

        dec3Sell = decTotalNet * (1 + 0.03)
        dec3GMD = dec3Sell - decTotalCost
        dec3GMP = (dec3Sell - decTotalCost) / dec3Sell
        lbl3GMP.Text = FormatPercent(dec3GMP, 2)
        lbl3GMD.Text = FormatCurrency(dec3GMD, 2)
        lbl3Sell.Text = FormatCurrency(dec3Sell, 2)
        'Test for good margins and display symbol
        If dec3GMP >= 0.2 Then
            pic3.Image = picSmile.Image
            pic3.Visible = True
        ElseIf dec3GMP <= 0.1 Then
            pic3.Image = picFrown.Image
            pic3.Visible = True
        ElseIf dec3GMP < 0.2 And dec3GMP > 0.1 Then
            pic3.Image = picNtrl.Image
            pic3.Visible = True
        Else
            pic3.Visible = False
        End If

        '5% Increase
        Dim dec5Sell As Decimal
        Dim dec5GMD As Decimal
        Dim dec5GMP As Decimal
        dec5Sell = decTotalNet * (1 + 0.05)
        dec5GMD = dec5Sell - decTotalCost
        dec5GMP = (dec5Sell - decTotalCost) / dec5Sell
        lbl5GMP.Text = FormatPercent(dec5GMP, 2)
        lbl5GMD.Text = FormatCurrency(dec5GMD, 2)
        lbl5Sell.Text = FormatCurrency(dec5Sell, 2)
        'Test for good margins and display symbol
        If dec5GMP >= 0.2 Then
            pic5.Image = picSmile.Image
            pic5.Visible = True
        ElseIf dec5GMP <= 0.1 Then
            pic5.Image = picFrown.Image
            pic5.Visible = True
        ElseIf dec5GMP < 0.2 And dec5GMP > 0.1 Then
            pic5.Image = picNtrl.Image
            pic5.Visible = True
        Else
            pic5.Visible = False
        End If

------
Sample of the weird output:

I enter $25 as the sell, $23 as the cost, with a qty of 1.  I get:

3% increase = GM 10.68% - No Pic (should be neutral)
5% increase = GM 12.38% - Neutral Pic (why did it work here??)
7% increase = GM 14.02% - No Pic (should be neutral)

Help!  What am I missing?  Thanks in advance.
0
Comment
Question by:EPA
  • 5
  • 5
10 Comments
 
LVL 27

Expert Comment

by:Dabas
ID: 10709780
Hi EPA,
I suggest you replace your If statements to a Select Statement

Select Case decNGMP
Case Is >= 0.2
      picN.Image = picSimile.Image
Case IS <= 0.1
      picN.Image = picSmile.Frown
Case Else 'No need to complicate. If it is not one of the others, then go here
      pinN.Image = picNtrl.Image
End Select

Dabas
0
 

Author Comment

by:EPA
ID: 10709849
Dabas,

Thanks for the suggestion.  I tried this like you said:

Select Case dec3GMP
            Case Is >= 0.2
                pic3.Image = picSmile.Image
            Case Is <= 0.1
                pic3.Image = picFrown.Image
            Case Else 'No need to complicate. If it is not one of the others, then go here
                pic3.Image = picNtrl.Image
        End Select

I only tested it for the 3% markup and it doesn't work.  The 3% comes out at 10.68% which should trigger a picNtrl.Image, but it's blank.  I don't understand what is going wrong.  Even if it didn't understand the other two ot should at least take the value of the Case Else right?
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10709920
EPA,
Do you have a Try Catch End Try statement that you did not include in your code above for the whole procedure?
Seems like an error is triggering which does not allow the code to be reached.

Have you tried setting a breakpoint and single stepping through the code?

Dabas
0
 

Author Comment

by:EPA
ID: 10710017
Dabas,

Here is the code from start to 3% (with your select case suggestion), the rest is the same with it just incrementing fro 3 to 5 to 7, etc

  'Declare the variables
        Dim decSell As Decimal
        Dim decCost As Decimal
        Dim intQty As Integer
        Dim decTotalGMD As Decimal
        Dim decTotalNet As Decimal
        Dim decTotalCost As Decimal
        Dim decTotalGMP As Decimal

        'Test the input box to make sure the user has eneter valid values
        Try
            decSell = CDec(txtSell.Text)
        Catch 'Display a message is valid values have not been entered in the text boxes
            MessageBox.Show("You have entered an invalid sell amount.  Please try again.", "Invalid Data Entry", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtSell.Focus()
            Exit Sub 'Stop code execution if error is found
        End Try

        Try
            decCost = CDec(txtCost.Text)
        Catch
            MessageBox.Show("You have entered an invalid cost amount.  Please try again.", "Invalid Data Entry", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtCost.Focus()
            Exit Sub
        End Try

        Try
            intQty = CInt(txtQty.Text)
        Catch
            MessageBox.Show("You have entered an invalid quantity.  Please try again.", "Invalid Data Entry", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtQty.Focus()
            Exit Sub
        End Try

        'Formulas
        decTotalNet = (decSell * intQty)
        decTotalGMD = (intQty * decSell) - (intQty * decCost)
        decTotalCost = (decCost * intQty)
        'Gross Margin Percent = (Selling Price - Total Cost) ÷ Selling Price
        Try
            decTotalGMP = (decTotalNet - decTotalCost) / decTotalNet
        Catch
            MessageBox.Show("The sell price and sell quantity values must both be greater than zero.  Please try again.", "Invalid Entry", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try

        'Calculate and display the extended amounts
        lblExtendedSell.Visible = True
        lblExtendedSellValue.Text = FormatCurrency(decSell * intQty, 2)
        lblExtendedCost.Visible = True
        lblExtendedCostValue.Text = FormatCurrency(decCost * intQty, 2)
        lblTotalGMD.Visible = True
        lblTotalGMDValue.Text = FormatCurrency(decTotalGMD, 2)
        lblTotalGMP.Visible = True
        lblTotalGMPValue.Text = FormatPercent(decTotalGMP, 2)

        'Calculate the margins at the various % increase & decrease levels

        'Markup Percent formula is Selling Price = Total Cost x (1 + Mark-Up Percent)
        'Example: if you want a mark-up of 27%
        'Would be Selling Price = $2.00 x (1 + 0.27)

        '3% Increase
        Dim dec3Sell As Decimal
        Dim dec3GMD As Decimal
        Dim dec3GMP As Decimal

        dec3Sell = decTotalNet * (1 + 0.03)
        dec3GMD = dec3Sell - decTotalCost
        dec3GMP = (dec3Sell - decTotalCost) / dec3Sell
        lbl3GMP.Text = FormatPercent(dec3GMP, 2)
        lbl3GMD.Text = FormatCurrency(dec3GMD, 2)
        lbl3Sell.Text = FormatCurrency(dec3Sell, 2)
        'Test for good margins and display symbol

        Select Case dec3GMP
            Case Is >= 0.2
                pic3.Image = picSmile.Image
            Case Is <= 0.1
                pic3.Image = picFrown.Image
            Case Else 'No need to complicate. If it is not one of the others, then go here
                pic3.Image = picNtrl.Image
        End Select

----

What's weird is how it works sometimes and not others.  The 5% seemed to work just find, but not the 3%.  I did set a breakpoint at the 3% mark and walked thru it.  The values look like they make it all the way through and it looks like the program is following the right path on my if statements, but the end result is incorrect.

Appreciate the help.  EPA
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10710080
EPA,
Just tried the following on my system. (Replaced setting of images with console Writelines)

Code:

    Const decTotalNet As Decimal = 25
    Const decTotalCost As Decimal = 23
    Private Sub Button5_Click_2(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        SetImage(0.03, PictureBox1)
        SetImage(0.05, PictureBox1)
        SetImage(0.07, PictureBox1)
    End Sub
    Private Sub SetImage(ByVal Percent As Single, ByVal Pic As PictureBox)
        Dim decSell As Decimal
        Dim decGMD As Decimal
        Dim decGMP As Decimal
        decSell = decTotalNet * (1.0 + Percent)
        decGMD = decSell - decTotalCost
        decGMP = (decSell - decTotalCost) / decSell
        Console.WriteLine("decGMP = " & decGMP.ToString & " Percent = " & Format(Percent, "Percent"))
        If decGMP >= 0.2 Then
            Console.WriteLine("Smile")
            Pic.Visible = True
        ElseIf decGMP <= 0.1 Then
            Console.WriteLine("Frown")
            Pic.Visible = True
        ElseIf decGMP < 0.2 And decGMP > 0.1 Then
            Console.WriteLine("Neutral")
            Pic.Visible = True
        Else
            Pic.Visible = False
        End If
    End Sub

Result:
decGMP = 0.1067961159233595622569326225 Percent = 3.00%
Neutral
decGMP = 0.1238095244312485437765254143 Percent = 5.00%
Neutral
decGMP = 0.1401869161273317144935215824 Percent = 7.00%
Neutral

Dabas
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 27

Expert Comment

by:Dabas
ID: 10710141
EPA,
>                 pic3.Image = picFrown.Image

My guess is that the above line causes an exception.

Dabas
0
 

Author Comment

by:EPA
ID: 10710321
Interesting...

So with your results we go all neutrals which is correct

Result:
decGMP = 0.1067961159233595622569326225 Percent = 3.00%
decGMP = 0.1238095244312485437765254143 Percent = 5.00%
decGMP = 0.1401869161273317144935215824 Percent = 7.00%

Why would pic3.Image = picFrown.Image cause an error, but not pic3.Image = picSmile.Image ?  I double checked all the the image names and the pic boxes.  I can't see any spelling errors.

In mine neither 3% or 7% display a pic, but 5% does correctly display the neutral pic

Here is the code for 5 & 7.  I can't see anything different can you?

  '5% Increase
        Dim dec5Sell As Decimal
        Dim dec5GMD As Decimal
        Dim dec5GMP As Decimal
        dec5Sell = decTotalNet * (1 + 0.05)
        dec5GMD = dec5Sell - decTotalCost
        dec5GMP = (dec5Sell - decTotalCost) / dec5Sell
        lbl5GMP.Text = FormatPercent(dec5GMP, 2)
        lbl5GMD.Text = FormatCurrency(dec5GMD, 2)
        lbl5Sell.Text = FormatCurrency(dec5Sell, 2)
        'Test for good margins and display symbol
        If dec5GMP >= 0.2 Then
            pic5.Image = picSmile.Image
            pic5.Visible = True
        ElseIf dec5GMP <= 0.1 Then
            pic5.Image = picFrown.Image
            pic5.Visible = True
        ElseIf dec5GMP < 0.2 And dec5GMP > 0.1 Then
            pic5.Image = picNtrl.Image
            pic5.Visible = True
        Else
            pic5.Visible = False
        End If

        '7% Increase
        Dim dec7Sell As Decimal
        Dim dec7GMD As Decimal
        Dim dec7GMP As Decimal
        dec7Sell = decTotalNet * (1 + 0.07)
        dec7GMD = dec7Sell - decTotalCost
        dec7GMP = (dec7Sell - decTotalCost) / dec7Sell
        lbl7GMP.Text = FormatPercent(dec7GMP, 2)
        lbl7GMD.Text = FormatCurrency(dec7GMD, 2)
        lbl7Sell.Text = FormatCurrency(dec7Sell, 2)
        'Test for good margins and display symbol
        If dec7GMP >= 0.2 Then
            pic7.Image = picSmile.Image
            pic7.Visible = True
        ElseIf dec3GMP <= 0.1 Then
            pic7.Image = picFrown.Image
            pic7.Visible = True
        Else
            pic7.Visible = False
        End If
0
 

Author Comment

by:EPA
ID: 10710358
Okay I found one issue with the 7%... should have been:  ElseIf dec7GMP <= 0.1 Then
0
 
LVL 27

Accepted Solution

by:
Dabas earned 125 total points
ID: 10710387
EPA,
I suggest you rewrite using my subroutine.
That way you know you will not make an error like that.
Also check pic5 in design time. Are you able to display the Neutral image on it?

Also add Option Explicit On to the top of your module.

Have you tried single stepping as suggested earlier?

Dabas
0
 

Author Comment

by:EPA
ID: 10710476
Dabas,

Thanks for the help.  I added the Option Explicit like you suggested and single stepped through the code I believe I have found the errors.  I was going to rewrite using your subroutine, but I've got mine going now.  The cod for 7% worked great so after tweaking it I just used it for the rest switching out the #'s where needed.  

I appreciate all you efforts.  Points coming now.  EPA
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now