U1IP_Stats

/in /by

Course: Statistics

Unit: Using Statistics in Business

Complete the following problems in the text:

  • Problem 1.2
  • Problem 1.26
  • Problem 1.30
  • Problem 1.48
  • Problem 1.50

U1IP_Stats
1.2 Do you work hard for your money? Java professionals think they do, reporting long working hours at their jobs. Java developers from around the world were surveyed about the number of hours they work weekly. Listed here are the average number of hours worked weekly in various regions of the United States and the world. Region Hours Worked U.S 48 Northeast 47 Mid-Atlantic 49 South 47 Midwest 47 Central Mt 51 California 50 Pacific NW 47 Canada 43 Europe 48 Asia 47 South America and Africa 49 SOURCE: Jupitermedia Corporation a.How many hours do you work per week (or anticipate working after you graduate)? b.What happened to the 40-hour workweek? Does it appear to exist for the Java professional? c.Does the information in this chart make a career of being a Java professional seem attractive? 1.26 Suppose a 12-year-old asked you to explain the difference between a sample and a population. a.What information should your answer include? b.What reasons would you give him or her for why one would take a sample instead of surveying every member of the population? 1.30 A coin-operated coffee vending machine dispenses, on the average, 6 oz of coffee per cup. Can this statement be true of a vending machine that occasionally dispenses only enough to fill the cup half full (say, 4 oz)? Explain. 1.48 The election board’s voter registration list is not a census of the adult population. Explain why. 1.50 How have computers increased the usefulness of statistics to professionals such as researchers, government workers who analyze data, statistical consultants, and others?

EX 10

/in /by


Complete Exercise 10 in Statistics for Nursing Research: A Workbook for Evidence-Based Practice, and submit as directed by the instructor.


PLEASE PROVIDE RATIONALES OF EACH ANSWER

EX 10
Questions to Be Graded EXERCISE 10 Name: _______________________________________________________ Class: __________ Date: _____________________________________________________________ Follow your instructor’s directions to submit your answers to the following questions for grading. Your instructor may ask you to write your answers below and submit them as a hard copy for grading. Alternatively, your instructor may ask you to use the space below for notes and submit your answers online at http://evolve.elsevier.com/Grove/statistics/ under “Questions to Be Graded.” 1. What demographic variables were measured at the nominal level of measurement in the Oh et al. (2014) study? Provide a rationale for your answer. 2. What statistics were calculated to describe body mass index (BMI) in this study? Were these appropriate? Provide a rationale for your answer. 3. Were the distributions of scores for BMI similar for the intervention and control groups? Provide a rationale for your answer. 4. Was there a significant difference in BMI between the intervention and control groups? Provide a rationale for your answer. 5. Based on the sample size of N = 41, what frequency and percentage of the sample smoked? What frequency and percentage of the sample were non-drinkers (alcohol)? Show your calculations and round to the nearest whole percent. 6. What measurement method was used to measure the bone mineral density (BMD) for the study participants? Discuss the quality of this measurement method and document your response. 7. What statistic was calculated to determine differences between the intervention and control groups for the lumbar and femur neck BMDs? Were the groups significantly different for BMDs? 8. The researchers stated that there were no significant differences in the baseline characteristics of the intervention and control groups (see Table 2). Are these groups heterogeneous or homo-geneous at the beginning of the study? Why is this important in testing the effectiveness of the therapeutic lifestyle modification (TLM) program? 9. Oh et al. (2014, p. 296) stated that “the adherence rate to the TLM program was 99.6%.” Discuss the importance of intervention adherence, and document your response. 10. Was the sample for this study adequately described? Provide a rationale for your answer. Copyright © 2017, Elsevier Inc. All rights reserved.

In preparation for writing your report to senior management next week, conduct the following descriptive statistics analyses with Excel. Answer the questions below in your Excel sheet or in a separate

/in /by

In preparation for writing your report to senior management next week, conduct the following descriptive statistics analyses with Excel. Answer the questions below in your Excel sheet or in a separate Word document:

  • Insert a new column in the database that corresponds to “Annual Sales.” Annual Sales is the result of multiplying a restaurant’s “SqFt.” by “Sales/SqFt.”
  • Calculate the mean, standard deviation, skew, 5-number summary, and interquartile range (IQR) for each of the variables.
  • Create a box-plot for the “Annual Sales” variable. Does it look symmetric? Would you prefer the IQR instead of the standard deviation to describe this variable’s dispersion? Why?
  • Create a histogram for the “Sales/SqFt” variable. Is the distribution symmetric? If not, what is the skew? Are there any outliers? If so, which one(s)? What is the “SqFt” area of the outlier(s)? Is the outlier(s) smaller or larger than the average restaurant in the database? What can you conclude from this observation?
  • What measure of central tendency is more appropriate to describe “Sales/SqFt”? Why?

Statistics study analysis

/in /by

I.               Use the Sleep Patterns and Energy Drinks study below, and answer the following questions:

A. What sampling technique was used for the study?

B. What was the level of measurement for each type of data collected in the study?

C. What descriptive statistics were used in the study? Name them all.

D.  What hypothesis was tested in the study?

E.  What type of test was run on the data?

F.  What was the significance level for the hypothesis test?

G.  What conclusions did the researcher draw?

H.  Were the conclusions appropriate for the study?

I. What were the limitations of the study?

Sleep Patterns and Energy Drinks

A researcher decides to study the relationship between the consumption of energy drinks and the amount of sleep that college students report.  He does a survey of students at a local college where he has used a random numbering scheme to select 100 students to send the survey to. Initially his survey questions included questions about class in school—Freshman—Senior, age, marital status, class load, hours of sleep on average during the week and on the weekend, number of energy drinks consumed on week days and on the weekends.  When he refined his questions, he decided to include questions about consumption of other forms of caffeine as well—coffee, teas, and soda. He believes that students who consume energy drinks are getting less sleep than those who do not, but he decides that he needs the other caffeine data as well and decides to split his group up into three groups once he collects the data—no caffeine use, caffeine use but no energy drinks, and energy drink users (whether or not they use caffeine in other forms).

He gets 72 surveys returned and divides them up into groups according to caffeine use as indicated above with the following results:

No caffeine group n = 15, sleep per night mean = 7.23 hours, sd = 1.72 hours

Caffeine, no energy group n = 27, sleep per night mean = 7.17 hours, sd = 1.58

Energy drink group n = 30, sleep per nigh mean = 6.42, sd = 1.87

He runs a statistical comparison between the groups and finds F= 7.923, p = .032 .

He concludes that there is a significant difference between the groups at p =05 and decides to do further tests to see where the difference lies.

(STATISTICS)Expansion Strategy and Establishing a Re-Order Point. Write a 1,050-word report based on the Bell Computer Company Forecasts data set and Case Study Scenarios. DUE 8PM EASTERN MONDAY 5/15

/in /by

This assignment has two cases. The first case is on expansion strategy. Managers constantly have to make decisions under uncertainty. This assignment gives students an opportunity to use the mean and standard deviation of probability distributions to make a decision on expansion strategy. The second case is on determining at which point a manager should re-order a printer so he or she doesn’t run out-of-stock. The second case uses normal distribution. The first case demonstrates application of statistics in finance and the second case demonstrates application of statistics in operations management.


Assignment Steps


Resources:

Microsoft Excel®, Bell Computer Company Forecasts data set, Case Study Scenarios


Write

a 1,050-word report based on the Bell Computer Company Forecasts data set and Case Study Scenarios.


Include

answers to the following:

Case 1: Bell Computer Company

  • Compute the expected value for the profit associated with the two expansion alternatives. Which decision is preferred for the objective of maximizing the expected profit?
  • Compute the variation for the profit associated with the two expansion alternatives. Which decision is preferred for the objective of minimizing the risk or uncertainty?

Case 2: Kyle Bits and Bytes

  • What should be the re-order point? How many HP laser printers should he have in stock when he re-orders from the manufacturer?


Format

your assignment consistent with APA format.


Grading Guide


Content

Met

Partially Met

Not Met

Comments:

Write a 1,050-word report based on the Bell Computer Company Forecasts data set and Case Study Scenarios.

Case 1: Bell Computer Company

·       Compute the expected value for the profit associated with the two expansion alternatives. Which decision is preferred for the objective of maximizing the expected profit?

Compute the variation for the profit associated with the two expansion alternatives. Which decision is preferred for the objective of minimizing the risk or uncertainty?

Case 2: Kyle Bits and Bytes

·       What should be the re-order point? How many HP laser printers should he have in stock when he re-orders from the manufacturer?

3

#/3


Writing Guidelines

Met

Partially Met

Not Met

Comments:

The paper—including tables and graphs, headings, title page, and reference page—is consistent with APA formatting guidelines and meets course-level requirements.

Intellectual property is recognized with in-text citations and a reference page.

Paragraph and sentence transitions are present, logical, and maintain the flow throughout the paper.

Sentences are complete, clear, and concise.

Rules of grammar and usage are followed including spelling and punctuation.


Total Available


Total Earned

2

#/2


Assignment Total


#


5


#/5

Additional comments:

(STATISTICS)Expansion Strategy and Establishing a Re-Order Point. Write a 1,050-word report based on the Bell Computer Company Forecasts data set and Case Study Scenarios. DUE 8PM EASTERN MONDAY 5/15
Case Study – Week 3 Individual Assignment QNT/561 Version 9 Case Study – Bell Computer Company The Bell Computer Company is considering a plant expansion enabling the company to begin production of a new computer product. You have obtained your MBA from the University of Phoenix and, as a vice-president, you must determine whether to make the expansion a medium- or large- scale project. The demand for the new product involves an uncertainty, which for planning purposes may be low demand, medium demand, or high demand. The probability estimates for the demands are 0.20, 0.50, and 0.30, respectively. Case Study – Kyle Bits and Bytes Kyle Bits and Bytes, a retailer of computing products sells a variety of computer-related products. One of Kyle’s most popular products is an HP laser printer. The average weekly demand is 200 units. Lead time (lead time is defined as the amount of time between when the order is placed and when it is delivered) for a new order from the manufacturer to arrive is one week. If the demand for printers were constant, the retailer would re-order when there were exactly 200 printers in inventory. However, Kyle learned demand is a random variable in his Operations Management class. An analysis of previous weeks reveals the weekly demand standard deviation is 30. Kyle knows if a customer wants to buy an HP laser printer but he has none available, he will lose that sale, plus possibly additional sales. He wants the probability of running short (stock-out) in any week to be no more than 6%. Copyright © 2017 by University of Phoenix. All rights reserved.

The Logic of MANOVA

/in /by


The Logic of MANOVA


Resources

  • Multivariate Statistics Discussion Scoring Guide.

In this discussion, the logic of MANOVA will be discussed. Complete instructions are attached. Please cite all references.

The Logic of MANOVA
DIRECTIONS: Discuss the bulleted aspects below, cite references The Logic of MANOVA . Discuss the logic of the MANOVA. Why would a researcher use MANOVA instead of running several separate analyses of variance? Mention specific advantages and disadvantages in doing so.

Assignment: Analyzing SPSS (PASW) Software: Part 2

/in /by


Assignment: Analyzing SPSS (PASW) Software: Part 2

For this assignment you use SPSS (PASW) software and learn to properly munipulate data according the APA requirements. This is an important skill and will be a major factor in future assignments in this course, your doctoral studies and dissertation. It is strongly encouraged that you review Chapter 5 of the APA Publication Manual to understand table and figure requirements before starting.

Follow the directions for using SPSS (PASW) in this assignment:

You will then write a 5-6 page paper in which you present your table and an analysis of your findings. Keep in mind that you cannot draw conclusions without further testing. Instead identify notable trends, patterns, relationships, associations, etc. Your paper must meet the following requirements.

Assignment: Analyzing SPSS (PASW) Software: Part 2
Week 2 Application Directions Open the Main Dataset file located in Resources and use the File > Open > Data function to import the data into PASW, making sure to import only the range from A1 to J52 on the Excel worksheet. Add an ordinal variable to the PASW worksheet titled EXPERIENCE, with three classifications Low (0 to 5 years), Medium (6 to 10 years) and High (11+ years). Enter Low for supervisors BOS-1, BOS-4, BOS-7, BOS-10, BOS-13, PHO-1, PHO-4, PHO- 7, PHO-10, PHO-13, PHO-16, PHO-19, SEA-1, SEA-4, SEA-7, SEA-10, SEA-13, and SEA- 16. Use Medium for supervisors BOS-2, BOS-5, BOS-8, BOS-11, BOS-14, PHO-2, PHO-5, PHO-8, PHO-11, PHO-14, PHO-17, SEA-2, SEA-5, SEA-8, SEA-11, SEA-14, and SEA-17. Use High for BOS-3, BOS-6, BOS-9, BOS-12, BOS-15, PHO-3, PHO-6, PHO-9, PHO-12, PHO-15, PHO-18, SEA-3, SEA-6, SEA-9, SEA-12, and SEA-15. Ensure all variables are designated as numeric, except for Site, Supervisor, SupervisorGender, Experience and Operations, which are string variables. Save the file to your computer under a unique name, ensuring that Site (width = 7; Nominal), Supervisor (width = 6; Nominal), Experience (width = 8; Ordinal), NumEmps (width = 11; Scale), HoursWorked (width = 11; Scale), SupervisorGender (width =6; Nominal), PerSafeBeh (width = 11; Scale), InjuryRate (width = 13; Scale), Safety Climate (width = 11; Scale), Risk (width = 11; Nominal) and Operations (width = 50; Nominal). Al variables are String, except for the Scale variables which are numeric. Open the file and ensure all data are included on the PASW file. Eliminate extraneous variables, usually in the columns led or headed by terms like VAR00011, VAR00012, etc.

Statistics Assignment

/in /by

Using the information supplied in the attachement, determine the expected amount of time it will take George to travel from Washington, DC to his sister’s house, employing both the I-95 and alternate route. SHOW YOUR WORK, DEMONSRATING HOW YOU ARRIVED AT THE ANSWERS YOU PROVIDE

Statistics Assignment
George’s Thanksgiving Trip George is invited by his sister, Dorothy, to attend a family reunion during the Thanksgiving weekend. Dorothy lives in Denver, NY, about 90 miles northeast of New York City. George lives in Washington, DC, about 215 miles south of New York City. George decides to visit Dorothy and to travel to her place by car. The only problem is that road traffic during the Thanksgiving holidays is terrible along the East Coast of the United States. George would normally travel to Dorothy’s house by taking Interstate Highway 95. This is the major link connecting Washington and New York City. However, during Thanksgiving, the traffic on I-95 is usually bad, leading to major delays. George decides to explore an alternate route to traveling to Dorothy’s. This route would be a few miles longer. Also, he would encounter a 60 mile segment of road in a rural area, and he would have to travel slowly on this segment. The good feature about the alternate route is that it is unlikely to suffer from Thanksgiving traffic. A map showing the two routes to Dorothy’s house is offered in Figure 1. Based on his experience in traveling along I-95 during Thanksgiving holidays, George has developed a good sense of the likelihood of delays that he can encounter on the journey. Table 1 shows the probability distributions he has created for all the segments of his trip to Dorothy for both the I-95 route and the alternate route. Assignment Using the information supplied in Figure 1 and Table 1, determine the expected amount of time it will take George to travel from Washington, DC to his sister’s house, employing both the I-95 and alternate route. SHOW YOUR WORK, DEMONSRATING HOW YOU ARRIVED AT THE ANSWERS YOU PROVIDE. Probability Distributions for Travel Times on Journey Regular Route (East Route) Probability achieving schedule Probability 10% longer than schedule Probability 20% longer than schedule Probability 30% longer than schedule Probability 40% longer than schedule Segment Washington-Baltimore 0.7 0.3 0.0 0.0 0.0 Baltimore-New York City 0.0 0.1 0.2 0.5 0.2 New York City-Kingston 0.1 0.2 0.3 0.3 0.1 Kingston-Sister’s Home 0.8 0.2 0.0 0.0 0.0 Alternate Route (West Route) Probability of achieving schedule Probability 10% longer than schedule Probability 20% longer than schedule Probability 30% longer than schedule Probability 40% longer than schedule Segment Washington-Baltimore 0.7 0.3 0.0 0.0 0.0 Baltimore-Binghamton 0.9 0.1 0.0 0.0 0.0 Binghamton-E Branch 0.9 0.1 0.0 0.0 0.0 E Branch-Sister’s Home 0.8 0.2 0.0 0.0 0.0 3

numerical analysis

/in /by


Using the attached information/data: Write a 2- to 3-page analysis of your multiple regression using dummy variables results for each research question. In your analysis, display the data for the output. Based on your results, provide an explanation of what the implications of social change might be.

numerical analysis
Research Question: Rs occupational prestige score is the dependent variable. My independent variables are highest year of school completed, respondent age, and a dummy variable of American citizenship categorized as 1 (if yes) and 2 (if no) and 0 (if otherwise). Are the highest year of school completed, respondent age and dummy variable significant for predicting Rs occupational prestige score? Also what is the effect on Rs occupational prestige score with a unit increase in highest year of school completed, respondent age, and American citizenship? Coefficients significance and interpretation With p-value less than alpha (0.05), highest year of school completed and coefficient of respondent age. But with p-value greater than alpha (0.05) yes and no are not significant. With one unit increase in highest year of school completed, there is 2.324 units increase in Rs occupational prestige score. With one unit increase in respondent age, there is 0.112 units increase in Rs occupational prestige score. Rs occupational prestige score is .335 units less on an average for American citizens as compared to that of others. And Rs occupational prestige score is 2.487 units more on an average for American citizens as compared to that of others Assumptions From the table of correlations I observe that there is no correlation between independent variables. Therefore there is no problem of multicollinearity and the assumption of independence of variables is satisfied. All correlations lie in the interval [-.3, 3] implying no weak linear relationship or no correlation. From the graph of residual I observe that residuals are normally distributed. This implies that the assumption normality of residuals is also satisfied. From the graph of regression standardized residual the constant variance is observed. This implies that assumption of homogeneity of variance is satisfied. Thus all assumptions of regression analysis are satisfied. Output Correlations Rs occupational prestige score (2010) AGE OF RESPONDENT HIGHEST YEAR OF SCHOOL COMPLETED yes no Rs occupational prestige score (2010) Pearson Correlation 1 .126** .514** .028 -.077** Sig. (2-tailed) .000 .000 .165 .000 2427 2421 2426 2427 2427 AGE OF RESPONDENT Pearson Correlation .126** 1 -.014 .036 -.083** Sig. (2-tailed) .000 .479 .068 .000 2421 2529 2528 2529 2529 HIGHEST YEAR OF SCHOOL COMPLETED Pearson Correlation .514** -.014 1 .088** -.201** Sig. (2-tailed) .000 .479 .000 .000 2426 2528 2537 2537 2537 yes Pearson Correlation .028 .036 .088** 1 -.172** Sig. (2-tailed) .165 .068 .000 .000 2427 2529 2537 5054 5054 no Pearson Correlation -.077** -.083** -.201** -.172** 1 Sig. (2-tailed) .000 .000 .000 .000 2427 2529 2537 5054 5054 **. Correlation is significant at the 0.01 level (2-tailed). Variables Entered/Removeda Model Variables Entered Variables Removed Method no, AGE OF RESPONDENT, yes, HIGHEST YEAR OF SCHOOL COMPLETEDb . Enter a. Dependent Variable: Rs occupational prestige score (2010) b. All requested variables entered. Model Summaryb Model R R Square Adjusted R Square Std. Error of the Estimate .534a .285 .284 11.440 a. Predictors: (Constant), no, AGE OF RESPONDENT, yes, HIGHEST YEAR OF SCHOOL COMPLETED b. Dependent Variable: Rs occupational prestige score (2010) ANOVAa Model Sum of Squares df Mean Square F Sig. Regression 126088.636 4 31522.159 240.867 .000b Residual 316050.099 2415 130.870 Total 442138.735 2419 a. Dependent Variable: Rs occupational prestige score (2010) b. Predictors: (Constant), no, AGE OF RESPONDENT, yes, HIGHEST YEAR OF SCHOOL COMPLETED Coefficientsa Model Unstandardized Coefficients Standardized Coefficients t Sig. B Std. Error Beta (Constant) 6.194 1.326 4.671 .000 AGE OF RESPONDENT .112 .014 .143 8.277 .000 HIGHEST YEAR OF SCHOOL COMPLETED 2.324 .078 .525 29.896 .000 yes -.335 .473 -.012 -.709 .478 no 2.487 1.397 .032 1.780 .075 a. Dependent Variable: Rs occupational prestige score (2010) Residuals Statisticsa Minimum Maximum Mean Std. Deviation N Predicted Value 10.02 62.23 43.65 7.220 2420 Residual -40.393 40.169 .000 11.430 2420 Std. Predicted Value -4.659 2.573 .000 1.000 2420 Std. Residual -3.531 3.511 .000 .999 2420 a. Dependent Variable: Rs occupational prestige score (2010) Frankfort-Nachmias, C., & Leon-Guerrero, A. (2015). Social statistics for a diverse society (7th ed.). Thousand Oaks, CA: Sage Publications.  Wagner, W. E. (2016). Using IBM® SPSS® statistics for research methods and social science statistics (6th ed.). Thousand Oaks, CA: Sage Publications.

Hello, I need help solving this excel problem for business analysis. If I could have it done by 10pm tonight so I could review it that would be great. Thank you!

/in /by

Hello, I need help solving this excel problem for business analysis. If I could have it done  by 10pm tonight so I could review it that would be great. Thank you!

Hello, I need help solving this excel problem for business analysis. If I could have it done by 10pm tonight so I could review it that would be great. Thank you!
Grader – Instructions Excel 2019 Project Exp19_Excel_Ch07_Cap_Real_Estate Project Description: You are the office manager for a real estate company in northern Utah County. You tracked real estate listings, including city, agent, listing price, sold price, etc. Agents can represent a seller, a buyer, or both (known as dual agents). Your assistant prepared the spreadsheet structure with agent names, agent types, the listing and sold prices, and the listing and sold dates. You want to complete the spreadsheet by calculating the number of days each house was on the market before being sold, agent commissions, and bonuses. In addition, you will use conditional functions to calculate summary statistics. For further analysis, you will insert a map chart to indicate the average house selling price by city. Finally, you will create a partial loan amortization table and calculate cumulative interest and principal to show a potential buyer to help the buyer make decisions. Steps to Perform: Step Instructions Points Possible Start Excel. Download and open the file named Exp19_Excel_Ch07_Cap_RealEstate.xlsx. Grader has automatically added your last name to the beginning of the filename. The spreadsheet contains codes (BA, DA, SA) to represent agent roles (Buyer’s Agent, Dual Agent, Seller’s Agent). You want to switch the codes for the actual descriptions.In cell E12 of the Details sheet, insert the SWITCH function to evaluate the agent code in cell D12. Include mixed cell references to the codes and roles in the range J2:K4 for the valuesand results arguments. use all cell references in the function. Copy the function to the range E13:E39. Now you want to calculate the number of days between the list date and sale date.In cell J12, insert the DAYS function to calculate the number of days between the Listing Date and the Sale Date. Copy the function to the range J13:J39. You want to calculate agent commissions based on their role.In cell K12, insert the IFS function to calculate the agent’s commission based on the agent code and the applicable rates in the range L2:L4. Use relative and mixed references correctly. Copy the function to the range K13:K39. You want to calculate a bonus if the sold price was at least equal to the listing price, and if the house sold within 30 days after being listed.In cell L12, insert an IF function with a nested AND function to calculate a bonus. The AND function should ensure both conditions are met: Sold Price divided by the Listing Price is greater than or equal to 100% (cell L7) and the Days on Market are less than or equal to 30 (cell L8). If both conditions are met, the bonus is $1,000 (cell L9). Otherwise, the bonus is $0. Use mixed cell references to the input values in the range L7:L9. Copy the function to the range L12:L39. The top-left section of the spreadsheet is designed for summary statistics for one condition. You will calculate average selling prices and the number of houses sold in each city (the condition).In cell B2, insert the AVERAGEIF function to calculate the average Sold Price for houses in the city of Alpine. Use mixed references for the range; use a relative reference to cell A2. Copy the function and use the Paste Formulas option to paste the function in the range B3:B5 so that the bottom border in cell B5 is preserved. You want to count the number of houses in one city.In cell C2, insert the COUNTIF function to count the number of houses in the city of Alpine. Use mixed references for the range; and use a relative reference to cell A2. Copy the function and use the Paste Formulas option to paste the function in the range C3:C5 so that the border in cell C5 is preserved. You want to calculate the total commissions for each agent (the condition).In cell B7, insert the SUMIF function to total the commissions by agent. Use mixed references for the ranges; and use a relative reference to cell A7. Copy the function and use the Paste Formulas option to paste the function in the range B8:B9 so that the borders are preserved. The top-middle section of the spreadsheet is designed for summary statistics for multiple conditions. You will calculate the number of houses sold for each agent when he or she served as a Dual Agent (DA). Use mixed references for ranges and the agent code condition in cell J3. Use relative cell references to the agent condition in cell E2. When you copy the formulas, use the paste Formulas options to preserve border formatting.In cell F2, insert the COUNTIFS function in cell F2 to count the number of houses sold by the first agent (cell E2) who was a Dual Agent (DA) (J3) for that house. Use all cell references in the function. Copy the function to the range F3:F4 and preserve the bottom border for cell F4. 10 You are ready to calculate the total value of those houses for each agent when he or she served as a Dual Agent (DA). Use mixed references for ranges and the agent code condition in cell J3. Use relative cell references to the agent condition in cell E2. When you copy the formulas, use the paste Formulas options to preserve border formatting.In cell G2, insert the SUMIFS function to sum the selling prices of the houses sold by the first agent (cell E2) who was a Dual Agent (DA) (J3) for that house. Copy the function to the range G3:G4 and preserve the bottom border for cell G4. 11 Now, you will calculate the highest-price house highest-price house sold for each agent when he or she served as a Dual Agent (DA). Use mixed references for ranges and the agent code condition in cell J3. Use relative cell references to the agent condition in cell E2. When you copy the formulas, use the paste Formulas options to preserve border formatting.In cell H2, insert the MAXIFS function in cell H2 to display the highest-price house sold by the first agent (cell E2) who was a Dual Agent (DA) (J3) for that house. Copy the function to the range H3:H4 and preserve the borders in the range H3:H4. 12 The Map worksheet contains a list of cities, postal codes, and average house sales. You will insert a map chart to depict the averages visually using the default gradient fill colors.Display the Map worksheet, select the range B1:C5 and insert a map chart. 13 Cut the map chart and paste it in cell A7. Set a 2.31″ height and 3.62″ width. 14 You want to enter a meaningful title for the map.Change the map title to Average Selling Price by Zip Code. 15 Display the Format Data Series task pane, select the option to display only regions with data, and show all labels. Close the task pane. 16 You are ready to start completing the loan amortization table.Display the Loan worksheet. In cell B8, type a reference formula to cell B1. The balance before the first payment is identical to the loan amount. Do not type the value; use the cell reference instead. In cell B9, subtract the principal from the beginning balance on the previous row. Copy the formula to the range B10:B19. 17 Now, you will calculate the interest for the first payment.In cell C8, calculate the interest for the first payment using the IPMT function. Copy the function to the range C9:C19. 18 Next, you will calculate the principal paid.In cell D8, calculate the principal paid for the first payment using the PPMT function. Copy thefunction to the range D9:D19. 19 Rows 21-23 contain a summary section for cumulative totals after the first year.In cell B22, insert the CUMIPMT function that calculates the cumulative interest after the first year. Use references to cells A8 and A19 for the period arguments. Be sure the function returns a positive value. 20 The next summary statistic will calculate the principal paid after the first year.In cell B23, insert the CUMPRINC function that calculates the cumulative principal paid after the first year. Use references to cells A8 and A19 for the period arguments. Be sure the function returns a positive value. 21 Rows 25-28 contain a section for what-if analysis. In cell B27, use the RATE financial function to calculate the periodic rate using $1,400 as themonthly payment (cell B26), the NPER, and loan amount in the original input section. 22 In cell B28, calculate the APR by multiplying the monthly rate (cell B27) by 12. 23 Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of each worksheet. 24 Save and close Exp19_Excel_Ch07_Cap_RealEstate.xlsx. Exit Excel. Submit the file as directed. Total Points 100 Created On: 06/22/2020 3 Exp19_Excel_Ch07_Cap – Real Estate 1.2