scenario/what if ??

I have no idea how to accomplish tis but I am sure that it is possible in 
Excel.

I have data that tells me for each day of the week for thre months how many 
visitors I get to the area. I have 5 staff and I also know how much time they 
are utilised for the day. ie they are kept busy say 20% of the time as a 
group for the visitor load on a Monday.

Can I use tis info in a spread sheet to do 'what if' such that if I get an 
increase in visitors what utilisation I will get from the 5 staff - 
conversely if I have 3 staff with the same visitors what utilisation do I now 
get from the staff?

Your Assistance appreciated.

Mike 
0
MikeROz (103)
11/23/2006 1:37:01 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
521 Views

Similar Articles

[PageSpeed] 6

Use the current data that you have for that three month period to come up 
with a value that is kind of a visitor to staff ratio.  

Lets say you know that with 5 staff and 40 visitors you have the 20% busy 
time.
With 5 staff and 60 visitors it goes up to 30%
with 5 staff and 100 visitors it goes up to 50%

Now I cheated with the numbers to make them come up even, but it turns out 
that if you reduce those numbers by lowest common denominator, you get
1 staff, 8 visitors = 20%
1 staff, 12 visitors = 30%
1 staff, 20 visitors = 50%
20% / 8 = 2.5%
30% / 12 = 2.5%
and, oddly enough,
50% / 20 = 2.5%
Your percentages will no doubt vary some, but once you get it down to those 
kinds of numbers, just average the percentages (sum them all, divide by total 
count of them used to get the sum).  That will tell you how much time each 
visitor 'costs' you in terms of a staff member's time.

Then you can set up some simple what-if tests in Excel.  You could set up a 
cell with the percentage of time that one visitor costs (2.5%), another cell 
to put in the number of visitors you estimate will come in on a given day and 
have a 3rd cell multiply the two to tell you how much of 1 staff member's 
time it will take to deal with them.  Lets say you think you'll have 100 
visitors:
2.5%  100 visitors
2.5 * 100 = 250%
Since you know there is no such thing as 250% of 1 person's time, what that 
actually tells you is that you need 2 1/2 staff members working at full speed 
to keep up with the 100 visitors.  Hard to hire half a person these days 
(although it seems easy enough to hire one person and only get half-a-day's 
work out of them <g>), so you would want at least 3 people on hand for a day 
you expected 100 visitors, and you'd probably want to expect to hear 
complaints from the staff about being overloaded, because the workload is 
probably not going to be evenly distributed.

In Excel terms, for the first what-if, you could set it up like this:
put .025 in A1 (an unchanging constant value, 2.5% = .025 )
put the estimated number of visitors into B1 (change as you desire)
in C1 put this equation:  =A1 * B1
You could even put this into D1 to get number of whole bodies you need:
=ROUNDUP(C1,0)

You can work the formula backwards, so to speak, to say "if I have this many 
staff members, how many visitors can I serve?"
4 staff members = 400% time available
400% / 2.5% = 160 visitors

Again, in Excel terms:
put the percentage, .025, into A2
Put the number of staff you will have on hand (4) into B2,
in C2 put this formula:  =B2/A2
again, this may not come out as a whole number, so you may want to put this 
formula into D2:
=ROUNDDOWN(C2,0)
which will tell you the absolute maximum number of visitors that a staff of 
whatever number you put into B2 can handle in one day.

Hope this helps you some.



"MikeR-Oz" wrote:

> I have no idea how to accomplish tis but I am sure that it is possible in 
> Excel.
> 
> I have data that tells me for each day of the week for thre months how many 
> visitors I get to the area. I have 5 staff and I also know how much time they 
> are utilised for the day. ie they are kept busy say 20% of the time as a 
> group for the visitor load on a Monday.
> 
> Can I use tis info in a spread sheet to do 'what if' such that if I get an 
> increase in visitors what utilisation I will get from the 5 staff - 
> conversely if I have 3 staff with the same visitors what utilisation do I now 
> get from the staff?
> 
> Your Assistance appreciated.
> 
> Mike 
0
Utf
11/24/2006 12:14:01 AM
Thanks for that excellent explanation. I will now try to apply.

