代写MGTSC212 Fall 2024 Lab Assignment 2代写C/C++编程

MGTSC212

Fall 2024

Lab Assignment 2

Due by 11:59 PM, October 11

Perform all calculations using Excel. For any calculation, no other source of answers requiring calculations will be permitted. All the answers should automatically appear in the Answer worksheet unless otherwise specified. After calculating answers on the designated worksheet for each problem, if they do not automatically appear in the Answer sheet, Copy and Paste 123 (Values) to the Answer sheet without rounding. Do not type values into the Answer sheet that you calculate elsewhere. Rounded numbers are errors. Only the answers given in the shaded cells will be graded*. Answer all of the following questions as Excel formulas, not numbers (i.e. =sum(A2:A40) instead of the result/number only, unless otherwise specified).

Do not delete the embedded formulas provided within the shaded cells of the Answer sheet (they are very important for grading your file correctly). The only exception is if your calculated answers from the respective worksheet do not transfer correctly and you need to copy-paste the answer as values. Only the Answer sheet will be graded for Q4, Q5, Q6 and Q7.

[3 points] Add your student ID and name in the designated shaded cells in the Answer sheet.

Questions

1.   [2 points] Within the Rand worksheet, propagate the formula from B3 to B4. Copy the value from B4 as

Paste Special in cell C7 of the Answer worksheet [paste as values 123, slide #45 from Lab-cycle1_data_summary]. Note that, failure to fulfill the instructions for the Rand portion, as outlined above, will result in a 20% deduction from your earned marks for Lab Assignment 2.

2.   [17 points] The Q2 worksheet contains sample data on housing Monthly mortgage and related variables, such as Feats (number of features), SqFt (Size of the house), and estimated Tax.

a.    [5+1 points] Create a pivot table with Monthly mortgage ranges as rows and Features as columns.

Use the following ranges for Monthly mortgage: 500 - 699, 700 - 899, ..., 2100 - 2299 (i.e. width = 200). The Pivot Table should have a similar layout and provide the same type of information as the frequency table within the Q2 sheet. Create the pivot table within the same worksheet, no need to transfer it to the Answer sheet (we will check the Q1 worksheet for the pivot table). Replace the blank cells within the pivot table with 0.

b.   [2 points] What percentage of houses have 4 features and a monthly mortgage in the range of $700 - $899?

c.    [5 points] Creating frequency distribution and histogram for Tax. Provide the frequencies in the shaded cells between T3 and T13. You can use either the COUNTIF function or the Histogram tool from the Data Analysis Tool pack. [Hint. First frequency count using the COUNTIF function is provided]

d.   [3 +1 points] Create a histogram based on the frequency distribution of Tax. 1 point for adding the axes titles. You can place the histogram anywhere within the Q1 worksheet. Make sure you do not delete any cells within this worksheet.

3.   [15 points] The Q3 worksheet provides the frequency distribution of three different tax ranges and no. of

features for different houses. Based on this information answer the following questions and use cell reference and Excel functions only to answer all questions (0 will be given for hard-coded numbers):

a.    [2 points] Complete the row and column totals for the Frequency table.

b.   [6 points] Convert the frequency table into the joint probability table using cell reference. Round each number to 4 decimal places. [Do not use the ‘Decrease Decimal’ place option from the Home tab]

c.    [1 point] What is the probability that a randomly chosen house will have four features?

d.   [2 points] What is the probability that a randomly chosen house needs to pay at least $700 tax per year?

e.    [2 points] What is the probability that a randomly chosen house will have at most four features?

f.    [2 points] What is the probability that a randomly chosen house with three features will be in the tax range of $700 – $999?

4.   [10 points] The Q4 worksheet contains randomly selected samples of countries over a couple of years

indicating whether the annual Military Expenditure (% of GDP) for a country is more than 2%. Calculate the sample proportion (p-) of the countries having an annual Military Expenditure (% of GDP) of more than 2%. Fill in the shaded cells to find a 99% confidence interval for the population proportion of countries having an  annual Military Expenditure (% of GDP) of more than 2% (P). Use cell reference and Excel formula (only) to fill in the shaded cells to complete the CI calculations. Don’t round any numbers, rounded numbers will be considered 0. You can type the interpretation.

5.   [6 points] The Q5 worksheet contains randomly selected samples of countries with data on net Energy

imports (% of energy use). Calculate the 90% confidence interval for the population average of net Energy imports from all countries around the world. Assume the net energy imports across countries follow a Normal distribution. Use cell reference and Excel formula (only) to fill in the shaded cells to complete the CI calculations. Don’t round any numbers, rounded numbers will be considered 0.

6.   [8 points] TSA Security Line Wait Times. According to the U.S. Transportation Security Administration (TSA), 2% of the 771,556,886 travelers who utilized 440 federalized airports in 2017 waited more than 20   minutes in the TSA security line. The Q6 worksheet contains TSA waiting times data for a sample of 5000 passengers from a major US airport. Use these data to test the hypothesis that the proportion of travelers waiting more than 20 minutes in TSA security lines at this airport is the same as the national proportion at  α = 0.05. Use cell reference and Excel formula (only) to fill in the shaded cells to complete the hypothesis test (HT) calculations. Don’t round any numbers, rounded numbers will be considered 0.

7.   [5 points] The Q7 worksheet contains selective summary statistics for different variables. Read the data description within the worksheet and answer the listed questions accordingly.

Save your file as a .xlsx file. Please make sure all your answers appear on the Answer sheet. Close all existing Excel files on your computer, upload your Excel file to UofA_Online-Assessment (Choose File > Open) → Submit the Assignment. IF YOU DO NOT CLICK, “Submit Assignment”, your Assignment cannot be graded. After submitting, close all the existing Excel files on your computer. Download your submission, open it, and make sure that the file you submitted is the one you intended. If it is a different file (e.g., ablank one), then resubmit your assignment from the desired location again following the steps mentioned above. Your file will be graded as submitted (for example, if you submit a blank file or a shadow file, you will get a zero on this assignment). You can submit it multiple times before the deadline. Only your latest submission will be graded.