Solved

Excel calculation help

Posted on 2011-03-11
24
264 Views
Last Modified: 2012-05-11
Hi All,

I would like some help with this excel document I have created if possible.  I have left comments in the attached spreadsheet on columns M, N and column AP.  I would like to calculate a percentage of scrap material generated from 2 of the columns, and also calculate how much a price would need to be to hit a certain percentage.

I though this would be best explained in the spreadsheet.  If somebody could please open this and let me know if they can help.  Please ask if further information is required.

Thanks in advance. Example.xls
0
Comment
Question by:choy77
  • 8
  • 5
  • 3
  • +3
24 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35109954
Spreadsheet missing.

Sid
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35110049
Hello Choy77, try this formula for M3

=L3-MID(O3,FIND("x",O3)+1,9)

then for N3

=1-M3/L3

format as %

and, I/m not entirely clera for AP, try this in AP3

=AO3*(1+AW3)

that gives you a % increase on AO3 as defined by AW3, is that right? see attached

regards, barry
26880549.xls
0
 

Author Comment

by:choy77
ID: 35110978
@SiddharthRout, i didnt upload initially but should be there now.

@barryhoudini, I will give this a go now, thanks for your quick response.
0
 

Author Comment

by:choy77
ID: 35111110
Hi barry,

Again thanks for your quick response,

For N3 It needs to show the percentage for M3, can I ask what the minus one is doing at the beginning of the formula in N3? As the figure 6.5 is the scrap from the figure 47 so wouldn't this be much smaller?

For AP3, I would like to show the price which would generate a 33% figure shown in column AW so we have a cost to work towards for the current selling price.  Could you explain the formula in M3 so I can understand as this would be very much appreciated.  Thanks again.
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 168 total points
ID: 35112194
OK, I see

=1-M3/L3 gives you the percentage used, so if you just want the percentage unused (scrap) then that should be just

=M3/L3

For the M3 formula I suggested this

=L3-MID(O3,FIND("x",O3)+1,9)

FIND function finds the "x" in O3 so the mid function effectively gives you everything after the "x", (i,e, the length) and then that's subtracted from the actual length to give the scrap figure

for AP3 I'm still not sure I understand what you require - does the formula I suggested give you the results you expect? If not can you give an example, manually calculated - what should AP3 be given the figures in your example, and how is that calculated?

regards, barry
0
 

Author Comment

by:choy77
ID: 35116144
Hi Barry,

Thanks for the explanation to the formulas.  the "Current selling price" in column AO is manually inputted, and then generates a percentage margin against the selling price in column AW.  I would like to some how if possible calculate what the current selling price would need to be (shown in column AP) in order to reach a 33% margin as this is a set target for the sales team.  Basically, if the user types in £20 in column AO and this shows as a 20% in column AW, I would like column AP to automatically show as £33 so the sales person would know that's would price he/she would need to ask for in order to meet the 33%.  I hope this makes sense but please let me know if not.
0
 

Author Comment

by:choy77
ID: 35146150
Hi Barry,

Did you manage to have a look at this?

Thanks
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35151977
Sorry for the delay....but I'm still a little confused. You talk about column AO being manual input but in your attached spreadsheet AO is a calculation, e.g. AO3 shows this formula

=Q3+V3+AF3

AO3 = 18.06 and AW3 is 33.04% so what should AP3 be for those values? I assumed that you want AO3 + 33% so you get that with the formula I suggested, i.e.

=AO3*(1+AW3)

which gives 24.03

Is that the value you would expect?

regards, barry

0
 

Author Comment

by:choy77
ID: 35154723
Hi Barry,

Sorry I have given a wrong bit of information there regarding the formula.

AO3 is already over the 33% mark shown in column AW.  I want AP3 to display the exact current selling price that would be required to generate a 33% margin against the selling price in column AW.  

I don't want AP3 to display AO3 + 33%, I just want AP3 to show a single price.  I hope this is abit clearer.

Thanks for your response.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35160367
Sorry, perhaps I'm being a little slow on the uptake here but you say

>AO3 is already over the 33% mark shown in column AW