Cheers
Mike

"JLatham" wrote:

> Use the current data that you have for that three month period to come up 
> with a value that is kind of a visitor to staff ratio.  
> 
> Lets say you know that with 5 staff and 40 visitors you have the 20% busy 
> time.
> With 5 staff and 60 visitors it goes up to 30%
> with 5 staff and 100 visitors it goes up to 50%
> 
> Now I cheated with the numbers to make them come up even, but it turns out 
> that if you reduce those numbers by lowest common denominator, you get
> 1 staff, 8 visitors = 20%
> 1 staff, 12 visitors = 30%
> 1 staff, 20 visitors = 50%
> 20% / 8 = 2.5%
> 30% / 12 = 2.5%
> and, oddly enough,
> 50% / 20 = 2.5%
> Your percentages will no doubt vary some, but once you get it down to those 
> kinds of numbers, just average the percentages (sum them all, divide by total 
> count of them used to get the sum).  That will tell you how much time each 
> visitor 'costs' you in terms of a staff member's time.
> 
> Then you can set up some simple what-if tests in Excel.  You could set up a 
> cell with the percentage of time that one visitor costs (2.5%), another cell 
> to put in the number of visitors you estimate will come in on a given day and 
> have a 3rd cell multiply the two to tell you how much of 1 staff member's 
> time it will take to deal with them.  Lets say you think you'll have 100 
> visitors:
> 2.5%  100 visitors
> 2.5 * 100 = 250%
> Since you know there is no such thing as 250% of 1 person's time, what that 
> actually tells you is that you need 2 1/2 staff members working at full speed 
> to keep up with the 100 visitors.  Hard to hire half a person these days 
> (although it seems easy enough to hire one person and only get half-a-day's 
> work out of them <g>), so you would want at least 3 people on hand for a day 
> you expected 100 visitors, and you'd probably want to expect to hear 
> complaints from the staff about being overloaded, because the workload is 
> probably not going to be evenly distributed.
> 
> In Excel terms, for the first what-if, you could set it up like this:
> put .025 in A1 (an unchanging constant value, 2.5% = .025 )
> put the estimated number of visitors into B1 (change as you desire)
> in C1 put this equation:  =A1 * B1
> You could even put this into D1 to get number of whole bodies you need:
> =ROUNDUP(C1,0)
> 
> You can work the formula backwards, so to speak, to say "if I have this many 
> staff members, how many visitors can I serve?"
> 4 staff members = 400% time available
> 400% / 2.5% = 160 visitors
> 
> Again, in Excel terms:
> put the percentage, .025, into A2
> Put the number of staff you will have on hand (4) into B2,
> in C2 put this formula:  =B2/A2
> again, this may not come out as a whole number, so you may want to put this 
> formula into D2:
> =ROUNDDOWN(C2,0)
> which will tell you the absolute maximum number of visitors that a staff of 
> whatever number you put into B2 can handle in one day.
> 
> Hope this helps you some.
> 
> 
> 
> "MikeR-Oz" wrote:
> 
> > I have no idea how to accomplish tis but I am sure that it is possible in 
> > Excel.
> > 
> > I have data that tells me for each day of the week for thre months how many 
> > visitors I get to the area. I have 5 staff and I also know how much time they 
> > are utilised for the day. ie they are kept busy say 20% of the time as a 
> > group for the visitor load on a Monday.
> > 
> > Can I use tis info in a spread sheet to do 'what if' such that if I get an 
> > increase in visitors what utilisation I will get from the 5 staff - 
> > conversely if I have 3 staff with the same visitors what utilisation do I now 
> > get from the staff?
> > 
> > Your Assistance appreciated.
> > 
> > Mike 
0
MikeROz (103)
11/24/2006 11:33:02 AM
Reply:

