# data atanlysis and excel

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.
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.
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)?
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)?
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)?
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)?

.
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.
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)?
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
What is the largest Unit Price in this dataset?
Hint: Use the MAX() function here.
How many orders were sent by Delivery Truck? Assume each line is counted as a single order.
Hint: Use the COUNTIF() function here.
What is the total shipping cost of all orders sent by Delivery Truck?
Hint: Use the SUMIF() function here.
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.

.
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?
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?
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!
Continuing from the previous question, update the pivot table to show the Average of Sales by month.
In the Corporate Customer Segment, what was the most common order quantity?
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)?
The median of “Total Sale \$” is larger than the mean. By how much? Round to 2 decimal places.
What is the standard deviation of Total Sale? Round to 2 decimal places.
What percentage of orders fell within the interquartile range of Total Sale?
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]
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]
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.
What is the median total sales of the age group with the highest average Total Sales? Round to 2
decimal places.

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

.
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
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?
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.
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.
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.
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.

.

Don't use plagiarized sources. Get Your Custom Essay on
data atanlysis and excel
Just from \$13/Page
Calculate your paper price
Pages (550 words)
Approximate price: -

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.

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!

## Calculate the price of your order

Total price:
\$0.00

How it works?

Follow these simple steps to get your paper done

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.

## Essay Writing Service

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.