We Helped With This Statistics Assignment: Have A Similar One?

Question 1: Excel Basic

PLE, headquartered in St. Louis, Missouri, is a privately owned designer and producer of traditional lawn mowers used by homeowners. Answer following questions. All data is provided in Question 1 worksheet.

1). Satisfaction Analysis: The Company categories their customer satisfaction into 5 levels. 0 stands for least satisfied and 5 stands for 100% satisfied. Use End-User Satisfaction sheet to answer the following questions:

a). Provide a table to display the total number of responses to each level of the survey scale (0-5) for each region. Create a dynamic column chart allow you display any region’s every survey scale’s statistics (total number). 

b). The Company uses score “4” as a threshold to understand if a customer is satisfied with the products: when a customer gave score 3 or below, that means that customer is not satisfied with the products; and if a score 4 or above, that means that customer is satisfied with the products. The PLE targets to achieve overall 80% customer satisfaction (the percentage of having score at “4” or above among all responses). Calculate yearly overall percentage customer satisfaction for each region to see if the Company has achieved this goal. If not, which particular region(s) should improve the most? 

c). Based on part b), draw a Sparkline chart (Line chart type) to display the trend of customer satisfaction overall percentage over years for each region. Based on charts, which region has improved satisfaction? Which region has declined satisfaction?

2). Profits Analysis: Use Product Sale Price, Unit Production Costs, Tractor Unit Sales and

Mower Unit Sales and appropriate Excel functions data sheets to answer following questions:

a). Find world-wide gross revenues by month for both Mowers and Tractors.(Please note: in Product Sale Price Sheet, it provides price for each year. For one given particular year, use this same price on every month) 

b). Find world-wide total costs by month for both Mowers and Tractors.

c). Find world-wide total profits by month for both Mowers and Tractors.

d). Draw a line chart for world-wide total profits over time for both Mowers and Tractors, what kind of conclusion can be made from that chart?

Question 2: Data Model

In Excel file Question 2 Data workbook, a British company kept its products sales records. There are three worksheets in this data: a). Sales: it includes date, branch, product names, sold quantity and unit price information; b). Product Category: lists products belong to which category; c). Region: lists branch belong to which sales region. Use all of this information and answer the following questions: 

1). What are total revenues for each year? 

2). What are total revenues for each product category; Chart bar graph of total revenues for all product category.

3). What are total revenues for each region? Draw a bar chart to compare each region revenue.

Question 3: Pay Rate Analysis

This question uses Adventure Work Database Human Resource Department data to analyze how pay rates are influenced by different types of jobs, gender, locations and sickleave-day factors. Your job is to create a report that analyzes all of these influences. 

To do so, you consider using several tables from Adventure Work Database, 

HumanResources_Employee

HumanResources_EmployeeAddress

HumanResources_EmployeePayHistory

Person_Address

Person_StateProvince

Use appropriate Excel Tool create an interactive Dashboard with the following information:

1)      Find average Pay Rate(hourly) by Employee Title.  What are pay differences between male and female through different job title? Create a table on it and make some comments on it.

2)      Find average pay rates by different states/provinces between male and female, comment on

it;

3)      Find average sickleavehours and Average vacationshours by different states, combine these two columns into a new column named average “LeaveHours” by states, comment on it.   

Question 4: Dashboard by Excel Pivot table and Pivot Charts.

A financial services firm is looking at a collection of their mutual funds to create an investment overview pamphlet with tips for new investors. The data consists of 316 funds and 13 variables information. 

Data includes a fund number, or unique identifier; market cap, or the market capitalization of the stocks in which the fund invests; Type, or the investment goal; Assets, the amount of money customers have already invested in the fund; Turnover ratio, how often a fund buys and sells securities (as a percentage); Beta, a measure of volatility relative to S&P 500 (1 is the same volatility); SD, a volatility measure relative to 36-month average (higher means more historical volatility); Risk, categorical variable assessing risk (based on Std. Dev. And other factors); 1-, 3, 5- and 10-year returns as a percentage change (self-explanatory); Expense Ratio, the annual percentage of a fund’s assets paid out for fund management costs; and Star Rating, a riskadjusted evaluation of a fund’s performance (higher is better).

With this information, considering using your Excel skills(Pivot table, Pivot Charts and Slicer) to create a following dashboard. The dashboard is expected to be interactive not simply a group of static charts. In order to better understand this data, we analyze this financial data from following perspectives:

•      Return by risk and rating:

1.      Create a side-by-side bar chart to show average 1,3,5 and 10-year return based on different RISKs for different MARKET CAP;

2.      Create a Sparkline-Win-Loss chart to show average 1,3,4 and 10-year return based on different RATINGS for different TYPE of the fund. 

•      Available Selections:

3.      Create a pie chart to present percentage for each MARKET CAP level funds.

4.      Create a Stacked-Column chart present the portion of each TYPE of the fund at the each RISK level. (Tip: you may consider put one factor under row, one under column in a pivot table setting)

•      Costs:

5. Create a bar chart for average expense ratio for each TYPE of fund under every MARKET CAP level.

•      Comments:

6. Based on above findings, briefly make some comments. Please make sure your comments are relevant to your charts.

Note: the whole word in CAPITAL means the variable name in the excel file.