More vocabulary lessons before school starts...(and this long non-summer holiday ends)

Man, is this 'summer' holiday dragging on forever? In the height of summer, the bridge outside our house has been flooded twice. The rainfall carries on, the children get more cranky and bored, and parents slowly, but surely, lose their minds.

In a rare moment, I found my sense of humour, and have tried to track the effect of long summer holidays on a parent's patience levels. In continuation of our series on improving your Excel vocabulary, we will look at 2 ways we could calculate this - the easy but long winded way, and the short but slightly more complicated way.

First up though, lets look at some made up figures, formulas and assumptions:

Let me explain my thinking on the figures above:

Variable Factors:
 - We have 8 weeks of summer holidays. Cell C3 shows us what week we are in out of those 8 weeks.
 - We assume the maximum number of outings/activities a child can attend a week is 14 (2 a day). Cell C4 shows us how many outings the child has actually attended.

Parameters:
 - The average assumed cost of an outing is €15.
 - The calculation of a child's boredom level is =(C3/C8)*(1-(C4/14)). This is saying the longer the holidays go on, the higher the boredom level will be, and can only be mitigated by more and more outings.
 - The weekly cost of outings is simply C7*C4
 - The % weekly cost of total possible is C10/210
 - The parent's patience levels is calculated as such =1-(((C3/C8)*0.45)+(C9*0.25)+(C11*0.3)). Before you lose your temper trying to figure this out, I have simply given different weightings to the current week in the holiday period, the child's boredom level and the cost of keeping them entertained. Added them all together and inverted.

Method 1: Manual What If Analysis

In this method, you could use the table above and change the variable factors (i.e. Cells C3 & C4) to obtain individual answers for various scenarios. So you could change the week to 6 and the number of outings to 4 and the parent's patience levels would be sliding down to 44%.This is great if you needed to get a once off answer every now and again, but what if you wanted to get the bigger picture? You would use:

Method 2: A What If Data Table

This is the simplest of the What If tools Excel provides. It is a data table that you can either use one or two variables on. We will go straight to using two variables. You need to start by setting up your table like so:

You will need to add in the variables in cells F4 to F17, and G3 to N3. The headings (Weeks in holiday and Weekly outings taken) are optional and should not be included in your range when creating your data table. The formula in F3 is = C12, which is our calculation of our parent's patience level.

Once you have done this, highlight the range F3 - N17 and go to Data - Data Tools - What If Analysis - Data Table. You will get a little pop up box that you need to complete as follows:

Press ok, and your results are displayed as below:

Add in a bit of conditional formatting, using the colour scales, and the problem becomes immediately apparent:

By week 8, a parent is ready to explode...which begs the question as to whose great idea it was to have a 8 week long holiday anyway?

Now, you may be wondering why this is at all helpful to add to your vocabulary. Well, you may not need this exact calculation (unless you belong to the Board of Education, and then please feel free to distribute to the powers that be!). But you may want to calculate the profits to be obtained if you sell a product that has reduced production costs per product the more units that are produced. Or calculate various payment options on mortgages with different interest rates.

 My newsletter this week will have some more examples....why not sign up here?

Happy spreadsheeting!