Thread: 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?? 

Originally Posted by intglad

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?

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.

Originally Posted by intglad

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.

Please check your original profit margin, you made a mistake there. It should be 356.03 instead of 454.63

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

See the attachment, and if it works correctly.

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.

Originally Posted by intglad

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 Buy Price ; then the Buy price should be: Tax.

If the tax rate was 4 percent, it would just be and

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?

Originally Posted by intglad

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.

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

Buy Price

Originally Posted by intglad

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

Buy Price

The known costs are 1347,50

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



I can only simplify it to this:



I could be missing something, but otherwise we'll need more info to calculate this one...

Otherwise they could mean that the tax is in fact included with the profit margin, and not subtracted from it.

So we can say:



We at least have the profit margin that way...

janvdl

Thanks for helping my friend.
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.

Originally Posted by intglad

janvdl

Thanks for helping my friend.
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?

It's a legitimate business calculation.
I've been working this stuff semi manually in Excel but always figured it could be completely calculated.
It would be awesome to figure it out if it is possible because if you have a product that know how much you could sell it for, the calculation to establish a buy price would be a very handy tool indeed.