# Edexcel Applied ICT Unit 3 exam Jan 2012

Discussion in 'Computing and ICT' started by colwynexile, Dec 1, 2011.

1. ### colwynexileOccasional commenter

Pre-release scenario has been published, but no sight of data files yet. Can we keep discussions of actual methods to be used to a minimum so that those who have taught there pupils to use Excel can really gloat when those who tell the kids to re-produce parrot fashion fall over themselves when the exam takes a different twist to what they expected and their pupils just blithly carry on - and to stop those smart alecs whinging when a 'big secret' is given away.

2. ### p1ppajNew commenter

Yes I agree about the rating worksheet. Done everything else..... just can't figure out what goes in the blanks. Trying to understand the AHHL worksheet....

3. ### spj2

The AHHL worksheet seems fairly straight forward, it will just read from the AHHL Data. The Ratings sheet looks tricky, I imagine they will be required to to enter formulas similar to G9 and N9 only with different weightings. Not sure about Season Weeks, think we're missing something that would go in there, although the scenario mentions that sheet will "calculate" the number of free weeks so I don't think it will simply be another data file.

4. ### chrisbarnes

Figuring out the blanks is what you have to do?!?
Anyway.... seems to me that there will some sort of formula required to calculate the season weeks for each property based on the price set and the rating possibly? Maybe to round it down to the nearest integer so as if the SP sheet for example says the property would 97.97% full, then you multiply the available weeks by that and round it down?
To be honest, having taught this course since it's inception I think this is quite possibly the most confusing spreadsheet to date. Not that it looks particularly complicated, just that the actual pre-release gives away so little about what will actually be happening on each sheet.
They're usually pretty obvious in as far as what each sheet will do but not in this case.
Or maybe I'm being a little thick?

5. ### spj2

I agree, compared to some of the previous Practice Data Sets they are giving away very little. I've been looking at it for about an hour and its not getting much clearer.

6. ### HappyHippy

Seems to me that Rental Setting will be the usual trial and improvement / find a strategy that works bit - easy but time consuming.

Revenue - easy peasy

Season weeks - huh? I just stuck in the max allowed, but I'm guessing there'll be something about maintenance periods maybe? 39 weeks in total if you add them all up. Am assuming it'll be data entered manually from a memo or suchlike.

Ratings - Surely we'll be given some kind of formulae or rules to enter - perhaps including an IF (there usually is room for an IF somewhere...)

AHHL - Straightforward, and this will be where Q2 comes from I reckon.

This reminds me quite a bit of the beach shop from the summer. Hopefully the results will be a little better this time...

7. ### tangmekaa

34 different prices to enter on the rental setting page! Seems like even when the formulas are all in there will need to be a lot of trial and improvement to be done to find a decent profit.
For the last few papers the formulas have seemed pretty logical. The formulas in some of this model are very "bespoke" - for example the ratings formulas in N & Q of ratings (which will be similar to O & P, I assume). I can only assume that in the exam they will give some guidance as to what kind of formula is required !?

I am always interested in developing a 'strategy' and over the years
that I have taught this subject I have loved finding that out for every
model.
for this model, I have managed to get maximum profit with 100% occupancy for all weeks mentioned for each season. (the actual value will largely depend on the formula for calculating ratings for Off peak and Ski peak).
But I am using a formula to do this on the Rental settings sheet and I am dead sure, that is not the way the students will be asked to complete the solution since the formula is very difficult and long.
What is tripping me is the Seasons Week sheet. There is something about it that doesnt fit in - the cells B19:E19 are not used as references anywhere in the model. So what is their purpose?
I agree - this exam is going to be a complete surprise.

9. ### DEmsleyNew commenter

I'm very impressed by this spreadsheet as it will rest solely on the ratings formula you use, which will be given in the exam (hopefully).
Gone are the endless debates ala "I got &pound;xxx,xxx profit" "I only got &pound;xxxxxxxx" etc

Bravo EdExcel - well done!

PS I got &pound;155,242 profit

10. ### spj2

I think they will just be used as a reference for the maximum number of available weeks in each season. The values in B8:E17 influence the Variable Costs for the properties. Still not sure where those values will come from though, it might be data entry from a memo etc as someone above suggested however the wording in the scenario is that the sheet will "calculate" the number of weeks each property is available which makes me think some kind formula may be required. Perhaps I'm just reading too much into it though.

11. ### tjraOccasional commenter

Sorry to hijack the discussion but would you all recommend Edexcel as an exam board?

I had found another cracker -
On Cost sheet cells B22:31 - has anyone noticed they are formatted as &pound;. If they are weeks shouldn't they be formatted as numbers?
I have figured out 2 possibilities for the figures on Season Week. But due the word 'calculate' on the scenario handout, I am tripping myself thinking - Nah! Edexcel aint going to ask the students to construct a complex formula. So the only other way seems pointless - but then it is possible.
There is another twister in the bag - has anyone tried using references to 'transfer' data values into the AHHL sheet, after importing them into a new sheet in the model? There is a nice surprise waiting there too
This exam is going to rock!

13. ### jwright6

The season weeks is an interesting one. I cant see where they can select where they can choose 80% or 100% occupancy so I wondered if it would be done in here as they would need an absolute cell reference to replicate it.
Paste link sorts out transfer problem
Any other ideas for Season weeks?

14. ### DEmsleyNew commenter

"Season Weeks" is used in the formulae on Costs sheet. B22:B31
The 80% and 100% is explained in the last paragraph in the scenario.

Found something interesting in the model last night.
The Revenue formula in sheet Revenue for each property for the different seasons incorporates the occupancy factor.
Revenue = Price(Rental setting) * occupancy% (SP/OP/SK/CHCalc) * weeks used (Season week)
So the relation of the price affecting the occupancy has been incorporated in this formula already.
Cost = Fixed cost + variable cost
The variable costs should be dependent on the occupancy of the properties. So they should be incurred only if the property is occupied. That means there should have been the occupancy factor while calculating the weeks in cells B22:B31 on Cost sheets. But neither there is the factor, nor there is factor to influence the variations in gas/elec consumption throught the different seasons. If there could be different revenue calculations for different seasons, why not have different calculations for variable costs too? So the model ASSUMES that there will not be any variations in variable costs due to seasonal change.
Now back to the occupancy issue on Costs. If revenues incorporate the occupancy factor already, then incorporating the occupancy factor in Season weeks for the sake of (variable)Cost will skew the revenue formulae. We will just have to live with the fact that the variable costs does not incorporate the occupancy issue and comment about this in the 'Evaluate the model' section. I think the solution to fix this is beyond the scope of our students.
I think there is a simple task set out for this area of the model.

16. ### hollata

I wonder if there will be another data file to import which contains the number of weeks that each property is unavailable during each season.
This will need to be imported to a different sheet then student will need to use a formula to take away the number of weeks not available from the number of weeks in the season (B19:E19) using an absolute reference.

17. ### DEmsleyNew commenter

There seems to be an interesting issue with the skiing properties as well. Apparently Christmas is not in the ski season.

18. ### DEmsleyNew commenter

I'd also have to be considering why I'm paying maintenence on a pool for three properties that don't have pools.

19. ### spj2

That sounds pretty reasonable to me.

20. ### stevieash

HI, I am having a problem importing the data set as the last value in each section has a comma in it and divides the figure across two columns ie 20,220, I successfully got the data in by removing the offending commas but surely this should not be good practice, am I missing something obvious while importing? Thanks in advance.