Thread: 'Problem calculating retail costs and discounts'

Hi there
I've been trying to create a spreadsheet for a holiday shop and I've gotten nowhere in 2 hours.

I need these columns and will have almost 200 items

item, qty, total cost, cost each, 40% disc cost each, price, count, sold, profit

I know the Qty and the total cost and will input the price we charge and then count after the sale is over. I would like the cost before and after the 40% disc and then figure out a formula for the amount sold and the profit.
the problem was I was trying to calculate the cost for each widget using QUOTIENT but it was giving me $3.00. My calculator gives me $3.35.

I am going to go back and look at the autosum for the sold column but I can't seem to be able to figure out cost per piece much less the disc or profit

I would appreciate any help with this.
thanks
g

Hi Ugh, welcome to the forum.

I would suggest uploading a copy of your workbook with sample data (enough to represent what you're actually working with). You can use fake data/names as needed.

In the sample workbook, show where you would like the results and manually type in what those results should be and WHY (using words if it's not blatantly obvious to a non-retailer).

This will help you get more timely and accurate responses.

I'd also recomment you change your thread title to something like 'Problem calculating retail costs and discounts'. You can do so by clicking the Edit button in your post and then clicking Go Advanced. You'll then be able to change the title.

See attached.

To upload attachments, click the 'Go Advanced' button instead of 'Post Quick Reply'. Scroll down and you'll see 'Manage Attachments'. You can click there and then browse your computer and upload a file (be sure to click the Upload button) then close the upload form. When you submit the post, it will include the attachment.

So you manually input the Quantity (15), Cost (50.25), Price (4.00) and Count (3). To get the other values:

Cost Each: = Cost / Quantity
40% Disc: = Cost Each * 0.60
Sold: = Quantity - Count
Profit: = ( Sold * Price ) - ( Sold * 40% Disc )

Hope that helps!

By default, numbers shouldn't round themselves in Excel. In a brand new workbook, if you type

=50.25/15

Does it show 3 or 3.35?

In your current workbook, if you right-click on the cell showing 3, and choose Format Cell, is there a custom number format showing? Or is it standard Number format with two decimal places?

If your column width is too narrow, it could also "round" the numbers - although it's only showing you the rounded value. If you perform math on that cell it will use the actual stored number (3.35).

using quotienent doesnt divide it returns the whole part of 50.xx / 15, never a decimal you need to use /

here is your file fixed