Spreadsheet: 38061d1236182626-icrosoft-excel-if-functions-or-vlookup-problem-deleteme
Sheet: Requirements

EXCEL Main Assignment - 2009
This Assignment is worth 25%.
To be completed in groups of NO MORE THAN 3.
A warning: you will be asked to submit the EXCEL 2007 file - please make sure I can read it.
A sample of order data for a quarter (3 months) is given in sheet Orders.
This table of data is incomplete.
The Orders data has been taken from a database system and is in a sparse form.
The data about Salesperson Name is given in sheet Salespersons.
The data about [NAME] (Toys) is given in sheet Products.
The data about Product Prices and Unit Costs is given in sheet Prices.
Requirements (Allocation of Marks):
1 | Fill in the blanks and change date 'number' to a date
0.05 | Use Paste Special to remove any formulae created.
 | Describe how you do this in the Conclusions sheet.
2 | Use concatenation to create the 'full' name for the Salespersons.
0.05 | See Salespersons sheet.
3 | Complete the Orders Table.
0.1 | [NAME] columns as appropriate to display day of the week, Salespersons' names, Product description,
 | Unit Price and Order Value.
 | This should include functions such as TEXT, VLOOKUP and …..
 | Please do NOT 'destroy' any formulae created using [NAME].
4 | Copy the completed Orders sheet 3 times.
0.1 | In the first copied sheet create SubTotals of Quantity (boxes) and Order Value by Date.
 | In the second copied sheet sort the data by Product Code and create SubTotals of Quantity (boxes) and Order Value by Product.
 | In the third copied sheet sort the data by Salesperson Code and create SubTotals of Order Value by [NAME].
 | Give the sheets appropriate names.
5 | Create THREE Pivot Charts (with Pivot Table), placed on sheets called Chart1/Pivot1, Chart2/Pivot2 and Chart3/Pivot3,
0.1 | based on the original completed Orders sheet, that give insight into the data.
 | (Note: different Pivot Tables can be displayed on different sheets.)
 | (You can assume the form of the Pivot Table will not change.)
6 | Smarten up Orders Table.
0.05 | This might include 'wrap text' for column headings, appropriate use of lines, colours etc, (but do not overdo it).
7 | Create a Custom list for the Products (Toys).
0.05
8 | Copy the Products sheet and rename it Protect.
0.05 | Protect the sheet, but allow the Salespersons' names to be changed. You must not be allowed to change any other cell.
9 | Carry out any other analyses you see fit, using any functions (e.g. database functions), commands (e.g. AutoFilter),
0.15 | etc. you think appropriate.
 | The results from these 'other' analyses can duplicate those found in the Pivot Tables and each other.
 | That is, you can show alternative ways of analysing the data.
 | (These should be on separate appropriately labelled sheets.)
 | You may wish to copy the completed Orders Sheet to display these other analyses.
 | These other analyses should NOT include Subtotals and Pivot Tables.
10 | Create a Navigation system using Visual Basic so that I (the marker) will be able to find my way
0.1 | around your spreadsheet model. The easier it is for me to mark the assignment more marks will be awarded.
11 | The unit cost for each product is given in the Prices sheet.
0.1 | A Salesperson earns £3000 per month plus a bonus based on sales revenue generated - see Salespersons' sheet.
 | Calculate the Profit after deducting Product costs and Salespersons' remuneration for the quarter.
 | (You can ignore distribution costs etc.)
12 | Give a summary of the main conclusions about the performance of Salespersons and Products, indicating the
0.1 | analysis(es) used to arrive at such conclusions.
 | Put this in the text box in the sheet Discussion.
Comments:
 | Appropriate use of "Names" will be rewarded.
 | One Name has already been created; PersonCode (for Salesperson Code).
 | The information on prices is not yet in a form suitable for the VLOOKUP function.
Due:
 | 11am, Wednesday, 18th March
 | Please give Names (optional) and Student Numbers on sheet Discussion. (You will lose 10% of mark if this is not done!!)
 | (Instructions on how to submit the assignment will be given later.)