Basic calculations of the data

Assignment Help Business Management
Reference no: EM131037014

Please help complete these two step by step questions. I have 2 hours before it is due and I need help asap! the two data files are attached and step by step instructions are below. Please HELP!

Property Records [worth 90/200 points]

  • Open the Property Records.xlsx file in the Final Exam area of Blackboard, save it to your computer or USB drive, and name it your last name Exercise 1. An example is Thompson Exercise 1.
  • Rename the Data worksheet tab Property Data.
  • Insert the title Rhode Island Properties at the top of the worksheet and the subtitle 2015 Property Records in the row beneath it.
  • Insert four blank rows between the subtitle and the data.
  • Format the title and subtitle so they appear larger and bolder, with a different color, than the rest of the text.
  • Convert the data to a table. Use a table style of your choice and name the table RI_Records.
  • Format the Parcel ID cells in the first column of the table as text so that Excel doesn't confuse them as numbers.
  • Turn on the table's Total Row and display the sum for the market value column and the count for the City State column.
  • The accounting department likes all dollar values formatted in Accounting format, so make sure the Market Value values are formatted appropriately (including in the Total Row). Decimal places are not needed.
  • Sort the data first by City State in A to Z order, then by Zip in A to Z order, then by Market Value in largest to smallest order, then by Last Name in A to Z order. In the resulting Sort Warning dialog box, sort anything that looks like a number as a number.
  • Change the column D width if necessary so that all information in that column is displayed and none of it is cut off.
  • Copy the worksheet and name the new sheet Filtered Data. Move the new worksheet to the right of the Property Data sheet.
  • In the Filtered Data worksheet, use the appropriate filter button to display only the Wakefield, RI and Saunderstown, RI properties.
  • Return to the Property Data worksheet and create a PivotTable. Allow the PivotTable to be placed in a new worksheet.
  • Name the new worksheet Average Market Values and locate it to the right of the Filtered Data worksheet. Then display the PivotTable data so that the City State information is arrayed down the left side of the table and the average of the market values for each City State is just to the right of the City State data. The PivotTable will be a very simple two-column table.
  • Format the market values to be Accounting format with no decimal places, then turn off the display of the field headers. Select a PivotTable style of your choosing.
  • Using the PivotTable, create a PivotChart. It must be a 3D clustered column chart that must be located below the PivotTable. Make sure you remove the legend in the chart since it's not necessary. Give the chart an appropriate title. Format the chart as you wish.
  • Return to the Property Data worksheet and proceed to cell A1.
  • Copy the Parcel ID header cell in A7 to cell A4. Then copy the Market Value header cell in H7 to cell B4.
  • In cell A5, create a data validation drop-down list of all Parcel ID cells. Create an appropriate input message and error alert.
  • Select a random Parcel ID in the list to verify the list works.
  • In cell B5, create a VLOOKUP formula to display the correct Market Value for the selected Parcel ID in the drop-down list. Make sure the resulting Market Value has an accounting format with no decimal places, just like the values in column H.
  • Save your work and proceed to the second exercise.

Eck-Sell Imports [worth 50/200 points]

  • Open the Eck-Sell Imports.xlsx file in the Final Exam area of Blackboard, save it to your computer or USB drive, and name it your last name Exercise 2. An example is Thompson Exercise 2.

The Orders worksheet contains annual sales data for an import business. It is currently sorted first by Order Month then by Company Name.

  • Merge and center A1 through E1. Format the text in the merged cell so it looks like a real title (larger, bolder, and colored font)
  • Convert the sales data into a table.
  • Conditionally format the order amounts so that if the amount is greater than $5,000, the cell will be highlighted with a green fill and dark green text (one of the built-in color schemes).
  • Column G currently contains text pertaining to some basic calculations of the data. Create the appropriate calculations in column H. If the results are dollar values, format the values the same way the values in column E are formatted.
  • In cell G10, type Jan. Then use the fill handle of G10 to auto-fill month names down to Dec.
  • In cell H10, create a COUNTIF function to count the number of orders placed in January. When done, do the same for the other months.
  • Type UK Bonus in cell F3. That will append a new column to the end of the table. Slightly widen the column width so that the words are totally visible. Format the cells in the column to be Currency.
  • In cell F4, create an IF function to calculate the following.

