Questions
Series Legend Input Area Net Sales CE OR TB Royalty Rate: 7.3% Average Certification Series Office...

Series Legend

Input Area

Net Sales

CE

OR

TB

Royalty Rate:

7.3%

Average

Certification Series

Office Reference

True Beginner

Return Rate:

10.0%

Highest

Bonus Amount:

$ 500.00

Lowest

Author

Series Code

Software

Quantity Sold

No. Books Returned

Percent Returned

Unit Price

Net Sales

Author Royalties

Bonus

Author Earnings

Lopez

OR

Word 2016

          8,584

           500

$    49.95

500

Krupin

TB

Word 2016

          1,847

           271

$    25.00

500

Cote

CE

Word 2016

          2,684

           400

$    39.95

500

Yeung

OR

Excel 2016

        11,841

        1,042

$    49.95

500

Tremblay

TB

Excel 2016

          9,475

           957

$    30.00

500

Torres

CE

Excel 2016

          8,443

           327

$    39.95

500

Martin

OR

Access 2016

          8,064

           834

$    49.95

500

Alfero

TB

Access 2016

          3,397

           331

$    30.00

500

Daniels

CE

Access 2016

          3,978

           415

$    34.49

500

Ortiz

OR

PowerPoint 2016

          1,279

           120

$    49.95

500

Wong

TB

PowerPoint 2016

          1,050

           184

$    25.00

500

Kumar

CE

PowerPoint 2016

          2,507

           187

$    34.49

500

Bartalotti

TB

Outlook 2016

          1,884

           175

$    25.00

500

Wallace

OR

Windows 10

        14,750

        1,839

$    49.95

500

Toulou

TB

Windows 10

          8,342

           803

$    25.00

500

Coleman

CE

Windows 10

          6,124

           741

$    34.49

500

EX16_XL_VOL1_GRADER_CAP_HW - Software Training Books 1.5

Project Description:

You are a vice president for a publisher of software training books. Your division publishes three series that focus on Microsoft Office and Windows. You want to analyze the sales data and calculate author royalties. You will format the worksheet, insert formulas and functions to perform calculations, sort and filter data to review specific book sales, and prepare a chart that compares sales by series.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Start Excel. Download and open the file named exploring_ecap_grader_h1.xlsx.

0

2

On the Data worksheet, select the range A6:K6, wrap the text, and apply Center alignment. Change the row height to 30 for row 6.

3

3

In cell F7 in the Data worksheet, insert a formula that calculates the percentage of books returned based on the number of books returned and the quantity sold. Copy the formula from cell F7 to the range F8:F22.

4

4

In cell H7 in the Data worksheet, insert a formula that calculates the net sales. This monetary amount reflects the number of books not returned and the unit price. Copy the formula from cell H7 to the range H8:H22.

4

5

In cell I7 in the Data worksheet, insert a formula that calculates the amount of the first author’s royalties. An author’s royalties are based on the Royalty Rate located in the Input Area and the respective Net Sales. Copy the formula from cell I7 to the range I8:I22.

4

6

In cell K7 in the Data worksheet, insert a formula that adds the first author’s royalty amount to the bonus. Copy the formula from cell K7 to the range K8:K22.

4

7

In cell J2 in the Data worksheet, insert a function to calculate the average net sales. In cell J3 insert a function to calculate the highest net sales. In cell J4 insert a function to calculate the lowest net sales.

6

8

Select the range L1:N2 in the Data worksheet, copy the selected data, and transpose the data when pasting it to cell A2. Delete the data in the range L1:N2.

4

9

Click cell C6 in the Data worksheet and insert a column. Type Series Name in cell C6. Click cell C7 in the Data worksheet and insert a lookup function that identifies the series code, compares it to the series legend, and then returns the name of the series. Copy the function you entered from cell C7 to the range C8:C22. Change the width of column C to 18.

8

10

Click cell K7 in the Data worksheet and replace the current contents with an IF function that compares the percent returned for the first book to the return rate in the Input Area. If the percent returned is less than the return rate, the result is $500. Otherwise, the author receives no bonus. The only value you may type directly in the function is 0 where needed. Copy the function you entered from cell K7 to the range K8:K22.

