Analysis essay

Information for this project is downloaded and emailed

look for……Gwynnproject4 Excel info

Start Here
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. Continue with Step 1: Refresh Your Math, Statistics, and Excel Skills.
Competencies
Your work will be evaluated using the competencies listed below.
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.
Step 1
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
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.
Open the Excel file and go to “Save As” to rename it. Use the name format YourLastName Project 4. Save as Gwynnproject4 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.
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.
Once you have completed these tasks, proceed to the next step.

Save as Gwynnproject4
Step 3
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

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

Step 5

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.

Step 6

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 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
In this step, you will show the summary for total annual disposable income for each of the news categories.
Right-click the Data tab to get a menu of choices.

Source: Used with permission from Microsoft.
Click Move or Copy.
Check the box Create a Copy. Click OK. This will create a duplicate of the page. Relabel the new page “Sorted Data.”
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
Now you will use the SUBTOTAL function in Excel to sort by type of news category in Column N.
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.
Select the entire table with all the data (including heading).
Click on cell A10.
Hold down the CTRL and SHIFT keys.
Press the right arrow (keeping CTRL and SHIFT held), then the down arrow (CTRL and SHIFT held).
Click the DATA tab in the Excel ribbon, then click Subtotal. Complete the dialog box as shown below:
At each change in: New Category.
Use function: Sum.
Add subtotal to: Annual disposable income. (Scroll up and down to make sure nothing else is selected.)
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
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

v