Reference no: EM132283711
Assignment -
Need to do assignment based on excel workbook. All instructions are given below.
Instructions -
1. Download the Excel Spreadsheet file "TAFExcel".
2. Open the spreadsheet and in the first worksheet compete the first set of functions so that the spreadsheet shown total sales for each product after six months, and then after 12 months. Also, find out the total for each month (as well as the product).
3. Work out the average deviation for the first six months sales of the products. Add extra rows or columns if necessary.
4. For the months July to December find the average sales per product. Then find the average sales per product for the same period but only include figures above 170. There may or may not be any above 170 but the formula in excel should be in force to allow for it. Use the appropriate function to work this out.
5. Display the current date and time somewhere visible in the worksheet using a function.
6. Type in any cell the word "Technology" ensuring that you type in lowercase. In a cell beside it convert the text to uppercase using an appropriate function. You may leave the original word in place.
7. In an empty cell of your choice put the word "Microsoft". In a separate cell put the word "Office". Then use a function to join these words together in one cell.
8. On the next worksheet called DATABASE in cell M1 put the total for all the months' sales in the previous worksheet. This should just be one figure but must be derived from a linked function (not just typed in!).
9. In the same worksheet called DATABASE use data validation to ensure that in the price column the price must be greater than 20 Euros.
10. Then filter the data to show all orders from Dublin AND Paris in the month of March. Again, there may be none but it should be set up to show if there are.
11. On the next worksheet that displays data for a chart called "Charts" create a suitable chart to display the figures excluding the totals. Ensure that it is presentable.
12. On the "Scenarios" worksheet create two scenarios: the first should look at the effect of a 5% increase in Sales whereas the second should look at a 10% increase in rent. These increases apply to all stores.
13. In the final "Pivot" worksheet create an appropriate pivot table that will display the data in an effective and flexible manner. The pivot table should be displayed on a new worksheet.
14. Create a macro that automatically arranges three windows within the file to be viewed vertically you may have to create more windows in order to view them together. However, it is possible that you have done this already in your work. Use a keyboard shortcut and need the macro a logical name. As a result of creating this macro you may need to save the file in a macro enabled workbook. Follow the instructions provided when you try to save when doing so.
15. Finally, check the marking scheme to ensure all parts are complete and correct.
Attachment:- Assignment File - TAFExcel.rar