I have some work, in an Excel spreadsheet. do Macros and Create Automated Report
Task 1 (33 Marks)
1- Convert the table on the left into a dynamic table (2 Mark)
2- Given this table, insert a cross-tabulated pivot table in a new worksheet, which shows:
Gender, Hand, LeftTime and RightTime. You need to decide by yoursel in which Pivot field each of these field should go to. (2 Marks)
Rename the worksheet "Experiment"
3-Remove Grand Totals (2 Mark)
4- Change the design of your pivot table to tabular form (2 Mark)
5- Show values of LeftTime and RightTime as "Average" not "Sum". (2 Marks)
6- Change the number format in the pivot table to show 2 decimal places only (2 Mark)
7- Insert two slicers for: "Hand" and "Gender" ( 2 Mark)
8- Change the format of the "Hand" slicer to have 3 columns, and change the colour to Yellow. (2 Mark)
9- Change the format of the "Gender" slicer to have 2 columns (1 Mark)
10- Insert 3-D stacked bar chart (1 Mark)
11- Do the following formatting to your chart:
a) Hide all field buttons on the chart (1 Mark)
b) Change chart size and properties to not move or size with cells (2 Mark)
d) Hide Gridlines (1 Mark)
e) Show data labels on the bars (1 Mark)
f) Delete Horizontal (Value) axis (1 Mark)
g) Give a title for your chart "Average Response Time per Gender" (1 Mark)
12- Use the slicers to show:
a) Gender Slicer: Female (1 Mark)
b) Hand Slicer: Both , Right Hand (2 Marks)
13- Take a screen shot for you the current view of your Pivot Table and Chart. Paste the photo around Row 40 (2 Marks)
14- Add the following three records to the dynamic table table in Task 1 worksheet: (1 Mark)
ID Gender Age Hand LeftTime Righttime
170 Female 50 Right 0.198 0.345
171 Male 25 Left 0.312 0.101
172 Female 35 Left 0.122 0.845
173 Female 15 Right 0.785 0.857
14- Go back to Experiment worksheet. Refresh your pivot table and save.
Task 2: Create Automated Report (60 Marks)
You have on the left a database which consists of approximately 22,000 records.
1- Convert tables to dynamic tables, in order to build a database and create a data model using power pivot.
2- Name Tables
3- Add tables to data model (4 Marks)
4- Create necessary relationships between tables in order to connect them (8 Marks- each relationship 2 marks)
5- In Data View- power pivot, format all columns in the four tables (date, text, number without decimals, etc.
Make sure similar fields across tables have similar format. (4 Marks)
6- Calculate the following measures (calculated fields):
- Total Sales Budget (2 Marks)
- Total Marketing expenses (2 Marks)
- Net Profit (Net profit = Total Sales Budget -Total Marketing Expenses) (2 Marks)
7- Format calculated fields to show English pound (£), and no decimal places. (3 Marks - each field 1 mark)
8- Insert the following pivot tables in a new worksheet using the data model:
Note: You need to decide in which pivot field each of the following field needs to be placed.
a) Cross-tabulated pivot table shows "TerritoryID", "Net Profit", and "Year". (Name this pivot table "Net Profit"). (3 Marks)
b) Pivot table shows "TerritoryID" and "Total Sales Budget" (Name this pivot table "Sales"). (2 Marks)
c) Pivot table shows "TerritoryID" and "Total Marketing Expenses" (Name this pivot table "Expenses"). (2 Marks)
d) Pivot table shows only "Year" (Name this pivot table "Year"). (1 Mark)
9- Format all pivot tables to show as "Tabular Form", and remove "Grand Totals"
10- Given pivot table "Net Profit", insert line chart. (2 Marks)
11- Given pivot table "Sales", insert clustered chart (2 Marks)
12- Given pivot table "Expenses", insert Horizontal bar charts (2 Marks)
13- Do the following changes to the charts:
a) Hide all field buttons on the chart
b) Change chart size and properties to not move or size with cells
d) Hide Gridlines
e) Show data labels on horizonal bar chart only
f) Delete Horizontal (Value) axis, for horizontal bar chart only
g) Give a relevant dynamic title (which updates itself automatically according to specific selected value). (3 Marks - 1 mark per dynamic title)
14- Insert 3 slicers given "Year" pivot table. (3 Marks- 1 mark per slicer)
15- Connect "Year" Slicer to all pivot tables (Net Profit, Sales, and Expenses, Year) (8 Marks - 2 marks per connection)
16- Test your connections given the slicers you created and make sure everything works.
17- Create your dashboard which needs to show the following (7 Marks)
- Fill backgorund
- 3 Pivot Tables (Net Profit, Sales, Expenses) (Make sure Pivot Tables have solid color for their background, when you paste them in dashboard)
- 3 Charts
- 3 Slicers
- Unique Title (ex: Annual Report)
18- Zoom to selection (your Dashboard) and Save.
Task 3: Macros (7 Marks)
Please note: if you did not save your file in suitable excel extension, you will lose the work for this task.
1- Create 3 Macros for each of the following profitability ratios and calculate the ratios for the available 5 years (2006 : 2010)
Operating income / Total Revenues
Income Before Extra Ord Items/ Total Revenues
Net Income/ Total Revenues
2- Insert a Combo chart given the profitability ratios you generated using macros
3- Save your Excel file in a suitable excel extension to reserve your macros.
19 freelancers are bidding on average £120 for this job
Hi there, MS Excel/VBA Expert here I've read the project details & completely understand, Feel free to award will ensure work to get full marks Feel free to award Thanks
I have sound experience (5+ years) in Excel/MS Access/Data Entry and Visual Basic for Applications development and ability to learn new task quickly. I am efficient and Trustworthy.
I am professional with MBA Marketing I have Hands on experience in MS Excel, Powerpoint I am looking forward to work for your esteemed company. Sincerely Rakesh