Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

How to build a static consolidation in excel

26/03/2021 Client: saad24vbs Deadline: 2 Day

Excel Project 3 Instructions:
Part 1: Functions covered in this exercise: Grouping & formatting worksheets, create static
data consolidation with SUM and AVERAGE functions and create and edit hyperlinks.

Open the Project 3 workbook in Excel. Follow the directions below.
1. Group the first three worksheets (San Diego, La Jolla and Carlsbad).
2. Edit and format grouped worksheets.
a. In A28, type First Quarter, 2015 in bold font.
b. Merge and center across cells A1:B1. Merge and enter cells A2:B2.
c. Edit A10 to read Cash paid for publicity.
d. Ungroup sheets.
3. Click the Summary sheet tab.
4. Consolidate the case received amounts in the Cash flow from operations section.
a. Select cells B4:B6.
b. Use Sum to consolidate the static data from the three location sheets without links to
the source data.
5. Consolidate the cash paid amounts in the Cash flow from operations section.
a. Select cells B7:B12.
b. Use Sum to consolidate the static cash paid amounts on the Summary sheet.
6. In the Cash flow from banking and investment section on the Summary sheet, use Sum to
consolidate the static cash received amounts in cells B15:B19 without links to the source data.
7. Consolidate the cash paid amounts for the CashFlow from banking and investment section on
the Summary sheet.
a. Use cells B20:B21 for the static consolidation.
b. In the message that no data was consolidated, click OK.
8. Use Sum to consolidate the cash balance at the beginning of the quarter amounts in B24 on
the Summary sheet.
9. Insert a hyperlink on the Summary worksheet.
a. Click cell D3 on the Summary worksheet.
b. Create a hyperlink that displays San Diego Data and switches to the San Diego
worksheet
10. Copy and edit a hyperlink.
a. Right-click the hyperlink in cell D3 and choose Copy from the menu.
b. Paste the hyperlink in cell D5.
c. Right-click cell D5 and choose Edit Hyperlink from the menu. 2

d. Edit the hyperlink to read La Jolla Data and to switch to the La Jolla worksheet.
e. Right-click the La Jolla Hyperlink and choose Orange, Accent 6, Darker 25% as the
Font Color from the mini toolbar.
f. Copy either hyperlink to create a third hyperlink for the Carlsbad sheet in cell D7. Use
Purple, Accent 4, Darker 25% as the font color.
g. Test the hyperlinks.
11. Continue work on the next exercise in the workbook.

Part 2: Functions covered in this exercise: Nested IF, COUNTIFS, MATCH and INDEX

Click in Refurbish Sheet and follow the directions below.
1. Nest INDEX and MATCH functions in N3 on the Refurbish tab to determine the list price of cars
based on the Stock No. number in N2.
a. Select N2 and type the number 125.
b. Click cell N3 and enter the INDEX function.
c. Select A3:K15 for the array argument.
d. Use MATCH(N2,B3:B15,0) for the MATCH function row_num argument.
e. Use MATCH(“List Price”,A3:K3,0) for the MATCH function column_num argument. The
result is $11,800.00
f. Click N2 and type 127. The result changes to $22,000.
2. Use typing to create another nested INDEX and MATCH function in cell N4 to determine the
Compression based on the Stock No. number in N2.
a. Use the same argument ranges listed in step 2
b. Hint: use absolute reference symbols to the original function as required; then copy the
formula to N4 for easier editing.
c. Apply Comma formatting to the result in N4. The Compression for Stock No. number 127 is
3,000.
3. Nest an AND and an IF function in A4 to determine if you refurbish a car based on the number of
Cylinders, Valves and list price in E4, F4 and I4, respectively.
a. Select A4 on the Refurbish tab.
b. Use E4=N12, F4=N13, and I4c. For the IF function, type “Yes” for value_if_true argument and “No” for value_if_false
argument.
d. Edit the formula to include absolute symbols to all references necessary within the formula.
e. Copy the formula in cell A4 to cells A5:A15 without formats. Center align the column to
show the results. The result in A4 is No.
4. Apply the AVERAGEIFS function to cell N7 on the Refurbish tab to determine the average list
price for car on more than .75 size containing more than two Cylinders. 3

a. For the Average_range argument, select cells I4:I15.
b. For the Criteria_range1 box, select cells K4:K15 for sizes.
c. For the Criteria1 box, type >.75
d. For the Criteria_range2 box, select cells E4:E15 for the number of Cylinders.
e. For the Criteria2 box, type >2. The result is $18,600.00.
5. Copy the AVERAGEIFS function in cell N7 to N9 on the Refurbish tab to determine the average
list price for a car on less than .75 size containing more than two Cylinders.
a. Apply absolute references as needed in the AVERAGEIFS in cell N7 and copy it to N9.
b. Edit the formula in N9 so the Criteria1 is “<.75”. The result is $11,866.67.
6. Apply COUNTIFS to cell N8 on the Refurbish tab to determine the number of cars less than four
engine with over 2000 compression.
a. For the Criteria_range1 argument, select cells G4:G15 for the Compression.
b. For the Criteria1 box, type >2000.
c. For the Criteria_range2 box, select cells H4:H15.
d. For the Criteria2 box, type <4. The function result is 3.00.
7. Save and upload your Project 3 workbook to Blackboard.

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