How is that? AO3 is 18.06 so waht are you measuring that against to establish that it's already oevr 33%?

>I want AP3 to display the exact current selling price that would be required to generate a 33% margin against the selling price in column AW

AW doesn't have the selling price - did you mean to refer to another column?

In these situations it's often easier if you can specify the exact result you require, i.e. then actual figures that you want a formula to deliver in AP3:AP6. I'm struggling to understand your written explanation so if I see the actual figures I hope I can ascertain how you came about those (manually) and then I can suggest a formula that would produce those results (or somebody else can)

regards, barry
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:choy77
ID: 35179148
Hi Barry,

Sorry I don't think I have explained it very well.  

The value in column AO is calculated from the Material Price Quoted (Q3) + Quoted Machining Price (V3) + Treatments Price Quoted (AF3) and gives the result of £18.06 in cell AO which generates a percentage margin against selling price of 33.04% in AW3.

I don't know how this would be calculated, but I would like the value in AP3 to somehow display what selling price would be needed to hit a 33% or greater value in column AW.

I suppose something like this although it may look incorrect as I am not good with formulas:

For the formula in AP3 I would like it to do:

=sum(Q3+V3+AP3) / (sum calculation) to generate a 33% or greater result.

I dont know if this will help, I just want a price to be generated here to show what price would be required to hit a 33% figure in column AW?

Could you let me know if this helps?  I have attached a file to try and make it a little clearer.

Thanks.








 Example-2.xls
0
 

Author Comment

by:choy77
ID: 35191639
Hi Barry, could you please let me know whether this information is sufficient.
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 166 total points
ID: 35213470
For the 33%, I think the solution is quite simple (although I'll admit that it took me a while to see it).

 AS3*1.5

 If you want the margin % to be 33 % (basically 1/3), the price has to be 50 % more than the cost, so that the difference between them will be 1/3 of the price.  AS3*1.5 should give you a margin of 33.333... %.  If you want the % to be closer to 33.0, you can lower the 1.5 slightly, like to around 1.4925 or 1.4926.

 James
0
 
LVL 20

Assisted Solution

by:ElrondCT
ElrondCT earned 166 total points
ID: 35215100
Presumably you want to be able to vary the margin (right now it's 33%, but you might change it to 25%, or 50%), and assuming your definition of "margin" is, "I want x% of the selling price to be profit", the formula you need is:

=A1 / (1 - B1)

where A1 is the price of inputs, and B1 is the margin you want, expressed as x% or 0.xx, the result is your desired selling price.
0
 
LVL 34

Expert Comment

by:James0628
ID: 35221503
ElrondCT,

 I assume by "price of inputs" you meant the cost.  "Inputs" could mean different things (like values that are "input" into the spreadsheet), and you'd mentioned "selling price" right before that, so I just assumed that A1 was supposed to be the selling price at first, and couldn't see how that could work.  But if A1 is the cost, then yeah, that's like the calculation that I posted, but more flexible.

 James
0
 

Author Comment

by:choy77
ID: 35239378
Thanks very much for your help everybody, it is much appreciated.  I am just going to have a play around with the spreadsheet today I think.

0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35711899
There were 3 questions in all

I attempted to answer all 3 and I believe I answered question 1 to the Asker's satisfaction in my first answer...and then, after clarification, I gave a working answer for question 2 in my second reply, I'm not clear whether the 3rd question was answered at all - the Asker might have to clarify. If no response I suggest equal split between my first 2 posts and also the first posts of both ElrondCT and James0628

regards, barry
0
 
LVL 34

Expert Comment

by:James0628
ID: 35727234
FWIW, I basically agree with everything barryhoudini said.  He seems to have answered part of it and I tried to answer another part.  ElrondCT's post was after mine, but has the benefit of being more flexible, which could be useful.

 James
0
 
LVL 20

Expert Comment

by:ElrondCT
ID: 35729161
I concur.
0
 
LVL 24

Expert Comment

by:broomee9
ID: 35783894
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

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

18 Experts available now in Live!

Get 1:1 Help Now