Conduct Quantitative Analysis

Project 4: Conduct Quantitative Analysis
Start Here
This project was developed for learners entering a variety of disciplines. It will enable a deeper understanding of how data informs solutions to practical problems with potential impact on your organization or industry.
Over this two-week period, you will use mathematical operations and data analysis to solve problems and inform decision making. Using a method similar to the qualitative research process, you will identify an issue and use mathematical formulas or an analytic tool to derive a solution. You will construct graphs, charts, and tables as necessary to inform analysis and interpretation. You will evaluate the results, draw and validate analyses, and communicate key mathematical information.
Choose the instructions for the program in which you are currently enrolled as you proceed throughout this project. You will first be presented with a scenario, then guided through your specific path as you advance through the project steps.
Click to reveal a scenario for Strategic Communications students.
Scenario
Your boss, the CEO of Mountain Air Recreation, Inc., has invited you to her office to tell you that the Board is considering the launch of a new product offering: luxury resort skiing. This offering will complement their existing line of high-adventure outdoor recreational sport offerings, which include jungle safaris, scuba diving, wilderness hiking, and helicopter skiing. The marketing team has done a great deal of research on the target audience and believes the company will make a handsome profit on the new resort. The CEO wonders, however, if the introduction of a luxury sport activity will affect their guests’ perceptions of the company, which is known for its rugged outdoor activities. She also wonders if downhill skiing —and the accompanying requirement to manage mountain forest growth—will damage the company’s reputation as a responsible steward of the environment. She is convinced that if the company moves forward with this project, it will be vital to integrate the marketing effort with a communications campaign designed to enhance the image of the company as one that not only sells high-end adventure but cares deeply about the environment and enjoying it responsibly. 
The CEO provides you with demographic and psychographic data about the company’s guests and asks you to provide an analysis of the data to inform her recommendations to the Board about the long-term wisdom of entering the high-end ski resort market. The CEO has scheduled an offsite planning meeting to discuss the new business endeavor and has asked you to prepare worksheets in Excel to support her presentation at the upcoming meeting. 
You plan to analyze the data using standard statistical methods in Microsoft Excel and to develop statistics as well as charts and graphs to support the presentation of data. Your task is to conduct data analysis and prepare a report for your boss about your findings, which will also include an analysis of how data informs a future strategy for growth. 
Once you have reviewed the scenario, review the project overview, approximate time commitment, and competencies that you will be responsible for in this project.
Nine steps will lead you through this project. Each step should take no more than two hours to complete. Continue with Step 1: Refresh Your Math, Statistics, and Excel Skills.

