Closed

Do some data entry -- 2

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

- Date

- Sales_Budget

- Marketing_Expense

-TerritoryID

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)

- Year

- Quarter

- ProductID

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.

Skills: Excel

See more: what does a data entry clerk do, what do data entry clerks do, data entry job in english in pisa, visual basic for apps, data entry web researchers wanted &pound 8 hr flexible hours 20hrs+ wk, data entry web researchers wanted &pound 8 hr flexible hours 16hrs+ wk, data entry operator urdu english typing assignments, data entry openings student hire &pound 18 hour, data entry customer service up &pound 20 25k per annum, after registering home based data entry what do nextzx 1427036929161, do some data entry 2 do some data entry 2, do some data entry -- 2 do some data entry -- 2, data entry online spanish english, philippines virtual assistant data entry web development english, excel data entry work hindi english, data entry typing services english arabic

About the Employer:
( 0 reviews ) United Kingdom

Project ID: #13480826

19 freelancers are bidding on average £120 for this job

schoudhary1553

Greetings sir, i am an expert freelancer for this job and your 100% satisfaction is assured if you allow me to serve. Here is the reason. Why you should pick me? a) I am a very expert and have the same kind of ex More

£150 GBP in 2 days
(131 Reviews)
6.3
sandroshubladze

Hi I am Excel Expert and VBA Programmer I can do your task perfectly If interested, contact me Thanks & Regards

£85 GBP in 3 days
(53 Reviews)
5.6
atifdar

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

£59 GBP in 3 days
(48 Reviews)
5.2
ReflexiveData

Hi, Thanks for posting this request. I am an excel expert with an extensive knowledge on VBA and macros. I am sure I can help you with this task. Please check my portfolio for more details. Looking forward to dis More

£116 GBP in 1 day
(11 Reviews)
5.1
narsim3128

With respect to this project I would like to present myself as a candidate for your consideration. I have more than 6 years of experience. I have successfully completed few projects for US clients which involved web sc More

£111 GBP in 3 days
(20 Reviews)
3.8
fb57e1f6608abfb

I have 4+ years experience in data analytics, working on small excel reporting to big DWBI reporting work. I am an expert in developing user-friendly spreadsheets for data analysis and visualization, creating interacti More

£111 GBP in 0 days
(7 Reviews)
3.4
rafeemd

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.

£47 GBP in 3 days
(1 Review)
0.8
Niknockks

I am a hard working person. You can trust me with works. Hope for a response from you. Thank you and God bless

£150 GBP in 3 days
(0 Reviews)
0.0
studio3dvisul

Hi, I am expert in data entry, data processing &excel. I have 4 years experience in this field. if you give me the opportunity to do this work i think i will make you happy. Why choose us: Data Entry, Data Process More

£111 GBP in 2 days
(0 Reviews)
0.0
£150 GBP in 3 days
(0 Reviews)
0.0
£150 GBP in 3 days
(0 Reviews)
0.0
iqra2977

A proposal has not yet been provided

£38 GBP in 3 days
(0 Reviews)
0.0
£150 GBP in 3 days
(0 Reviews)
0.0
rockysharma733

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

£233 GBP in 3 days
(0 Reviews)
0.0
kingraz

Hello, I want to let you know that I have been working with these types of works for more than five years. I believe my skills would be ideal for your project.I can follow instructions very well and will deliver More

£150 GBP in 1 day
(0 Reviews)
0.0
findm4

A proposal has not yet been provided

£150 GBP in 3 days
(0 Reviews)
0.0
nano1479

Hello, I would like to work in your project, I have more than 5 years of experience working as data specialist (data entry), writer, translator and project manager for big companies as: Intel Corp, Thomson Reuters and More

£111 GBP in 3 days
(0 Reviews)
0.0
akuh25

A person who puts dedication in all the task given to her. Masters multi-tasking and a competitive person who finishes her work perfectly and efficiently.

£111 GBP in 5 days
(0 Reviews)
0.0
rohitnag92

Respected Recruiter, I am writing in response to the above mentioned job requirement. I have read your job details and I can assure you that I can complete the work before the mentioned time Please find below th More

£100 GBP in 3 days
(0 Reviews)
0.0