# Confusing pricing problem for MS Excel automation.

Show 40 post(s) from this thread on one page
Page 1 of 2 12 Last
• January 10th 2009, 12:36 AM
Confusing pricing problem for MS Excel automation.
Hoping a budding mathematician can help with a problem I’ve got. I’ve been playing around with this one in MS Excel and I’m afraid it has me beat.

Here’s what I’m trying to do
I start with a Sell Price from which there are fixed costs that I know will be incurred.

Sell Price 5000

Cost1 547.50 (Generated from sell price)

Cost2 800 (Generated from sell price)

Tax 3% on buy price ?? Will equal 92.19

Cost Total 1347.50 plus 3% tax on buy price(1439.69)

Sell less known costs 3652.5 should include the 3% tax on buy price(3560.31)

Profit Margin (10% on Sell less known costs) ?? Will equal 454.63

Buy Price ?? Will equal 3073.00

Essentially I’m trying to work out how to construct an Excel formula to calculate all this for me automatically from the information I know starting at a sell price and working back.
Is this possible anybody?? 
• January 10th 2009, 02:56 AM
janvdl
Quote:

Hoping a budding mathematician can help with a problem I’ve got. I’ve been playing around with this one in MS Excel and I’m afraid it has me beat.

Here’s what I’m trying to do
I start with a Sell Price from which there are fixed costs that I know will be incurred.

Sell Price 5000

Cost1 547.50 (Generated from sell price)

Cost2 800 (Generated from sell price)

Tax 3% on buy price ?? Will equal 92.19

Cost Total 1347.50 plus 3% tax on buy price(1439.69)

Sell less known costs 3652.5 should include the 3% tax on buy price(3560.31)

Profit Margin (10% on Sell less known costs) ?? Will equal 454.63

Buy Price ?? Will equal 3073.00

Essentially I’m trying to work out how to construct an Excel formula to calculate all this for me automatically from the information I know starting at a sell price and working back.
Is this possible anybody?? 

How do you generate the costs from the sell price? What's the percentage?
• January 10th 2009, 03:03 AM
janvdl,

They are actually just dollar values that come off a table.
For the purpose of the problem you could just assume it a fixed amount.
• January 10th 2009, 03:04 AM
janvdl
Quote:

janvdl,

They are actually just dollar values that come off a table.
For the purpose of the problem you could just assume it a fixed amount.

This should be quick. Check back in 15-20 minutes. ;)
• January 10th 2009, 03:25 AM
janvdl

And I assume the buy price = Selling price - (Known Costs + Tax + Profit)

See the attachment, and if it works correctly.
• January 10th 2009, 03:39 AM
janvdl

Thank you for that. I tried the spreadsheet and the tax in this case is not fixed but will be based on the buy price. I thought I could just use reciprocal calculations to find the buy price but doesn't work.
• January 10th 2009, 03:43 AM
janvdl
Quote:

janvdl

Thank you for that. I tried the spreadsheet and the tax in this case is not fixed but will be based on the buy price. I thought I could just use reciprocal calculations to find the buy price but doesn't work.

Yes you should be able to.

Since in this case the tax is $\frac{3}{100} \times$ Buy Price ; then the Buy price should be: $\frac{100}{3} \times$ Tax.

If the tax rate was 4 percent, it would just be $\frac{4}{100}$ and $\frac{100}{4}$
• January 10th 2009, 03:53 AM
janvdl

It's great trying knock this one off. hehe
The problem here is that I'm attempting to generate the buy price in order to calculate the tax and hence the profit margin.
The three elements that won't calculate so far are the tax, buy price and the profit margin.
Is it possible given the information we have?
• January 10th 2009, 03:56 AM
janvdl
Quote:

janvdl

It's great trying knock this one off. hehe
The problem here is that I'm attempting to generate the buy price in order to calculate the tax and hence the profit margin.
The three elements that won't calculate so far are the tax, buy price and the profit margin.
Is it possible given the information we have?

Please give me the exact question as it is on the paper. With only the information you were given, no information that you calculated yourself.
• January 10th 2009, 04:02 AM
Known Information.

Sell Price 5000

Cost1 547.50 (Generated from sell price)

Cost2 800 (Generated from sell price)

Information to be calculated.

Profit Margin (10% on Sell less known costs including the 3% tax)

Tax 3% on buy price that will be recorded as a cost

• January 10th 2009, 04:11 AM
janvdl
Quote:

Known Information.

Sell Price 5000

Cost1 547.50 (Generated from sell price)

Cost2 800 (Generated from sell price)

Information to be calculated.

Profit Margin (10% on Sell less known costs including the 3% tax)

Tax 3% on buy price that will be recorded as a cost

The known costs are 1347,50

So we can construct the following formula from the info given:

$Profit = \frac{10}{100} \left( 5000 - \left( 1347,50 + \frac{3}{100} \left( Buy \ Price \right) \right) \right)$

I can only simplify it to this:

$Profit = 365,25 - \frac{3}{1000} \left( Buy \ Price \right)$

I could be missing something, but otherwise we'll need more info to calculate this one...
• January 10th 2009, 04:22 AM
janvdl
Otherwise they could mean that the tax is in fact included with the profit margin, and not subtracted from it.

So we can say:

$Profit \ Margin = \frac{10}{100} \left( 5000 - 1347,50 \right) = 365,25$

We at least have the profit margin that way...
• January 10th 2009, 04:22 AM
janvdl

Thanks for helping my friend. (Hi)
I've tried nutting it out and felt another piece of information might have been required.
I'll keep playing around in Excel and will post back if I happen to get it.
• January 10th 2009, 04:23 AM
janvdl
Quote:

janvdl

Thanks for helping my friend. (Hi)
I've tried nutting it out and felt another piece of information might have been required.
I'll keep playing around in Excel and will post back if I happen to get it.

Alright. I usually miss simple little things, so just keep an eye open.

I'm curious, is this a homework problem, or something you are trying to calculate out of interest?
• January 10th 2009, 04:33 AM