Similar Artilces:

Excel Scenario Manager should let me re-order scenarios (in the s.
Within the scenario manger dilog box (excel 2002) I have several scenarios. Within the scenario manger dialog bos I want to re-order the scenarios I created. ...

Worst case scenario with a cluster
Hi, We are a shop that tends to look after a large amount of smaller scaled (~500 user) exchange servers. We perform backups with a variety of methods, and have always had a worst case scenario plan. No matter what has happened with hardware, we've always known we could drop a PC on its side and use last night's backup to turn it into a temporary "server" (ableit, a very slow one) while the real hardware is repaired. I have asked this question in other forums so I need to make this statement first. Yes, the above IS possible, even though hardware is totally different. We ...

scenario/what if ??
I have no idea how to accomplish tis but I am sure that it is possible in Excel. I have data that tells me for each day of the week for thre months how many visitors I get to the area. I have 5 staff and I also know how much time they are utilised for the day. ie they are kept busy say 20% of the time as a group for the visitor load on a Monday. Can I use tis info in a spread sheet to do 'what if' such that if I get an increase in visitors what utilisation I will get from the 5 staff - conversely if I have 3 staff with the same visitors what utilisation do I now get from the s...

Cell Names in Scenario Summaries
Does anyone know a way to get a Scenario Summary to automatically show Names for cells (assuming they exist) rather than the cell references? For example, a Scenario Summary will show: $A$1 500 $B$1 1000 when those cells might be named "Dogs" and "Cats". I can always copy and paste afterwards, but that's not very elegant. TIA The Scenario Summary should automatically use the cell names, instead of the cell references. Check the Names List (Insert>Name>Define to see if the names exist. Bufo Calvin wrote: > Does anyone know a way to get a Scenario...

COUNTIFS
Hi: I have tried many variations of different nested formulas and have had no luck solving this one. Here are the two scenarios that I am trying to accomplish: Scenario 1: Count 1 if the following criteria are met on a single line of data: Col F contains text (is not blank) Col G, H, and I do NOT contain "Dropped" Scenario 2: Count 1 if the following criteria are met on a single line of data: Col F contains text (is not blank) Col G, H, I all contain the word "Approved" or "N/A" - the tricky part is that there are several different variations ...

Scenarios
What can you do with Scenarios? What are they used for. I recently created a workbook that uses scenarios to store information about a range of product models. The user selects a model from a dropdown list, and a macro shows the scenario for that model, with all the parts that are required to build it. There's a simplified example here: http://www.contextures.com/excelfiles.html Under Data Validation, look for Model Pricing Scenario. Diane M. McDonald wrote: > What can you do with Scenarios? What are they used for. -- Debra Dalgleish Excel FAQ, Tips & Book List http...

Scenarios Tutorials
Hi everybody, I am new to exceltip forum. I need help on usage of Scenarios in excel. I have tried to find out Tutorials with example for usingh Scenarios but was unable to dig out anything like i found for Pivot tables (i.e. Pivot table 101 etc.) if any body knows nay link where i can have it for free, plz. tell me. I would be greatful to u. thanx in advance. Vikesh --- Message posted from http://www.ExcelForum.com/ There's a Scenario sample workbook on my web site: http://www.contextures.com/excelfiles.html Under the heading 'Scenarios', look for 'Model Pricing S...

Scenarios
Hello, I want to know if it's possible to create charts from the scenario summary report. This summary will be created interactively and I want (somehow) to be able to automatically have a comparison chart too. Thanks for your response! Teresa. ...

How do I find the currently selected Scenario?
Hi, Maybe this is a simple thing, but I don't seem to be able to track it down. I have an Excel sheet with a couple of Scenarios defined. I am trying to implement some VBA functions where I need to know which Scenario is currently selected. Does anyone know how I determine the active Scenario from VBA? Any help will be greatly appreciated. Thanks in advance. Best regards. hi, (?) > Maybe this is a simple thing, but I don't seem to be able to track it down. > I have an Excel sheet with a couple of Scenarios defined. > I am trying to implement some VBA functi...

Scenarios in Excel
My first love was Lotus 1-2-3, but then I discovered Excel. However, if there is one area where Excel is remarkably weak, it is Scenarios. Lotus' Version Manager was a far more powerful and flexible tool. Be that as it may, are there any tips/hints anywhere about making the best out Excel's 32 variable cell limit? I remember seeing something about this on a website but I no longer know where. Any help you can offer would be greatly appreciated... ...

Scenario Permutations
I am trying to work with Word Data, in Excel, whereby I would like to take a certain situations and generate different permutations. For example, category is Deposit a check and the action is Deposit a check to a savings account, or checking account, or MMA, or CD etc. I am not sure if I need to create a macro or if there is a function I could use. Help!!!! Here's something to play with .. In Sheet1, Assume you have this 3 x 4 within A1:B4 (ie 3 "action" items in A1:A3, 4 "acc" items in B1:B4) Deposit a check to a savings account Withdraw from a checking acc...

Scenario Manager
New to using Scenario Manager. I have a worksheet set up that I now must outline 7 scenarios. Whilst i am able to do each scenario it is possible to have one scenario summary worksheet showing all 7scenarios or must i have 7 sceanrio worksheets? If I can have one scenario worksheet how do I go about this? If the Scenarios are on the same worksheet, you show them in one summary, as described here: http://www.contextures.com/xlScenario02.html Tony0z wrote: > New to using Scenario Manager. I have a worksheet set up that I now must > outline 7 scenarios. Whilst i am able to do ea...

Plotting scenarios
Fairly complex multi-sheet workbook contains a precedent cell and a dependent cell. All sorts of convoluted computations are undertaken to arrive at the value in the dependent cell. I wish to plot a graph of dependent value v precedent value. At the moment the only way I can think of doing this is to set up a separate table, manually vary the precedent cell by constant increments and manually note in the table the value of the dependent cell, then to use that table as the data for a graph. I feel that there must be a better way that takes out the drudgery. Help?? Thanks. -- Return email...

Scenario Function
Hi there everyone! I have a problem which I'm sure must be fairly straightforward to solve, but I've spent hours on this and it's getting me down. I have a spreadsheet called Sales Forecasting and it's used to predict sales volumes and profits over a 6 year period. I want to use the Scenario function to predict the changes if I changed the value in my inflation amount cell. This is easy if I want all years to be affected (the cells for each year use a formula that links to the same cell that the inflation amount is in), but I want the years 1 & 2 to be unaffected by the ...

Scenarios #2
I have spreadsheets in which I need to run various scenarios for well more than the 35 cells Excel permits. Using "versions" in Lotus was much easier. Any suggestions on how to easily accomplish this in Excel? I am in the process of breaking down the sheets into numerous groups of 35 or fewer cells, but it is quite time consuming. There's a sample workbook here, that uses an alternative method to show scenarios: http://www.contextures.com/excelfiles.html#Scenarios under the heading, 'Alternative to Scenario' Variables are stored on a worksheet, and a scena...

Using a formulae within scenarios
Hi, I've just used scenarios for the first time but am a little frustrated in that it only remembers specific values rather than the actual cell reference that I type into the "Enter values for each of the changing cells" boxes. Is there a way of doing this as I wanted to include a whole range of values in the scenario that would be replaced each time. For example consider a basic cashflow forecast where line 1 is balance brought forward lines 2-5 are various incomes lines 6-8 are various payments line 9 is balance carry forward (sum of all of the above) each column represe...

Scenario
Is there a function that will allow me to see what scenario I am currently running? You can include the Scenario name as one of the changing cells. CK wrote: > Is there a function that will allow me to see what scenario I am currently running? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

A scenario...discussed...and help required...
Ok i have created a simple exchange environment. One pdc, mail server added to that domain and created the mail server. Its working fine. Now i ve got to know that there are 2 other existing domains that have to have their emails hosted at this same mail server. Is that possible? If so how...plz discuss a solution.... I ll put it in simple words, pdc.abc.com and mail.abc.com exist hence user1@abc.com is recieving and sending emails. Now i want a user user2@xyx.net and another user3@mno.net to receive mails on this same mail server. Does exchange 2000 work without active directory informatio...

Organizing scenarios
Is is possible to rearrange the order of the scenarios listed in the Scenario Manager? -- without Deleting and Adding? I don't know of any way to sort the scenario list in the Scenario Manager. In one of my sample workbooks, I create a Scenario list on a worksheet, sort it, and use a data validation list to select a scenario. The workbook is here: http://www.contextures.com/excelfiles.html#Scenarios Under the heading Scenarios, look for 'Model Pricing Scenario' anonymous@discussions.microsoft.com wrote: > Is is possible to rearrange the order of the scenarios > l...

Two SMTP connector scenario
Dear All Could you please help me to implement the scenario given below We have Exchange 2000 server running which is configured to accept mails for 5 domains that we have, and currently it is working perfect. It is configured one SMTP connector along with default SMTP virtual server to handle the mails Now I want to transfer the outgoing mails for a single domain through different route (Different IP using a Smart host) by keeping incoming mails to accept for the default IP address. My setup is given below - Exchange 2000 server has 2 internal IP addresses and the first one is published...

What are some common scenarios where we should implement delegates
hi I understand how delegates and events work. I can also imagine some common scenarios where we should implement events, but I=92m having harder times understanding in what situations should we implement delegates (not events). thanx trubar a wrote: > hi > > I understand how delegates and events work. I can also imagine some > common scenarios where we should implement events, but I�m having > harder times understanding in what situations should we implement > delegates (not events). What do you mean by "implement delegates"? Strictly speaki...

RPC over HTTP Scenarios
Any idea which scenario I would use for the following setup? 1 - Windows 2003 Domain Controller/Global Catalog 1 - Windows 2003 Server with Exchange Server 2003 Standard. It's a member server not a DC. IIS/RPC Proxy install on this machine. OWA is working so is mobile PDA access. http://www.microsoft.com/technet/prodtechnol/exchange/2003/library/ex2k3rpc.mspx Never mind this request.. bought a SSL cert from go daddy and it cleared up my problem. "Matt Lavigne" wrote: > Any idea which scenario I would use for the following setup? > > 1 - Windows 2003 Domain Co...

Multiple IF Scenario
Here is my scenario (I am an average user and do not make complicate formulas): I am pulling a single digit from a 12 digit code using (MID(B11,4,1)) Example: B11 = XXXAXXXXXXXX, then the digit pulled is "A" If the 4th digit is 1-9, then nothing needs to be done, but, if the 4t digit is A, B or C, then A=10, B=11, C=12 As you might have guessed, the 4th digit is a month code, that I nee to extract and properly display as a number ranging from 1-12 I tried a multiple IF formula that failed to produce a value, but di not cause errors. I tried AND, OR, THEN, ELSE, but I am not very ...

Merging Scenarios
I created a file which I then sent to two people to create best and worst case scenarios for their input cells. All scenario names are unique – we’ve added our initials after the Best Case etc. I have merged the scenarios from the other two people into my file and created a Scenario Pivot Table Report. I am using the pivot table report because it enables me to see the combined impact of the different scenarios, which it does. However, the page fields are showing only the options of All and MyName. I changed the Author field in the file properties to indicate the other people’s n...

Disaster scenario testing...advice wanted
I want to simulate various server disasters in my test environment and to be able to carry out these simulations I need to actually create problems. In terms of o/s I'm doing this on Server 2008 Standard and SBS 2008. I'm starting off with these issues and want to make them happen (in my test environment): 1. Operating system failure (server won't boot). Is there a file I can delete to simulate this? 2. Active Directory failure 3. Exchange system failure 4. Exchange database failure thanks in advance Restore processes are generally destructive by natu...