Edexcel GCE Applied Information and Communication Technology Unit 3: The Knowledge Worker January 2013 Scenario Paper Reference 6953/01 The scenario should be distributed to candidates at least three working weeks before the examination. Practice files: whitecaps_practice. xls, fixtures_practice. txt, haway_practice. txt This scenario should be used for the purposes of preparing candidates for the examination. This copy must not be taken into the examination. The information contained in the scenario will be included in the examination paper.
Further details are in the Instructions of the Conduct of Examinations (ICE), available from the Edexcel website for this qualification. Edexcel will not accept any request for special consideration should candidates be given the incorrect scenario for the examination they are sitting. Turn over W41049A ©2013 Pearson Education Ltd. 6/ *W41049A* Scenario Havering Whitecaps Maddy Wright and Katy Parrish have supported Havering Whitecaps since their boyfriends played for the team. Their boyfriends’ careers and the teenage romances are now very much in the past but their support of the team remains as strong as ever.
Until 15 years ago the Whitecaps were a struggling non-league team with little support. There were times, on wet and cold November afternoons, when Maddy and Katy were the only supporters. In 1999 a magical thing happened to the Whitecaps. At that time the team’s position, both in football terms and financially, was perilous to say the least. However, a financial backer appeared in the form of Harold Adenyi. Harold had studied at the local college and even played a few games for the Whitecaps. His progress as a footballer was hampered by a lack of ability.
He gave up on a career as a footballer to concentrate on what he was good at, which was IT and mathematics. He made a large amount of money by creating an online accountancy website called lesstax. com. Harold was looking for things he could offset against a huge tax bill and a loss-making football club seemed to be a good start. He bought the Whitecaps from the previous owners for ? 1 and immediately started to spend money on it. Harold’s flair for business meant that the loss-making football club did not stay loss-making for long.
Harold signed a talented former football league manager and with his advice, spent money on players who all turned out to be good investments. The way the manager had the team playing soon started to increase its fan base, so Harold built a new stadium. The team continued to be successful and the new stadium was soon filled at every match, putting the club on a healthy financial footing. Financial success and playing success went hand-in-hand and, in 2001, Havering Whitecaps was elected to the Football League. After a few seasons, when the Whitecaps struggled to establish themselves, there followed a eriod of gradual success including promotion to the 1st Division and two years later the Championship. Last season the Whitecaps won the Championship and are currently looking forward to their first season in the Premier League. Throughout this period of development, Maddy and Katy have been screaming encouraging words to their team (and the occasional discouraging one to the opposition) and they have hardly missed a match. It was inevitable that as the Whitecaps’ two most dedicated supporters they would become the chairman and secretary of the supporters’ club.
One of their main tasks is to organise away trips for fans. This involves buying the tickets, booking the coaches and deciding what to charge for the ticket and transport packages. With 14,678 registered supporters, organising the away trips is not an easy task. With the possibility of over 400 coaches travelling to an away match, the number of transport firms the pair could use was limited. Since the Whitecaps were promoted to the Championship Maddy and Katy have used ‘Rubicon Transport’ whose rates are reasonable and who can supply enough coaches on a match day. 2 W41049A
Rubicon charge a fixed sum of ? 350 per coach, plus a ? 1. 50 fuel surcharge for every 100 miles travelled. In addition, Rubicon’s regulations state that a driver cannot be in charge of the coach for more than 9 hours a day. Should the whole trip last more than 9 hours (including the match time) a second driver would be required. There is a charge of ? 75 for the second driver. Premier League ticket prices vary greatly and Maddy has been quoted a range of prices varying between ? 30 and ? 54 by the Premier League clubs. Maddy and Katy do not need to make an overall profit.
Their main aim is to get as many fans as possible to away matches. Although they can afford to make a loss on individual trips, they cannot afford to make a loss overall. Therefore an accurate prediction of the number of people who will want to go to a particular match is vital. They are also aware that more people will want to go to some matches than others. To help find out how many people are likely to go to each match they ran an online survey. This was completed by 94% of their registered supporters. To make data analysis easier, Katy has classified the teams as shown in the table.
EG Teams fighting for European glory MT Mid-table teams RD Teams likely to be involved in the relegation dogfight PR Newly promoted teams In addition to the classification of the team, she found that other things affect the number of people likely to go to away matches. Fewer people would go if the match was played on a weekday rather than a Saturday (or Sunday). The longer the travelling time, the fewer supporters were prepared to travel. Finally, the overall cost of the package would discourage some from attending certain matches. W41049A 3 Turn over Description of the model
Maddy and Katy have to be as accurate as they can when setting the prices as they don’t need to make a profit but cannot afford to make a loss. Maddy and Katy need a model to help them decide on prices. Therefore, Maddy has persuaded her brother Kieran, who works in IT, to create a model. Kieran wanted to make the model reusable so that he could sell it to other supporters’ clubs. Unfortunately, before he could finish the model, the company Kieran worked for sent him to Dubai on a three-year contract. Maddy has asked you to complete the model and use it to suggest prices for the next season’s away matches.
Kieran has left you these notes. Worksheet Description Teams This is the worksheet that will be used to set the package prices. I have listed the teams in the Premier League along with Katy’s classifications. For ease of viewing I have included information from other worksheets and have calculated the total costs, revenue and profit. There is a profit for each trip and a total profit for the season. The worksheet will also calculate the total number of supporters going to away matches. Cell B2 contains a drop-down box to choose the team you support. Fixtures
This worksheet is a calculated page which lists the dates of the home and away fixtures. Lookups This worksheet contains a number of lookup tables. There are two blank areas, these will contain the fixture information provided by the Premier League. There are other tables to look up the day of the week, the likely support based on the teams’ classification, the number of registered supporters and the ticket costs for the various clubs. Distances, Times These worksheets contain the distances between the grounds and the average times it would take a coach to travel those journeys.
I got these from a very helpful man at the RAC. Customer This worksheet is incomplete. Once completed it will predict the number of supporters who would travel to each away match. This is based on the number of registered supporters and then adjusted according to the home team classification, then package price and finally the travel time. I haven’t finished the analysis of the survey yet so I cannot put in formulae to take into account the travelling time and the overall cost. A friend of mine is doing the analysis for me and he will forward the results to you as soon as he has them. Transport
In this worksheet I calculate how many coaches are needed, the cost per coach and the overall transport cost for the trip. Transport Rates In this worksheet I have entered the transport rates as quoted by Rubicon Transport. I hope you find it easy to understand my notes. Good luck completing it. Kieran Wright 4 W41049A Some cells in the model are password protected. Should you wish to experiment with the model, the password is edexcel. Be aware that if you change the contents of any protected cell the model may not work. Please note that the Football League and Premier League referred to in this scenario are fictitious. W41049A 5
