Using your newly created “Expanded Order Type” column, calculate the total “Sales” for all orders of“Medium” type (rounded to 2 decimal places). Hint: The SUMIF() function is useful here.

Answer:

Create the formula from the previous question and copy the formula down to all the rows.

How many orders will have a discount applied?

Hint: The COUNTIF() function is useful here.

Answer:

The “Sales with Discount” can be calculated by applying the discount to the original

“Sales” entry:

Sales with Discount = Sales – (Sales * Discount)

or

Sales with Discount = Sales * (1 – Discount)

Create a formula in the “Sales with Discount” column and copy it down to all the rows.

What is the impact of the discount on total sales in 2012? In other words, what is the

difference between the sum of the “Sales” and the sum of the “Sales with Discount”

(rounded to 2 decimal places)?

Answer:

Currently, customers are responsible for paying the shipping costs. The sales team

suggests that customers really dislike paying shipping costs, and that offering “free

shipping” instead of the 1% discount would likely increase sales. Create a formula for the

“Sales with Free Shipping” column that subtracts the “Shipping Cost” from the “Sales”

only if the “Expanded Order Type” is Extra Large, XX Large, or XXX Large. Copy the formula

down to all the rows. What would total 2012 sales have been if the company had offered

free shipping instead of the 1% discount (rounded to 2 decimal places)?

Answer:

How much money would the company have saved in 2012 if it had offered free shipping instead of the

1% discount on Extra Large, XX Large, or XXX Large orders (rounded to 2 decimal places)?

Answer:

Question 10

What would 2012 total “Sales” have been if the company had offered free shipping on any order

shipped by Delivery Truck, and no additional discounts (rounded to 2 decimal places)?

Answer:

–

.

Import the file into Excel using the correct file format from the previous question. How many rows of

data does the file contain?

Hint: Do not count the top (header) row.

Answer:

Sort the data by Order Date (oldest to newest) and then by Order Quantity (largest to smallest). After

the data is sorted, what is the Order ID of the order in the 3rd row of data (i.e. spreadsheet row 4)?

Answer:

Insert a new column to the right of the “Sales” column, and type the name “Unit Price” as the header.

Using cell references, calculate the unit price of the order in the first row, then copy and paste that

formula down to the remaining rows.

Rounded to 2 decimal places, what is the Unit Price of the last row of data? (Do not include a dollar sign

or commas in your answer)

Answer:

What is the largest Unit Price in this dataset?

Hint: Use the MAX() function here.

Answer:

How many orders were sent by Delivery Truck? Assume each line is counted as a single order.

Hint: Use the COUNTIF() function here.

Answer:

What is the total shipping cost of all orders sent by Delivery Truck?

Hint: Use the SUMIF() function here.

Answer:

What is the average shipping cost (to 2 decimal places) of all orders sent by Delivery Truck?

Hint: Calculate the average by taking total cost divided by number of orders.

Answer:

–

.

On the “Compiled Information” tab, a VLOOKUP formula has been pre-entered into cell E3. This

formula was written correctly, and it uses references to the numbers in cells E1 through G1 to

determine the correct index_number parameter. Fill in cells F1 and G1 with the correct index numbers,

then copy the formula in cell E3 down to all the rows in columns E, F, and G. What number did you enter

into cell G1?

Answer:

Now that the data is updated, create a pivot table that contains all of the data on the “Compiled

Information” tab. Using the PivotTable Fields side bar, drag the “Unique ID” field to the VALUES area

and summarize the information by Count. What is the Count of Unique ID?

Answer:

Update the pivot table to show “Order Date” grouped by month. Drag the “Sales” field into the correct

area to show the Sum of Sales for each of the months listed. Which month had the lowest sales? (Your

answer should be the name of the month.)

Hint: Remember that you can sort the data once it is in the pivot table!

Answer:

Continuing from the previous question, update the pivot table to show the Average of Sales by month.

Answer:

In the Corporate Customer Segment, what was the most common order quantity?

Answer:

In all segments, how many orders were placed in one quarter of the year but shipped in the next

quarter (For example, an order in which Order Date = Qtr1 2009 and Ship Date = Qtr2 2009)?

Answer:

The median of “Total Sale $” is larger than the mean. By how much? Round to 2 decimal places.

Answer:

What is the standard deviation of Total Sale? Round to 2 decimal places.

Answer:

What percentage of orders fell within the interquartile range of Total Sale?

Answer:

Given the limited information you have, your boss wants you to group customers in a meaningful way.

You decide to take a look at how the order region impacts things. Calculate the average total sales from

the North region only. What is the difference between the North region average total sales and the

average total sales across all regions (including the North)? Round to 2 decimal places.

