DCL 600 UMGC Distribution of The Annual Household Income Project

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.
1. Step: 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-ofproject 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 2
Step 3
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 4
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 5
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 6
Step 7
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
Facebook
Print news
Online news sites
Radio
TV
News category
Social media
Hardcopy
Electronic
Traditional
Traditional
TOTAL
Count of news category
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
Step 7
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