If the Country is UK, then multiply the Order Amount by 3%, else, display nothing at all.

  • In cell J3, type the words Total UK Bonus and then in K3, calculate the total UK bonuses that appear in the data table, making sure that value is also formatted as currency.
  • Save your work to your computer or USB drive, and name it your last name Exercise 2. An example is Thompson Exercise 2.

Reference no: EM131037014

Questions Cloud

Determine the magnitude of the acceleration of the pin p : The slotted arm OA forces the small pin to move in the fixed spiral guide defined by r = Kθ Arm OA starts from rest at θ = π/4 and has a constant counterclockwise angular acceleration.
Identifying any new relevant job listings : Monster.com will then search for and list all the jobs that match the criteria. Uses also have the ability to click on the link "Email me Jobs" to send users e-mails identifying any new relevant job listings. Monster.com uses __________ to send it..
Understanding of omani history and evolution : Aim To provide the student with good understanding of Omani history and evolution, people of Oman and their culture and heritage - Gain knowledge of global and regional influences on Omani evolution
Identify a specific health care policy : In a flow chart, illustrate how the health care policy became law. The flow chart should include a historical timeline from the introduction of the law to its implementation. To create the flowchart, use the "Concept Mapping and Graphing Tool." Af..
Basic calculations of the data : Insert the title Rhode Island Properties at the top of the worksheet and the subtitle 2015 Property Records in the row beneath it. Insert four blank rows between the subtitle and the data.
Explain how you evaluated the alternatives : Explain how you evaluated the alternatives using the criteria you have described. Use basis statistics, decision analysis, mathematical formulas, scenarios, and other techniques you can defend.
Categorize these theories as descriptive and explanatory : Locate three journal articles that present middle range or practice theories in the nursing literature. Categorize these theories as descriptive, explanatory, predictive, or prescriptive.
Find the magnitudes of its velocity v and acceleration a : determine the angle between the horizontal and the direction of the trajectory of the rocket and find the magnitudes of its velocity v and acceleration a.
Managerial and philosophical viewpoints : Total Quality Management (TQM) addresses organizational quality from managerial and philosophical viewpoints. It focuses on customer-driven quality standards, managerial leadership, continuous improvement, quality built into product and process de..

Reviews

Write a Review

 

Business Management Questions & Answers

  Experience or observation from working

Give an example of when an experience or observation from working with a virtual team. What benefits do you observe? What adaptations do you have to make in the teams processes?

  Explain what is meant by inter-organizational design

What are the five organizational forms of Mintzberg's framework? Provide examples of each. Define the term OD, and explain how its techniques are used to implement change.

  Step-by-step answer to organizational change

Describe in examples instances of the human implications of a major organizational change. Analyze the critical success factors for organizational change from the perspective of managing people

  Law firm mobile device security management

Case study:Law Firm Mobile Device Security Management

  Describing the effects of change on stakeholders

Tell a specific story about a change and describe the effects on you and other stakeholders. This can be as simple as how you communicate today (cell phone, email, text, twitter, etc.) versus how you communicated ten years ago (telephone, pager, f..

  What is the basis of the ftc''s power to control ads

What is the basis of the FTC's power to control ads for diet products? What ethics has the FTC established to determine if a diet product claim is unfair and deceptive?

  Leader and free rider

Explain a time when you have participated as a leader and another time when you have been a free-rider.

  Explain your choice of theory or model from the leadership

Respond to the following: Explain your choice of theory or model from the leadership literature that has been utilized to describe or explain leadership behavior in a health care setting. Discuss whether this interest emerges from your current work, ..

  Can you provide me with a description of your business

Small Business interview questions: Why are you in business? Why are in this type of business? What is your background? Education, Work Experience Can you provide me with a description of your business? How long have you been in business? In this bus..

  Deliberate scholarly detail the term social responsibility

Deliberate in scholarly detail the term social responsibility as well as find an example of a company action that was legal but not socially responsible.

  Direct relationship between supply and demand

Question 1The law of demand states that there is a direct relationship between supply and demand. True False

  Two-stage sampling design

1  A study of academic dishonesty among college students used a two-stage sampling design.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd