Show the companys performance to lucy

Assignment Help Other Subject
Reference no: EM133125038

Scenario

While your first attempt to show the company's performance to Lucy was not bad, clearly she has a lot more requirements than what you provided. She wants to know more about the year over year sales, sliced into different categories, sub-categories, and countries. She also wants to see additional information such as customer demographics.

Jack has provided you with a different data source. This time the data has more than one hundred thousand rows.

Before you can create additional reports to Lucy, first you need to prepare the data.

What You'll Need

• A computer with Microsoft Excel installed on it. We prefer Excel 2016, but you can still complete the lab using Excel 2013 or Excel 2010.
• File Lab2Start v5.xlsx.

Download the lab file Lab2Start v5.xlsx to answer the questions below.

The first thing you need to do is to convert the data into an Excel table.

Once you do that, you can add total row, filter the data, and the total will reflect the total only for the filtered data. Let's try this. Add a total row for the table, and use the Sum aggregation to show the total of the Revenue column and then filter the data only for United States.

Now, you need to add several columns, derived from existing columns in the data. Before adding columns, it is good practice to clear any filters you previously applied.

First, let's add a "Month" column. Insert a new column to the left of the Customer ID column, and use formula to derive the month of sales from the Date column.

HINT: Use the Text() function and look for a format code in the examples that would work on a date field.
Next, let's add an "Age Group" column. Remember to clear any filters you previously applied. Insert a new column to the left of the Customer Gender, and use formula to derive the age group from the Customer Age column. Let's group the customers based on the following criteria:
- Youth (<25)
- Young Adults (25-34)
- Adults (35-64)
- Seniors (>64)
HINT: Use the nested IF() functions. Alternatively, you can use the IFS() function if it is available in your version (2016 + updates from O365)

Now, let's add a "Frame Size" column. Insert a new column to the left of the Order Quantity, and use a combination of the IF() and RIGHT() functions to derive the frame size of a bicycle from the last two characters of the Product column, when the Product Category is Bikes. Otherwise, leave it blank.

HINT: Use the IF() function to test for Product Category="Bikes" and if it does, use the RIGHT() function to extract the last two characters of the Product column.

Last but not least, let's add a "Profit" column. Insert a new column to the right of the Revenue, and use formula to derive the Profit from both the Revenue and Cost columns. Show the total for the Profit column. Use the Sum aggregation in the total row of the table, for the Profit column.

HINT: Profit is Revenue minus Cost.

Attachment:- data analytics life cycle.rar

Reference no: EM133125038

Questions Cloud

Analyze existing and shitting political circumstances : Analyze existing and shitting political circumstances of a country related to the stability of the government and how international business is conducted in tha
Prepare a revised statement of income for EcoPro Ltd : Prepare, in good form and with proper disclosure, a revised statement of income for EcoPro Ltd. The company has a 30% income tax rate
Build phones on windows mobile : -Launched in June 2007, Apple's iPhone quickly grabbed a substantial share of the worldwide market for smart phones. Competing phones and operating systems incl
Prepare partial statement of changes in shareholders equity : Please correct the error and prepare the partial Statement of Changes in Shareholders' Equity to document the change of retained earnings
Show the companys performance to lucy : Show the company's performance to Lucy was not bad, clearly she has a lot more requirements than what you provided
Negative aspects and opportunities for improvement : -Why is it important to survey patients and what are some factors that can affect the accuracy of the responses received?
Pass laws on social media censorship : Do you think vaccine and mask mandate could help end the spread of the virus quicker?
Determine the contribution margin per unit : There was no beginning inventory. Assume that direct labor is a variable cost. Determine the contribution margin per unit
What amount received on January : Question - What amount received on January 18 is equivalent to $7500 received on the preceding August 30 if money can earn 7.5%

Reviews

Write a Review

Other Subject Questions & Answers

  Cross-cultural opportunities and conflicts in canada

Short Paper on Cross-cultural Opportunities and Conflicts in Canada.

  Sociology theory questions

Sociology are very fundamental in nature. Role strain and role constraint speak about the duties and responsibilities of the roles of people in society or in a group. A short theory about Darwin and Moths is also answered.

  A book review on unfaithful angels

This review will help the reader understand the social work profession through different concepts giving the glimpse of why the social work profession might have drifted away from its original purpose of serving the poor.

  Disorder paper: schizophrenia

Schizophrenia does not really have just one single cause. It is a possibility that this disorder could be inherited but not all doctors are sure.

  Individual assignment: two models handout and rubric

Individual Assignment : Two Models Handout and Rubric,    This paper will allow you to understand and evaluate two vastly different organizational models and to effectively communicate their differences.

  Developing strategic intent for toyota

The following report includes the description about the organization, its strategies, industry analysis in which it operates and its position in the industry.

  Gasoline powered passenger vehicles

In this study, we examine how gasoline price volatility and income of the consumers impacts consumer's demand for gasoline.

  An aspect of poverty in canada

Economics thesis undergrad 4th year paper to write. it should be about 22 pages in length, literature review, economic analysis and then data or cost benefit analysis.

  Ngn customer satisfaction qos indicator for 3g services

The paper aims to highlight the global trends in countries and regions where 3G has already been introduced and propose an implementation plan to the telecom operators of developing countries.

  Prepare a power point presentation

Prepare the power point presentation for the case: Santa Fe Independent School District

  Information literacy is important in this environment

Information literacy is critically important in this contemporary environment

  Associative property of multiplication

Write a definition for associative property of multiplication.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd