Reference no: EM132639427

Question 1: Create series of Excel spreadsheets to demonstrate the skills you learned in this module.

You will need to demonstrate your ability to design a group of spreadsheets that:

1. have a link between one spreadsheet and another
2. demonstrates conditional formatting
3. use at least three functions from Excel and one formula you create
4. has at least one macro
5. uses a template you created to produce new workbooks
6. uses charts to help analyse data

You will need to provide printouts from the spreadsheets and a copy of a chart.

Example:

You can use this as your project if you wish.

A friend, Josh, knows you have a good understanding of Excel.

• He would like you to help him with his business. He wants to keep a spreadsheet for each month's sales. He has five sales staff and would like to record the daily sales figure. He wants to see a monthly total for each person and a daily sales total. He would also like an average daily sale, the lowest sales figure and the highest sales figure for each day. The data should have highlighting to show figures below the daily average and figures above the average. There should be a separate worksheet that shows a commission figure for each salesperson on a daily basis. He pays each person a flat 15% of everything they sell as a commission.

• He thinks a template for the above spreadsheet would help him because he will create a new spreadsheet every month.

• Then he would like a master spreadsheet that shows each person's monthly sales figure. He would like to have an annual total for each and a grand total. He would like a chart showing each person by month and another chart that shows each person's annual sales figure as a part of the total sales figure.