5

11

Select the range G7:G22 in the Data worksheet and apply the Percent Style format with one decimal place. Select the range K7:K22 and apply the Accounting Number Format. Merge and center the label Series Legend in the range A1:C1 in the Data worksheet. Apply Thick Outside Borders to the range A1:C4.

Note, the border type may be Thick Box Border, depending on the version of Office used.

6

12

Select Landscape orientation, adjust the scaling so that the data fits on one page, and set 0.1 left and right margins for the Data worksheet.

4

13

Click the Sales sheet tab, convert the data to a table, and apply Table Style Light 9.

4

14

Sort the data by Series Name in alphabetical order and then within Series Name, sort by Net Sales from largest to smallest.

4

15

Add a total row to display the sum of the Net Sales column. Change the column width to 14 for the Net Sales column.

4

16

Select the values in the Percent Returned column and apply conditional formatting to apply Light Red Fill with Dark Red Text for values that are greater than 9.9%.

3

17

Select the values in the Net Sales column and apply a filter to display only net sales that are less than $100,000.

4

18

Click the Net Sales sheet tab, select the range A3:D7, and create a clustered column chart.

4

19

Move the chart so that the top-left corner is positioned inside cell A9. Change the chart width to 4.66 inches and the chart height to 2.9 inches.

3

20

Link the chart title to cell A1. Format the value axis to display whole numbers only.

2

21

Format the chart title, value axis, category axis, and legend with Black, Text 1 font color.

3

22

Select the Series Sales tab, select the ranges A4:A7 and C4:C7 and create a pie chart. Move the pie chart to a chart sheet named Office Reference. Move the Office Reference chart sheet to the right of the Series Sales sheet.

5

23

Change the chart title to Office Reference Series. Apply bold and change the font size to 18 for the chart title.

2

24

Apply the Style 12 chart style and change the colors to Colorful Palette 4.

4

25

Display data labels in the Inside End position. Display Percentage data labels; remove the Value data labels. With the Series 1 Data Labels selected, apply bold, change the font size to 18, and then apply White, Background 1 font color.

6

26

Ensure that the worksheets are correctly named and placed in the following order in the workbook: Data, Sales, Net Sales, Series Sales, Office Reference. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

0

Total Points

100

In: Finance

Randomly sample 50% of cases from cohort study C and place them in the cells of...

Randomly sample 50% of cases from cohort study C and place them in the cells of Table 4 below. If the sample of cases (or controls) is random it will maintain the same ratio of exposed to unexposed among cases and non-cases that is present in cohort C. Next, determine how many controls will be required in table 4 in order to have 1 control for each case. There are two ways to sample the required number of controls from cohort C. First sample “controls 1” from all persons who entered the cohort (column 2 of Table 3), prior to knowledge of disease status. Then, sample “controls 2” from all persons who did not develop the disease during follow-up (Column 4 of Table 3). Although, it’s not realistic, retain two decimal places in the numbers of controls. As your samples of controls must also be random, they should also maintain the same ratio of exposed to unexposed that is present among potential controls in Cohort C.

Table 3. (4 points) Cohort Study C. Provide an answer for all 9 of the shaded cells. Make the calculation and show an appropriate number of decimal places. For example, 100/500 is not an acceptable answer, calculate the final number. Odds of disease = individuals with disease/individuals without disease.

Total

Disease

Disease

Risk of

Odds of

Present

Absent

Disease

Disease

Exposed

1,500

1350

150

Unexposed

28,500

8550

19,950

Total

30,000

9900

20,100

Risk Ratio =

Odds ratio =

Population Attributable Risk (PAR) use formula for cohort =

Table 4. (5 points) Sampling two studies from Cohort C.

Provide an answer for all 13 of the shaded cells. Show 2-4 decimal places.

Cases

Control 1

Control 2

Exposed

Unexposed

Total

CC Study 1: OR 1 (Control 1) =