Step 1: Refresh Your Math, Statistics, and Excel Skills
Everyone has a different background. As in previous projects, you will start by assessing your current baseline. First, think about what it means to engage in quantitative processes. Next, refresh your skills in math, statistics, and Excel. You will evaluate how much you already know and where you need to concentrate more attention.
If you need help outside the classroom, you can access tutoring by selecting Tutoring Assistance under Resources in your classroom. There are also videos when you access tutoring help and other resources to help you complete this project successfully.
Once you have a good idea of your skill levels in math and stats, and you know where to go for help, move on to Step 2, where you will apply some of your stats knowledge. Remember to choose the instructions of the course in which you are currently enrolled.
Step 2: Opening and Saving an Excel Spreadsheet
In this step, you will be opening and saving an Excel spreadsheet. Choose and follow the instructions that correspond to the current program in which you are enrolled.
Click to reveal instructions for MSC students.
1. Download the MSC Excel template and take a few minutes to review it. Note: There are two worksheets or tabs. The first tab (titled “QR Analysis Essay”) will be where you paste your end-of-project essay. The second tab (titled “Data”) contains the data to be analyzed.
2. Open the Excel file and go to “Save As” to rename it. Use the name format YourLastName Project 4. This file contains the data that you will manipulate and analyze. You will add tabs in Steps 3–8 to build an Excel workbook for this project.
3. Properly format your Excel workbook. Set the margins for landscape with narrow margins. Enter your name, date, and page number in the footer area of the sheet. Format the entire spreadsheet using Calibri 11-point font.
4. Once you have completed these tasks, proceed to the next step.
Step 3: Add Data
In this step you will add data in your Excel spreadsheet.
Click to reveal instructions for MSC students.
In Section 1 on the Data tab, complete each blank column of the spreadsheet to arrive at the desired calculations. Use Excel formulas to demonstrate that you can perform the calculations. Remember, a cell address is the combination of a column and a row. For example, C11 refers to Column C, Row 11 in a spreadsheet.
Occasionally in Excel, you may create an unintentional circular reference. This means that within a formula in a cell, you directly or indirectly refer to the same cell. For example, while entering a formula in A3, you might enter =A1+A2+A3. This is not correct and will result in an error. Excel allows you to remove or allow these references.
Hint: Another helpful feature in Excel is Paste Special. Mastering this feature allows you to copy and paste all elements of a cell or just select elements like the formula, the value, or the formatting.
Ready to Begin?
To calculate disposable annual income (Row G) per visitor, you will write an Excel formula that solves for this. In plain language, disposable annual income is the remainder from income minus fixed expenses minus the cost of the number of minors at a cost of $4,500 each. In Excel cell language, the formula will be G11 (annual income) − E9 (fixed costs) − E11 (number of minors) × G9 (cost per minor). Note that the number for fixed costs ($40,000) and cost per minor ($4,500) are static in the spreadsheet; to keep them the same in each line, you will place $ signs around the cell letter so that the calculation will always refer back to that number.

Source: Used with permission from Microsoft.
Once you have the answer displayed, you will then copy this formula down the column. Click the cell with the formula to be copied, move your cursor to the bottom right of the cell until it becomes a plus sign, then click and drag to the bottom of the table. (Since there are so many entries in the table, you may want to use a shortcut; once the cursor becomes a plus sign, double click and Excel will copy the formula to the bottom of the column). This will copy the formula and Excel will increment the references so that you only need to type the formula once.
Next, to calculate the age of the visitor, you will follow a similar process. You have visitors’ dates of birth in column B, and we are calculating their age as of 12/31/2019 (when marketing collected this data). You will need a formula that can compute absolute values to determine age. You could do this longhand, but it would take a long time. Instead, try the YEARFRAC formula, which computes the number of years. Once you start the formula in Excel, the element will appear to guide you. You need to know the ending date (B9) and the opening date (B11). The formula looks like this: =YEARFRAC($B$9,B11), and the $ will keep the end date constant down the column when you grab the right bottom edge of the cell and drag it to the bottom of the column, as above.

Source: Used with permission from Microsoft.
To round the age to the nearest year, you can adjust the cell format to provide a number with no decimal places, but you will get a more accurate view if you limit to one decimal place.
To calculate the travel classification, you will use an IF statement. You are given the number of miles the visitor traveled to come to the resort in Column D, and you are given the modes of travel in Section 2. Visitors are classified as “local,” “drive,” or “fly” based on the expected travel mode. Here are the rules: If the distance is greater than 100 miles, the visitor will fly; if the distance is greater than 35 miles, the visitor will drive; if the distance is less than or equal to 35 miles, the visitor is local. Here is how a simple IF statement works: =IF (X is greater (or equal or less than) Y, “Answer if true”, “Answer if false”). Expressed as a formula of only two modes of travel, the IF statement would look like this: =IF(F11>100,”Fly”,”Drive”). However, you are solving for three modes of travel so you will need to create a “nested” IF Statement. It will look like this: =IF(D11>100, “Fly”, IF(D11>35, “Drive”, “Local”))

