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:
–
.