CC Study 2: OR 2 (Control 2) =

Study 1: Population Attributable Risk (PAR) using

ca/co formula =

Study 2: Population Attributable Risk (PAR),

Using ca/co formula =

In: Biology

Passage 14 (Questions 78–81) The following experiments study the reactivities of several active metals (Experiment 1)...

Passage 14 (Questions 78–81)

The following experiments study the reactivities of several active metals (Experiment 1) and halogens (Experiment 2).

Experiment 1

Pea-sized samples of five active metals were placed in deionized water, and observations were recorded in Table 1.

Table 1 Observations

Metal used

Observation upon adding the metal to water

Observation of the resulting solution

Mg

No obvious reaction

Neutral

Ca

Sank, and slowly gave off bubbles of a gas

Basic

Li

Moved over the surface slowly fizzing

Basic

Na

Moved over the surface vigorously fizzing, caught fire

Basic

K

Moved over the surface vigorously fizzing, exploded loudly

Basic

Experiment 2

A saturated aqueous solution of Cl2 was added to separate aqueous solutions of NaF, NaCl, NaBr, and NaI and mixed well. Observations were recorded. In addition, samples of salt solutions were added to separate electrolysis cells, and the minimum voltage required to produce an observable reaction at the anode was recorded. H2(g) was produced at the cathode in each cell and, except for the NaF solution, the solutions became basic. Results are recorded in Table 2.

Table 2 Result

Solution

Chlorine water

Electrolysis cell

voltage and product

NaF

No change

2.06 V; O2(g)

NaCl

No change

2.19 V; Cl2(g)

NaBr

Red-brown

1.90 V; Br2(aq)

NaI

Yellow-brown

1.37 V; I2(aq)

Experiment 1 was repeated with 0.40 g of calcium, and the gas that evolved was collected. The identity of the gas, and its approximate volume at 1.0 atm and 27°C were:
(Note: R = 0.0821 L•atm/mol•K)

H2, 250 mL.

H2, 500 mL.

O2, 250 mL.

O2, 500 mL.

In: Chemistry

1)A professor using an open-source introductory statistics book predicts that 60% of the students will purchase...

1)A professor using an open-source introductory statistics book predicts that 60% of the students will purchase a hard copy of the book, 25% will print it out from the web, and 15% will read it online. She has 200 students this semester. Assuming that the professor's predictions were correct, calculate the expected number of students who read the book online.

2)The number of AIDS cases reported for Santa Clara County, California is broken down by race in the table below. Source: "HIV/AIDS Epidemiology Santa Clara County", Santa Clara County Public Health Department, May 2011.

Race Cases
White 2136
Hispanic 1122
Black 448
Asian/Pacific Islander 227
Total 3933


Directions: Conduct a chi-square test for goodness-of-fit to determine whether or not the occurrence of AIDS cases is consistent with the race distribution of Santa Clara County.

  1. Choose the correct null and alternative hypotheses.
    • H0:H0: The distribution of AIDS cases is consistent with the race distribution in Santa Clara County.
      HaHa The distribution of AIDS cases is different from the race distribution in Santa Clara County.
    • H0H0 The distribution of AIDS cases is different from the race distribution in Santa Clara County.
      Ha:Ha: The distribution of AIDS cases is consistent with the race distribution in Santa Clara County.
  2. Compute the test statistic.

    The population distribution of Santa Clara County by race is provided in the table below. Use these percentages to compute the expected number of cases for each racial group. Round each of the expected counts to 2 decimal places.
    Race Proportion Expected cases
    White 0.424
    Hispanic 0.259
    Black 0.025
    Asian/Pacific Islander 0.292
    Total 1

    Determine the value of the test statistic. Round your answer to 1 decimal place.

    χ2=χ2=
  3. Compute the p-value. Round your answer to 4 decimal places.

    p-value =
  4. Interpret the results of the significance test.
    • The differences between the distribution of AIDS cases and the distribution of the general population in Santa Clara County are not statistically significant. From a practical perspective, the differences are minor.
    • The differences between the distribution of AIDS cases and the distribution of the general population in Santa Clara County is statistically significant. The differences are also important from a practical perspective. For example, the number of blacks with AIDS is 3.6 times more than expected and the number of Asian/Pacific Islanders with AIDS is 4.1 times less than expected.

In: Statistics and Probability

In the interest of detecting the few children remaining who believe in Santa Claus, you give...

In the interest of detecting the few children remaining who believe in Santa Claus, you give the latest test for “Belief in Santa Claus” from Canadian Living magazine to your little brother. According to the test, if the child answers more than 10 or more of the questions with the correct “Santa answer”, then the child is declared to believe in Santa Claus. From that perspective, describe with reference to the test

(a) what would be a Type I error for the test? (1 point)
(b) what would constitute a Type II error for the test? (1 point)

(c) what would correspond to the power of the test? (1 point)

(d) what corresponds to the criterion of the test? (2 points)

In: Statistics and Probability

Who are the founders of the Building Bridges Initiative?

Who are the founders of the Building Bridges Initiative?

In: History

In the Introductory Survey we asked if you had paid employment while studying at university. We...

In the Introductory Survey we asked if you had paid employment while studying at university. We also asked this question in the equivalent survey for Semester 2 2018. A summary of the results for this question from both surveys is shown in the table below. ‘Working’ denotes students who said that they had some kind of paid employment while studying, ‘Total’ denotes the total number of students who responded to these questions in the surveys. 2019 Semester 1 Working 355 Total 627 2018 Semester 2 250 510 STAT101 includes a very wide range of students. For this question we will assume that these samples can be regarded as random samples from the total populations of first year UC students in Semester 1 2019 and Semester 2 2018, respectively. a) Use the data to construct a 90% confidence interval for the population proportion of first year UC students who were working while studying at the start of Semester 1 2019. b) Use the data to construct a 90% confidence interval for the population proportion of first year UC students who were working while studying at the start Semester 2 2018. c) Use your confidence intervals to comment on the suggestion that the proportion of first year UC students who were working while studying was higher at the start of Semester 1 2019 than at the start of Semester 2 2018. Give your response in one or two brief sentences.

In: Statistics and Probability

Find an article about the 2016 presidential election. Evaluate the article for priming, framing, and slant....

Find an article about the 2016 presidential election. Evaluate the article for priming, framing, and slant. Identify the following:

The title and author of the article Is the article produced by a public or private outlet?

Who is the intended audience’s and what is that group’s ideological leaning? Next, reflect upon your overall evaluation of the article’s legitimacy.

Do you agree with the arguments and or facts presented or do you disagree. Why or why not?

In: Economics

In discharge over V-notch experiment, calibration curve (using log Q vs log H) is plotted. How...

In discharge over V-notch experiment, calibration curve (using log Q vs log H) is plotted. How would you interpret results which, when plotted logarithmically, fall on a line which is not straight but slightly curved? and, What are the effects of a change in the V-notch angle on the discharge equation and the accuracy of flow measurement?

In: Civil Engineering

1. A magnet is inserted into a coil of wire consisting of 10000 loops. The cross-sectional...

1. A magnet is inserted into a coil of wire consisting of 10000 loops. The cross-sectional area of the coil is 0.5 m2and is kept in a uniform magnetic field of B = 4 T. Assume that the angle between the surface normal of the coil and magnetic field is qo= 45oat t0= 0s. The coil is rotated at a constant rate to an angle, q= 900at t = 60s. What is the average induced voltage (emf) induced in the coil?

A) 177 V        B) 220 V         C) 288.70 V    D) 235.70 V

2. To have stationary interference pattern in young double slit experiment, wavelength of incident beams should be:

A) Same         B) different      C) zero D) Infinite

3. If we switch from red laser (l= 670nm) to green laser (l= 530nm) in a Young’s double slit diffraction experiment then what happens to the fringe pattern?

A) Nothing     B) Position of the Principle maxima changed by 130 nm.

C) Separation between fringes increases.       D) Separation between fringes decreases

In: Physics