Source: Used with permission from Microsoft.
Finally, to find the news category in Column N, you will use the VLOOKUP function to quickly find the news category that equates to the news source named in Column M. You will have to provide the location for Excel to look for the options. In this case, the table is located in Section 2 (shown below). You will need to know the cell locations of the two columns to create this formula. Here is the formula:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
News Category Key
Primary news source News category Count of news category %
Facebook Social media
Print news Hardcopy
Online news sites Electronic
Radio Traditional
TV Traditional
TOTAL 0
Source: Used with permission from Microsoft.
You will direct Excel to look at the value in M11 (the news source) within the array (two columns of this table that starts in F1052 and ends in G1056), and then find the correlating value in a position number. If the News Source is 1 and the Category is 2, you will be looking for position 2. The last value of this formula gives options for a range; you don’t want this, so you will answer False. Because you want the location of the array to remain constant, you will use the $ signs around the column letters in the array. Refer to the example below:

Source: Used with permission from Microsoft.
To view videos that explain these formulas, please refer to Step 1 under the link entitled Access Tutoring Help and Other Resources. The videos were created for another class but pertain to a similar data set.
Remember, if you have any questions, please ask!
Step 4: Use Functions to Summarize the Data
In this step, you’ll begin to see patterns in the data that inform the “story” of the data table that you have prepared up to this point.
Note: You should strive to work through the first four steps this week. Take a breather here if you need it. Check in with your instructor as needed.
Click to reveal instructions for MSC students.
You are now ready to move into Section 2 and prepare the data for future analysis. You will include some simple statistical analyses as well as charts and graphs to present the data. Start by presenting the categories of data in summary tables, then counting them, totaling them, and calculating percentages. This basic analysis helps you begin to describe patterns in the data and starts to form the “story” of the resort’s customer base.
Complete each table in Section 2. Use the Countif Function to count each item in each table. Use the Sum Function to total the tables when required. Calculate percentages for each table as required. Format cells appropriately. Remember to make smart use of reference cells in formulas by avoiding typing in numbers or text into formulas; instead, point to other cells and use mixed and fixed cell references to make copying formulas faster and easier. Your supervisor will look for your appropriate use of these tools!

Source: Used with permission from Microsoft
Starting with Visitor Careers, tally the numbers by using COUNTIF, filling in the elements of the formula: =COUNTIF(Range, Value). The range will be the column that you are using for the count, in this case C. The value will be “Accounting” to start. If you want the formula to repeat down the column, put $ around the column letters in the range so it will remain constant. In this case, the range will be expressed as starting and ending cell separated by a colon or $C$11:$C$1010. The value will be the cell location of the career. Give it a try in the tables in Section 2.
Now, scroll down to Section 5 to forecast changes in the Source of News for Individuals in Section 5. There are several ways to predict change over time based on previous results and patterns. In this table, we are trying to forecast the direction of the two types of news sources for the period 2020–2024 based on the patterns of use from 2009–2019. There are several forecast tools in Excel. We will use a formula that projects future movement based on past patterns, and we will calculate each source separately.
The formula is =Forecast{@range of years to solve, known values, known years}. We will solve for the News for TV first, so plug in the cell values to get the answer. The formula will look like this: =Forecast{@C1128:C1132, D1117:D1127,C1117:C1127). See the example below. Solve for the years 2021–2024, then use the formula to forecast the performance of news from the internet using the same principles. (Keep in mind, the @ sign has a specific function in arrays in newer Excel versions as an implicit intersection operator.) You will refer to this table and the line chart you will create in Step 7 for your final analysis. Good luck!

Source: Used with permission from Microsoft
Take Note
Don’t forget to format cells appropriately. Make smart use of reference cells in formulas (avoid typing in numbers or text into formulas—instead, point to other cells), and use relative and absolute cell references to make copying formulas faster and easier. Your supervisor will look for your appropriate use of these tools!
With this step complete, proceed to the next step, where you will begin your analysis.

Step 5: Add Information to Your Spreadsheet
In this step, things get interesting! You will expand your analysis by employing descriptive statistics, or summary statistics, using Excel formulas. Now you will calculate mean, median, and mode for the categories of data and derive the deviation, variance, dispersion, and distribution. Format all the results to two decimal places.

Click to reveal instructions for Strategic Communications (MSC) students.