[Note: To calculate the average total sales from the North region only you could either “sort” the data

and calculate the average or “filter” the data, copy and paste as values and then calculate the average.

Please refer to Course 1 of this specialization for details on sorting and filtering data]

Answer:

What is the absolute value of difference between the North region median total sales and all orders

median total sales (across all regions including the North)?

Round your answer to two decimal places.

[Note: To calculate the median total sales from the North region only you could either “sort” the data

and calculate the median or “filter” the data, copy and paste as values and then calculate the median.

Please refer to Course 1 of this specialization for details on sorting and filtering data]

Answer:

Next, take a look at customer age. Create 3 age groups: 21-30, 31-40, 41-50. What is the average total

sales for the age group with the highest average? Round to 2 decimal places.

Answer:

What is the median total sales of the age group with the highest average Total Sales? Round to 2

decimal places.

Answer:

–

.

What is the covariance of Datasets A and B? Round to 2 decimal places.

Answer:

–

.

A new airline company has a commuter airplane that can hold up to 64 passengers. The plane flies a

single route and charges passengers $300 for a one-way fare. All fares are 100% refundable if the

passenger does not show up for the flight. The fixed cost (the cost that does not change with the

number of passengers, such as crew salaries, airport fees, etc) for every flight is $1,000. The variable

cost (the additional cost per passenger) for every flight is $150 per passenger.

Create a spreadsheet that calculates the total profit per flight based on the number of passengers on

the plane. What is the total profit if they fly with 56 passengers?

HINT:

1. Create a column of “Total number of passengers”, put in values 1 through 64

2. The next column would be “Variable Cost”. Put in values in this column, for example if you fly with 1

passenger the variable cost is $150, if with 2 passengers then it is 2*$150, with 15 passengers it is

15*$150 and so on.

3. The third column would be “Total Cost” which is the “Variable Cost” plus “Fixed Cost”, i.e. the

number in the Variable Cost column plus $1000

4. The fourth column would be “Revenue” column which is “Total number of passengers”*$300. That is

if the plane only carries 1 passenger then the revenue is 1*$300, if it carries 43 passengers then revenue

is 43*$300 and so on.

5. The fifth column would be “Profit” column which simply is “Revenue” column minus the “Total Cost”

column.

6. Once you have created this “Profit” column then read off the value of profit when the plane flies with

56 passengers. That is your answer.

Answer:

Because they have a full refund policy, it is common for customers not to show up. Airline company

management is wondering if it would make financial sense to overbook the flight and risk having not

enough seats for all passengers that show up. In that case, the airline would find volunteers to give up

their seats in exchange for a free ticket to the same destination on the next available flight. This would

cost $100 for each overbooked passenger.

Update your spreadsheet to account for overbooked passengers. What is the total profit if they fly with

64 passengers after having sold 72 tickets, assuming all 72 passengers show up?

Answer:

If 80 tickets are sold, the number of passengers expected to show up can be approximated by a normal

distribution with a mean of 68 and standard deviation of 5. Therefore, if the airline sells 80 tickets for

the flight, what is the probability that the number of passengers who show up will result in an

overbooked flight? Enter your answer as a decimal probability (not a percent) rounded to 4 decimal

places.

Answer:

Fill in the blank:

Using the same distribution as the previous question, there is a 0.10 probability that more than

_________ passengers show up. Please round your answer to the lowest integer.

Answer:

Continuing with the same distribution, what is the probability that less than or equal to 60 passengers

show up? Enter your answer as a decimal probability (not a percent) rounded to 4 decimal places.

Answer

XYZ Company produces copper pipes to be supplied to a local utility company. The requirement of the

utility company is that the pipes need to be 200 cm of length. Longer pipes are acceptable to the utility

company but any pipe less than 200 cm is summarily rejected and has to be scrapped. The XYZ

Company loses all its production cost on pipes that are rejected.

The production process is such that it has some variability in the lengths of pipes produced, and this

variability can be well approximated by a Normal distribution. The company can adopt one of the

following three production processes:

Process A: Produces pipes with an average length of 200 cm and a standard deviation of 0.5 cm

Process B: Produces pipes with an average length of 201 cm and a standard deviation of 1 cm

Process C: Produces pipes with an average length of 202 cm and a standard deviation of 1.5 cm

If the company adopts the third Process (Process “C”), what is the probability it will have its pipe

rejected by the utility company? Enter your answer as a decimal probability (not a percent) rounded to

4 decimal places.

•

•

•

Answer:

–

.

Don't use plagiarized sources. Get Your Custom Essay on

data atanlysis and excel

Just from $13/Page

Why Work with Us

Top Quality and Well-Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Professional and Experienced Academic Writers

We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.

Free Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.

Prompt Delivery and 100% Money-Back-Guarantee

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

Admissions

Admission Essays & Business Writing Help

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

Reviews

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

Reviews

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.