1
If 2013 Sales numbers were expected to increase by 10% in the following year in all customer segments, what would be the total estimated sales for Home Office in 2014?

2
Which product has the highest ship cost to sales ratio?

3
Find the customer with the highest profit. What is his or her average shipping cost per order?

[Hint: to calculate the shipping cost *per order *you will need to calculate the number of orders using the count distinct function]

Be sure to click Submit Quiz to see your results!

I GOT THE QUESTION 3 AFTER DOING IT WRONG. YOU HAVE TO CALCULATE TOTAL SHIPPING COST/ DISTINCT NO. OF ORDERS

Hello Adarsh,

Could you pls advise how you solved this question ? Iam unable to do it

Regards,

Jayasree.

Shipping cost per order = Total shipping cost divided by the number of unique orders

Use COUNTD([Order ID]) to get the number or unique orders.

need help for question 1 and 3

Answer for Qn 1

Sum of Sales of Home Office in 2013 = 10%of $6,17,498 = $61,749

Expected Sales for Home Office in 2014 = $6,17,498+$61,749

Regards,

Jayasree.

1) You will need to add two filters:

– customer segment = home office

– year(order date) = 2013

Then add SUM(Sales) in rows. To compute the next year (2014), you will have to multiply the SUM(Sales) result by 1.10 (because it’s a 10% increase)

And boom, you’ll have 679248.

3) Add SUM(Profit) in columns, and Customer Name in rows. Order the chart by SUM(Profit) and you will see that Andrea Shaw has the highest profit. Add a text with this formula: SUM([Shipping Cost])/countd([Order ID]) (shipping cost PER order)

Boom, you’ll see 12.59 for Andrea Shaw.

Hello, could anyone please help with Q2? I’m thinking to see the ratio, you’d have to create a calculated field?

Thanks in advance.

Q2 amswer

1. Create calculated field

2. Enter sum(shipping cost)/sum(sales)

3. Move newly created calculated field into columns

4. Move Product Names into rows

5. Sort by new calculated field

Just create a calculated field for Ship to Sales Ratio. Sum of Shipping Cost/Sum of Sales.

Put the product names and the new Ship to Sales ratio into a view and sort by the new calculated field.

Question one is 10%, not 110%. In the solution, it says 110%, why? Please clarify.

Hi

It means the sales in 2014 will increase by 10% so the sales in 2014 will be 110% of sales in 2013.

Thanks,

Iris

i didnt get the answer for question no.3 tried with the formula sum of total shipping cost/countd(order id) am i wrong in the calculation field please reply

Hi

First, you need to find the customer with the highest profit. Then find the number of unique orders for this customer to get the answer.

Thanks

Iris

Hi,

Q1: If 2013 Sales numbers were expected to increase by 10% in the following year in all customer segments, what would be the total estimated sales for Home Office in 2014?

Ans: In 2013 it’s expected that Sales numbers will increase by 10% in the following year(means 2014) in all customer segments .

But we are asked to find estimated sales for customer segment = Home Office.

Drag Customer Segment = Home office and Order Date = 2013 to filter.

Sum(sales) to Label ( T). It displays 617,498. To estimate the result in 2014 create Calculated field: [sales] * ([sales] *(10/100)). Drag this field to Label(T) . Ans: 679,248