You will now fill in the summary/descriptive statistics table in Section 3, using Excel formulas. In this section, you will not be able to copy the formulas. After this work, you will appreciate using the Excel Toolpak functions for the next part of this project.

Source: Used with permission from Microsoft
Step 6: Use the Data Analysis Toolpak
Now that you have calculated descriptive statistics using individual Excel functions, we’ll look at another approach. Did you know that you can generate the same descriptive statistics in one easy step?
Now, you will use Excel’s built-in Analysis Toolpak, an add-in that allows you to work with statistics and confirm the answers of your summary statistics. It will help you to save time by performing various complex analyses based on your needs.
You will first need to make sure the toolpak is enabled. Feel free to references How to Enable Data Analysis Toolpak for assistance. When you have completed that process successfully, you will see the words “Data Analysis” or an icon on the top right corner on the Data tab. Select Data Analysis and then choose Descriptive Analysis from the list.
Note: There may be some minor differences in the answers depending on the version of Excel you are using. Mac users will need Excel 2016 or later to download the toolpak.
Then proceed to the instructions that match your current program to calculate the statistics using the toolpak:
Click to reveal instructions for Strategic Communication (MSC) students.

Source: Used with permission from Microsoft.
To complete the descriptive statistics, please insert the input range. You can perform these calculations in one step by highlighting the adjacent D–H columns of data (D10: H1010) or by typing in these inclusive cells. Check Labels and Summary Statistics. The output will be a new sheet (tab). Label the tab “Excel Summary Stats.” Once you space out the information, it should be readable and close numerically to what you did manually with Excel formulas.

Step 7: Create Visual Representations of the Data: Charts and Graphs
Where would we be without the ability to view data in charts? It is sometimes easier to grasp the context of data if we can see it captured in an image. Graphs and charts help readers digest and interpret information more quickly, consistent with the familiar adage “a picture is worth a thousand words.”
Working with Excel Charts will provide an overview of the type of charts available such as pie and bar charts. Refer to it to create a histogram along with Use the Analysis Toolpak as needed.
Click to reveal instructions for Strategic Communications (MSC) students
Create the following graphs in your workbook on a separate tab, Graphs & Charts:
A. Create separate pie charts that show the percentage breakdown by (a) education and (b) travel classification.
B. Create separate bar charts that show the (a) level of support for the ski idea, (b) news category, and (c) interest in environmental issues.
C. Create a line chart to represent the two types of news sources (based on the table in Section 5). Please include data labels so the numbers are available in the charts.

Source: Used with permission from Microsoft
D. Your last task in this step is to create a histogram that shows the spread of visitor income in $10K increments from $50K to $200K. A histogram provides a strategic visual look that can help managers to perceive patterns that matter. In this case, it is helpful for marketing and public relations managers to understand where the “highs” and “lows” occur among the range of visitors, so this histogram will display the number of visitors that fall into different income brackets. Consider the patterns you see as you write your final essay.
You can find the data for this task in Section 4: Complete the Histogram. Using the data table, use the Data Analysis choice to create a histogram using the Data Analysis toolset.

Source: Used with permission from Microsoft.
To create the histogram, the required input cell range must come from the Income Data column (E11:E1010). The bin range information is provided in Section 4 in the Data tab. Copy and paste the cell numbers for the income “buckets” from the table into the Bin Range area. Check New Worksheet and Chart Output.

Source: Used with permission from Microsoft
Excel will then produce a frequency table and chart on a new tab. Please label the tab “Histogram.”

Source: Used with permission from Microsoft.

Step 8: Copy and Sort Data
You’ve accomplished a lot with your data set, summary stats, and histograms. In this step, you will copy and sort data in an Excel worksheet and create a tab for sorted data. You will be able to use this rearrangement of data when you are conducting quantitative analysis. This skill is useful for reporting purposes and can be applied to any Excel application.

Click to reveal instructions for Strategic Communications (MSC) students.
In this step, you will show the summary for total annual disposable income for each of the news categories.
1. Right-click the Data tab to get a menu of choices.

Source: Used with permission from Microsoft.
2. Click Move or Copy.
3. Check the box Create a Copy. Click OK. This will create a duplicate of the page. Relabel the new page “Sorted Data.”
4. Delete all content below the table in Section 1, except the section used as reference for the VLOOKUP. If you delete this referenced section, you will get an error in your news category column.
Primary news source News category Count of news category %
Facebook Social media
Print news Hardcopy
Online news sites Electronic
Radio Traditional
TV Traditional
TOTAL 0
5. Now you will use the SUBTOTAL function in Excel to sort by type of news category in Column N.
6. Select Column N:10 with the table heading, then select the Data tab and click Filter (with the funnel icon). Sort the data by news category, A – Z.
7. Select the entire table with all the data (including heading).
a. Click on cell A10.
b. Hold down the CTRL and SHIFT keys.
c. Press the right arrow (keeping CTRL and SHIFT held), then the down arrow (CTRL and SHIFT held).
8. Click the DATA tab in the Excel ribbon, then click Subtotal. Complete the dialog box as shown below:
a. At each change in: New Category.
b. Use function: Sum.
c. Add subtotal to: Annual disposable income. (Scroll up and down to make sure nothing else is selected.)
d. Check Replace Current Subtotals and Summary Below Data.

Source: Used with permission from Microsoft.
Now it is time to move on to the analytical questions in Step 9.

Step 9: Submit Your Completed Workbook with Responses and Analysis
You’ve done a lot of work and should now be prepared to manipulate data fields, analyze data, and create reports that your boss may request in the future. You’ve learned how to create a multi-tabbed workbook in Excel and explored many ways data can be manipulated and presented to support your summaries and findings.
You’re now ready to complete your analysis of the data and finish the project. Once you have answered some questions to help you refine your analytical ideas, please write a short essay about what the data reveals to you, and arrange the tabs according to the instructions. You can then submit your workbook in the Project 4 assignment folder. Good job!
Click to reveal instructions for Strategic Communications (MSC) students.
In this step, your hard work bears fruit. What does it all mean? Your boss tasked you with providing an analysis of data that may help her come to conclusions about the viability of adding a luxury skiing feature to the resort.
The questions may help you to interpret the data. Be sure to review each tab again to ensure that you can base your conclusions on the data.
Your 250- to 300-word essay will include the following:
• a one-paragraph narrative summary of your findings, describing patterns of interest
• an explanation of the potential relevance of such patterns
• a description of how you would investigate further to determine if your results could be perceived as good or bad for the company
You may find it helpful to create your essay in a Word document before copying and pasting it into the text box on the QR Analysis tab.
When you have completed your essay, review the order of tabs:
• Tab 1: QR Analysis
• Tab 2: Data Sheet (provided)
• Tab 3: Summary Stats
• Tab 4: Graphs & Charts
• Tab 5: Histogram
• Tab 6: Sorted Data
Submit your report and Excel workbook in the Project 4: Quantitative Analysis assignment folder below.
Check Your Evaluation Criteria
Before you submit your assignment, review the competencies below, which your instructor will use to evaluate your work. A good practice would be to use each competency as a self-check to confirm you have incorporated all of them. To view the complete grading rubric, click My Tools, select Assignments from the drop-down menu, and then click the project title.
• 1.1: Organize document or presentation clearly in a manner that promotes understanding and meets the requirements of the assignment.
• 1.2: Develop coherent paragraphs or points so that each is internally unified and so that each functions as part of the whole document or presentation.
• 1.4: Tailor communications to the audience.
• 1.5: Use sentence structure appropriate to the task, message and audience.
• 1.6: Follow conventions of Standard Written English.
• 3.1: Identify numerical or mathematical information that is relevant in a problem or situation.
• 3.2: Employ mathematical or statistical operations and data analysis techniques to arrive at a correct or optimal solution.
• 3.3: Analyze mathematical or statistical information, or the results of quantitative inquiry and manipulation of data.
• 3.4: Employ software applications and analytic tools to analyze, visualize, and present data to inform decision-making.