/ Head Office:
Robust IT.LTD
Unit 20, New Horizon Business Centre,
Harlow Essex,
CM19 5FN


 /
0800 677 1232
01279 770 950

DVD Rental Scenario - Spreadsheets

For this task, you need to create a spreadsheet file following the instructions below. You may only use a single spreadsheet file, but you may use multiple sheets within the file.

You have been employed as a sales manager by a rental video company, your tasks include accounting and advertising campaigns.

Use the information below to complete a sales spreadsheet in which you:

  • Create a table showing the number of customers each month & the company’s income per month
  • Using formulas, calculate total profit/loss per month
  • Using formulas, calculate how many customers were late and how much were they charged in total, per month
  • Using a SUM formula, show the total profit/loss for the entire year
  • Collect film data to show most popular film and film genre
  • Display total income per month in a bar chart in a separate sheet. The bar chart must have data labels. Image of bar chart is not acceptable.
  • Collate film data to show the most popular film and film genre over the year, create a new sheet for this information titled ‘Film Data’
  • In the ‘Film Data’ sheet utilise an AVERAGE function to show how many times each film was rented on average each month.
  • Create a pie chart to visualise the above information, place this in a new sheet. The pie chart must have data labels. Image of pie chart is not acceptable.
  • In the ‘Film Data’ sheet utilise MIN and MAX functions to highlight the best and worse months for the company (in terms of number of rentals)
  • In the ‘Film Data’ sheet utilise use conditional formatting to highlight any instances where a movie from the most popular list is rented less than 115 times in a month

This file needs to be professionally formatted and presented in a manner that is suitable to presentation to higher management, in this regard you are required to:

  • Utilise standard formatting practices to highlight particular information and separate data so that it is clear to the reader.
  • Divide data between worksheets in an appropriate manner, so that no crucial information is lost to the reader’s eye
  • Present certain information in a table where appropriate.
  • The spreadsheet tabs should have appropriate names.
  • Images of the charts are not applicable.

Once you have completed the above tasks it is important to reflect upon your work, so that you can highlight any possible improvements and make notes for future files. Create a new worksheet in the file you are due to upload, name this worksheet ‘Exercise review’, in this worksheet you should:

  • Discuss any issues you came across during this task.Highlight the aspects that you believe you did well, and the factors that you feel could be improved upon? (At least a paragraph long answer is required)
  • Describe how your file meets the requirements of the task? (At least a paragraph long answer is required)
  • Explain how your formatting choices make data clear and defined to the reader? (At least a paragraph long answer is required)
  • Identify any quality problems within your spreadsheet file, how could these problems be fixed if more work was completed on the file?(At least a paragraph long answer is required)
  • Describe what are errors, how to find errors in your spreadsheet and how to fix errors? (At least a paragraph long answer is required)

Sales stats:

  • Price for a movie rental: £3
  • Number of customers in January: 7651
  • Number of customers in February: 6468
  • Number of customers in March: 5843
  • Number of customers in April: 8918
  • Number of customers in May: 8771
  • Number of customers in June: 8449
  • Number of customers in July: 7378
  • Number of customers in August: 9503
  • Number of customers in September: 9594
  • Number of customers in October: 8834
  • Number of customers in November: 8189
  • Number of customers in December: 10131
  • Late return fee £1.50
  • Percentage of late customers per month: 14%
  • Monthly expenditure: £20,000

Popular Film Stats:

Film Title / Genre / Rented in Jan / Rented in Feb / Rented in Mar / Rented in Apr / Rented in May / Rented in June / Rented in July / Rented in Aug / Rented in Sept / Rented in Oct / Rented in Nov / Rented in Dec
Terminator / Action / 260 / 192 / 124 / 109 / 211 / 186 / 179 / 168 / 181 / 172 / 156 / 271
The Matrix / Action / 151 / 144 / 131 / 155 / 183 / 161 / 155 / 177 / 156 / 131 / 146 / 181
Mission Impossible / Action / 251 / 213 / 244 / 274 / 281 / 299 / 240 / 261 / 271 / 281 / 211 / 341
The Mask / Comedy / 166 / 122 / 126 / 117 / 89 / 109 / 244 / 215 / 255 / 181 / 151 / 187
Home Alone / Comedy / 213 / 155 / 111 / 99 / 121 / 91 / 282 / 278 / 201 / 101 / 121 / 195
Avatar / Sci-Fi / 445 / 454 / 471 / 461 / 411 / 312 / 428 / 456 / 422 / 371 / 357 / 333
The Exorcist / Horror / 211 / 244 / 190 / 155 / 162 / 112 / 169 / 187 / 171 / 121 / 101 / 171
Insidious / Horror / 433 / 465 / 447 / 399 / 413 / 368 / 384 / 372 / 381 / 322 / 301 / 312
The Shawshank Redemption / Drama / 332 / 363 / 344 / 321 / 299 / 341 / 279 / 240 / 210 / 311 / 317 / 213
Jurassic Park / Sci-Fi / 356 / 398 / 453 / 471 / 389 / 481 / 501 / 521 / 485 / 387 / 298 / 541
Others / Others / 4833 / 3718 / 3202 / 6357 / 6212 / 5989 / 4517 / 6628 / 6861 / 6456 / 6030 / 7386

Registered in England No. 7887928 VAT No. 126186908