Top Grade Essay
Financial Assignments
Solutions Store
Helping Hand
Helping Engineer
Unique Academic Solutions
Writer Writer Name Offer Chat
Top Grade Essay

ONLINE

Top Grade Essay

I have read and understood all your initial requirements, and I am very professional in this task.

$20 Chat With Writer
Financial Assignments

ONLINE

Financial Assignments

I have read and understood all your initial requirements, and I am very professional in this task.

$15 Chat With Writer
Solutions Store

ONLINE

Solutions Store

I have read and understood all your initial requirements, and I am very professional in this task.

$35 Chat With Writer
Helping Hand

ONLINE

Helping Hand

Give me a chance, i will do this with my best efforts

$19 Chat With Writer
Helping Engineer

ONLINE

Helping Engineer

I have read your project details. I can do this within your deadline.

$24 Chat With Writer
Unique Academic Solutions

ONLINE

Unique Academic Solutions

I have read your project details. I can do this within your deadline.

$58 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

Bishop martin primary school - Monopolies are socially inefficient because the price they charge is - Cjt 202 - Explore science working scientifically - Reviewing an Econ 101 assignment - AVD Raj A - Affidavit of undertaking philippines - Aerospace engineering course list - Slp 3 537 - Daimler annual report 2012 - 6.11 km in miles - MGT - Sylvia plath youtube - Art quiz for students - Appealing centrelink decisions fact sheet - Australia 842 form 2021 - Oso hotwater indirect 20ri - Serum1111 - The road to hell case study - Well researched paper needed - Facilities management career path singapore - The house on mango street questions - Slip and drive duct connection - How to create a cost benefit analysis in excel - Stage 4 - 3.00 x 10 8 - Discussion - Tegumentario - Journal assignment - St john dress 14 - Bristol dental hospital referrals - Ncra accredited formal education certificate program - History teacher interview questions - Sn2 reaction 1 bromobutane - Maladaptive coping actions used by disaster survivors include - Uob bank transaction code - Eduqas physics a level - Cert ii in electrotechnology - HW - 2pg Case Analysis Worksheet (Outline)- NEED 2PM CST 8/12 - World socialist web site - 150 lashes dan murphy - It’s crucial that cost variances and inefficiencies be identified early so that - Periodic table jokes dirty - 12 dbm to mw - Billy kratzert net worth - Characteristics of news report - 180 ohm resistor color code - Ac 550 midterm - Configure pat on cisco router - Business Intellegence - Bbc languages spanish games - Coca cola labor practices - Beijing time gmt 8 - Qualitative analysis of organic compounds pdf - Romeo and juliet plot - A widely used disaster recovery approach includes - Help 10 pages excluding citations - 116 brolga road beaudesert - Katy perry firework figurative language - Texting and driving persuasive speech outline - Integrated marketing communication imc campaigns typically involve ______ - What is an example of a physical contaminant servsafe - Swot analysis of singapore - Which of the following accounts belongs on the asset side of a balance sheet? - Examples of why english is a crazy language - Half round concrete culvert pipe - Circuit construction kit virtual lab - How to calculate retained earnings in consolidation - Zinc lead ii acetate balanced equation - Prepare income statements for each year using absorption costing - Managing Team work and stress - Usq withdraw without academic penalty - Orthopedic nursing skills checklist - CMGT - Writing apus style guides - Write an eight sentence paragraph that fully develops the topic - Cfitrainer.net login - Mitsubishi heavy industries diesel generators - Discussion - Notes from a young black chief - Haydn enjoyed phenomenal musical success with two trips to - Costco keeps selling treats from china despite dog death - What is mondelez international's corporate strategy - 2000 Words Essay 30 Hrs U Need Decide A Topic And Find A Small Company By Urself And Write The Essay - Module 2 discussion - Does duro tuss contain paracetamol - Which of the following is equivalent to the expression below - Bletchley to kingston milton keynes bus - I need (3000 words) on International Human Resource - Ancient egypt hierarchy ks2 - Acc 291 week 2 wileyplus answers - Compare and contrast the tax outcomes for the sole trader, - Assessment 2: - A frequency polygon is graphed using - How does elizabeth affect victor and henry - Subjective frame visual arts - Sei strategies in a siop lesson plan - Essay - European patent office munich jobs