The Board has decided to continue its investment in technology as a key product. Using the data provided

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Administrative Analysis & Reporting

Using Superstore Data 

Focus: Regression Analysis, Forecasting, Pivot Table, Descriptive Statistics, 

Using the submitted Excel file, undertake the following:

1. Descriptive statistics for Sales, Quantity, and profit respectively. Explain the results. 

2. You are the newly appointed Chief Business Development Officer (CBDO) for Superstore. There have been many management discussions on whether the company should remove discounts as a strategy for growth.  In the last executive meeting, the Chief Operation Officer(COO) suggested that the company should discontinue operations in some locations. The CEO wants to understand whether there is a relationship between the below sets of variables so that he can make appropriate decisions relating to the company’s variable cost (light, water, cost of raw material, etc).

a. Discount & sales 

b. Discount & quantity 

c. Postal code & net profit

Conduct a Linear Regression Analysis for each of the below:

i. Discount & sales

ii. Discount & quantity

iii. Postal code & net profit. 

Present your findings and analysis to the CEO.

3. Using data from orders made in 2011 and 2012 (order date), as the CBDO, project the sales, quantity and profit for 35 occurrences in January 2013. Compare the results of the actual sales, quantity and profit for January 2013 vs the project (based on your projection in Excel) sales quantity and profit for January 2013.  

Present your findings and analysis to the Board of Directors.

4. The Board has decided to continue its investment in technology as a key product. Using the data provided (2011 to 2014),

a. Project sales for technology in the first quarter for 2015. Present your findings, analysis and recommendations. 

b. Given the new global warfare - biological warfare including laboratory made viruses which can cause global pandemic, outline strategy/strategies that you believe the organization should undertake to increase sales and profitability. Substantiate your strategy/strategies.

Excel Quick Reference

Descriptive Statistics

Descriptive statistics allows one to describe data in a practicable form. 

There are four major types of descriptive statistics:

1. Measures of Frequency (Count, percentage)

2. Measures of Central Tendency (mean, median, mode)

3. Measure of dispersion or variation (range, variance, standard deviation)

4. Measures if position (percentile ranks, quartile ranks)

Regression Analysis

Statistical methodology to determine the strength of the relationship between two variables. That is, regression analysis involves identifying the relationship between a dependent variable and one or more independent variables. 

Formula : Y = mx + c where c is a constant, x is the independent variable and y is the dependent variable.

Independent (explanatory) variable – a variable that does not depend on other factors

Dependent variable - a variable that depends on other factors

Possible questions:

 Does an increase in one variable causes an increase in another? (+ relationship)

Does an increase in one variable causes a decrease in another? (- relationship)

Forecast

Excel’s Forecast functions facilitates the prediction of a future value by using linear regression. 

Where: X (required) - a numerical x-value for which you want to predict a new y-value. Forecast feature uses historical data to predict future trends.

Descriptive Statistics

a. Click on Data -> Data Analysis -> Descriptive Analysis

b. Select data range (numeric data only)

c. Select the statistical test -> Click ok

d. Label columns appropriately

e. Document your findings.

     Linear Regression

1. Click on Data -> Data Analysis -> Regression

• Click Normal Probability Plot & 95% confidence interval(CI) -> ok

Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme