# Edexcel Applied ICT Unit 3 exam Jan 2012

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

1. ### vornstarNew commenter

I original thought it was an IF but then all the discussion on here made me change my mind. I have taught the INDEX and MATCH functions today and it went better than I thought. They'll hate me if it is not that now.

while we are talking about shooting anyone down in flames, can I bring some points up for discussion?
the scenario says that there is a lot of maintenence that happens during off season.
The addition of Available weeks in season Weeks sheet do not add up to 52. Has anyone noticed that?
What happens to the other weeks missing out from the model? how do we get them accounted in the model?
What happens if maintenance 'drags along' and 'eats up' some 'Available weeks'?
Can repairs occur DURING Available Weeks or season? If so, then the property is 'Unavailable' during this time.
Given a maintenance/repair schedule, would it be possible for us to incorporate it in the model, using a fairly SIMPLE formula?

3. ### tanhurs

Please can you explain this in more detail??

How do you work out the predicted occupancy? You have said it it the property rating and the seasons but where are you putting a formula to work it out - which sheet?

4. ### tanhurs

"Consequently, I think we just need to use an IF statement that looks at the values in Rental Settings cells B8:E17. If there's a rental price there, it returns the relevant available weeks value (cells B19:E19 on Seasons Weeks) and if there is a zero (as for properties 1-3 and 8-10), it returns a zero. "
DOES THIS DEPEND ON THE RENTAL PRICE SET TO COMPLETE THE IF FORMULA?
HOW DO YOU KNOW HOW MANY WEEKS IT WILL BE AVAILABLE TO RENT - DOES THIS NEED ANOTHER ASPECT TO THE FORMULA TO LOOKUP THE PRICE SET TO THEN CALCUATE THE NUMBER OF WEEKS THAT IT WOULD BE AVAILABLE TO RENT?

5. ### dmmlNew commenter

"are we reading too much into it?"
Yes as always you all are

6. ### nikrah

Is there any chance of some one emailing me some help as all I have done to date is import the data and linked it into the sheet - I am not sure where to go from here and I think I am goint to flounder.

7. ### chrisbarnes

What a helpful and constructive comment.

8. ### staxisNew commenter

I've been teaching this unit since it came into being. This exam is the least predictable ever so I am least comfortable helping my students with it. I think it is in danger of being a 'spreadsheet' exam and not a 'decision making/modelling' exam - shame.

9. ### DuckyGee

I very much agree staxis.
I have finally had a chance to have a proper look at this with regards to the INDEX/MATCH solution on the Season Weeks sheet and I now understand how it works. However, I am really wondering whether the exam board would have set something so completely different to previous years. In previous years, pupils have needed to use SUM functions, VLOOKUP functions and IF functions, but never anything as long and convulted as the INDEX/MATCH option.
I really do think that if the INDEX/MATCH option turns out to be the correct option, this will mean that Edexcel have completely switched from an exam that tests students understanding of decision making and knowledge management to a spreadsheet test, which I thought was what Unit 11 was for.

10. ### Baz_Liverpool

On the subject of over thinking this spreadsheet
I agree that the use of INDEX/MATCH would be a step up from edexcel in this exam and it has been used elsewhere in the spreadsheet (I think to complete this function).
season weeks:
would they not just be asked to copy n9:q18 from the ratings sheet into this area? this seems to complete the model just fine when ive tried it. I agree that its pretty simple but that doesnt mean its not the answer.
I dont think the available weeks will actually be used (similar to sections in the kornish kraftz exam last year) but theres potential here for an if statement i suppose. thoughts?
ratings:
this is by far the most difficult sheet for me, the overall column could litterally be anything and i expect they will give us the formula in the exam, what are peoples thoughts on this? am I missing something as it cant just be a sum= based on whats in the other overall column (a completley random calculation as far as I can see)
as for the other two ratings columns, I expect again that the formula would be given in the exam but it could be anything based on the other two formulas in the spreadsheet. What I cant understand is why its 2.5* for the summer and 1.5* for the christmas, the rest of the calculation just about makes sense but is still pretty 'bespoke' like has been mentioned.
I have just had the kids practice by entering random formula multiplied by percentage etc based on worded formula (e.g. multiply the sea by the .....) so they should be able to cope with this easily.
the other option is an if statement?
Revenue:
seems easy enough, could be a prime candidate for conditional formatting
Rental Settings:
this one is also tripping me up, on a simple level the kids could just type in amounts (seeings as all the available cells are set as currency)
but would they really expect them to enter that many individual amounts? and look them up on the calculation tables themselves?

I have read mention of a lookup? where are people using this?
cheers

11. ### cc4rhu

Ok, perhaps I'm being an idiot with this but I thought that season weeks would simply be how many weeks the proprty would be available and that it was a validation exercise using B19:E19.
Don't they just select the number of weeks from a drop down? Except for propoerties 1-3 and 8-10 during the ski-ing season. Perhaps an if statement can come in there.
I don't understand why the season weeks sheet would link to the ratings sheet.
Thanks
Cc

12. ### p1ppajNew commenter

If you read the scenario it says the season weeks "... calculates the number of weeks that each property is available during each season...". Therefore it should show how many weeks each property is available - nothing to do with what points rating each property has (which is what the ratings sheet shows). So I agree with cc4rhu on this one.
My feeling is it could either be another text file to import OR they give us the properties available for each season OR you need to apply a bit of common sense. I think someone mentioned before that when you look at the ratings worksheet, you will notice only 4 properties offer winter sports.... so common sense says only these 4 would be open at ski peak? The scenario also says "...<font face="MyriadPro-Regular">She was also able to rent out all of her properties over the Christmas holidays......". Again common sense says all properties are available over the Christmas peak. No mention of Summer peak or Off peak as yet (maybe they will tell us this in the exam?).</font>
<font face="MyriadPro-Regular">We have looked at using a HLOOKUP on the rental settings so that the value to check is the points rating given on the ratings worksheet, looks this up on the AHHL worksheet, identify the row required to give a rental value for the property. Seems easy enough (hopefully!). You need to do this for 100% and 80% and take it from there.</font>
<font face="MyriadPro-Regular"> That would or *should* fit in to what they have done in previous years. Fingers crossed!</font>

13. ### DEmsleyNew commenter

I believe that to be incorrectly worded. Season weeks should calculate number of weeks it's occupied based on % chance given by price charged. It should read, IMHO, "... calculates the number of weeks that each property is OCCUPIED during each season..."

Erm, no again. Common sense says these properties would be open in ski season. The spreadsheet says no. Ski season is 15 weeks so covers mid-December to Easter. The properties are in France. France at Easter is busy with tourists. She would be seriously stupid to close properties 1-3 and 8-10 during all of that period.
Where are the missing 13 weeks?
That's my students b*****ed then ;-)

14. ### p1ppajNew commenter

Darn you're good.

15. ### DEmsleyNew commenter

I think that that is something for the evaluation of the model.

16. ### tangmekaa

Can't see any need for Index formulae.
The 'season weeks' is just to give the max no. *available* for each property for each season.
I agree with others that this will be the IF and absolute reference question - that is, each entry should be the number in the bottom row of the table unless the rent is set at &pound;0 for that property in the 'rental settings' sheet, in which case the weeks available for that property in that season will be &pound;0.
Other sheets ('cost' & 'revenue') use the index formulaes to predict the likely occupancy as a %. This % occupancy is then multiplied by the weeks available in 'season weeks' to give the predcited occupancy in terms of a number of weeks. For example see 'Costs'!B22 which gives the number of weeks predicted for property 1 for all 4 seasons combined (should not be currency though!)
The bit I am struggling with is 'ratings' columns K, O and P as these are much more random!

17. ### Baz_Liverpool

are we in agreement that 1-3 and 8-10 are closed simply because they are not situated anywhere near the ski resorts, for the purposes of the model?
probably the in built maintenance that was mentioned?

Yes
and
Yes

19. ### Steve Carter

Here's a thought (based on the assumption that Season Weeks uses an IF function and some absolute refs):
The exam paper will give the AHHL formulae for students to enter into cols K, O and P of the Ratings sheet. They will be rather random looking (like the existing ones) and return a figure between 10 and 100. The formula for the Ski Peak ratings will incorporate multiplying by the Winter Sports rating, meaning that any property (1-3 & 8-10) that has a Winter Sports rating of zero will end up with a Ski Peak rating of zero. This would then give us a table of figures in the Ratings sheet that's ripe for an IF function on the Season Weeks sheet.

20. ### p1ppajNew commenter

Back again for another look..... DEmsley i've just reread the scenario and it says the ski season run from Nov to Feb so maybe this is why the properties are closed?
This spreadsheet is a pain. Lucky(?) for us that we are still in school until next Thurs