MS3251 Analytics Using SAS Assignment 2 You must complete the assignment by

MS3251 Analytics Using SAS

Assignment 2

You must complete the assignment by yourself. Exchanging ideas with classmates are encouraged, but you must not cross the line between discussion and collaboration. Showing your work to your classmates will be considered as a kind of collaboration.

Complete all tasks. Put all of your SAS code into one PLAIN TEXT file or in PDF format. Name your code file as nnnnnnnn.txt or nnnnnnnn.pdf where nnnnnnnn is your EID (login name) at CityU. Do not submit your file in Word format or in .SAS extension.

You should set all irrelevant statements in your code as SAS comment statements. Be aware that if you create your SAS code under a non-English operating system, it may contain extraneous characters when viewed in an English operating system. It is your responsibility to ensure that your submitted code is free of these characters. All extraneous characters in your submitted code will be considered errors.

Your submitted SAS code should be free of error when running in SAS Studio.

You must submit your code file via the link Assignment 2 under the Assignments section of the course on Canvas. If you submit your file more than once, only the latest submitted file will be marked. The link will be closed on the 30 November, at 11:59 pm. Late submission will not be accepted. Submission by other methods will not be accepted.

Tasks

Download the zipped file Assignment2.zip from the folder Data Sets under the Files section. Expand the zipped file for the following SAS data sets:

Demographics.sas7bdat: contain demographic information of all members.

Flying_Activities.sas7bdat: contain members’ flying activities between 1 Jan 2018 and 31 Dec 2021.

You can find the variable dictionary Assignment2_Variabledictionary.xlsx. Do not change the contents of these data sets unless you are told to do so explicitly.

Write a SAS program for the following tasks. Mark the code of each task by using a proper comment statement, such as ‘/*Task 2a*/’.

Task 1:

Write a LIBNAME statement to define a SAS library in SAS Studio so that you can access the above-described data sets (and any others) via the library.

Task 2:

2a: Write a DATA step to create a SAS data set named as Demographics_New. This data set must contain all observations and variables in Demographics and a new variable called Tenure. Tenure is defined as the number of full months from an observation’s Join_Date till 1 Jan 2021. For example, if an observation’s Join_Date is 2 Jan 2020, then the observation’s Tenure value is 11 months. Place Demographics_New in the library created in Task 1. {Hint: SAS function INTCK(‘month’, ‘2jan2020’d, ‘1jan2021’d,’continuous’) returns a value of 11 months. The ‘month’ and ‘continuous’ are system keywords for the INTCK function.}

A subset of the observations in Demographics_New is shown here for illustration purposes:

2b: Write a DATA step to modify the Gender values in Demographics_New so that if an observation’s Gender value is either ‘f’ or ‘m’, replace it by ‘F’ or ‘M’, respectively. Name the modified data set as Demographics_New again and keep it in the library created in Task 1. (Be aware that this new Demographics_New will overwrite the original Demographics_New.)

2c: Write appropriate SAS procedure statements for the purpose of comparing the average Tenure between male and female members in Demographics_New.

Task 3:

3a: Write appropriate SAS procedure statements for sorting the data set Flying_Activities by the variable Member_Id. Name the sorted data set as Sorted_Activities and place it in the Work library of SAS Studio.

3b: Write a DATA step to reshape Sorted_Activities by collapsing the observations with the same Member_Id value into a single observation. Name this new SAS data set as Collapsed_Activities and place it in the Work library of SAS. Create the following variables and keep only these variables, but not necessary in the listed order, for each observation in Collapsed_Activities:

Variable Name

Description

Member_Id

Member’s identity.

Air_CityU_2018

Number of times flew on CityU Airline (i.e. Airline value = ‘CityU’) in 2018.

Air_CityU_2019

Number of times flew on CityU Airline in 2019.

Air_CityU_2020

Number of times flew on CityU Airline in 2020.

Air_NonCityU_2018

Number of times flew on non-CityU Airlines (i.e. Airline value ^= ‘CityU’) in 2018.

Air_NonCityU_2019

Number of times flew on non-CityU Airlines in 2019.

Air_NonCityU_2020

Number of times flew on non-CityU Airlines in 2020.

FlyBonus_Earned_2018

Total bonus points earned from flying in 2018.

FlyBonus_Earned_2019

Total bonus points earned from flying in 2019.

FlyBonus_Earned_2020

Total bonus points earned from flying in 2020.

A subset of the observations in Collapsed_Activities is shown here for illustration purposes:

Task 4:

4a: Write appropriate SAS procedure statements for sorting the data set Demographics_New as created in Task 2b by the variable Member_Id in ascending order. Replace the original Demographics_New with the sorted data set.

4b: The data set Demographics_New is supposed to contain all members’ information. However, However, not all members in Collapsed_Activities are also contained in Demographics_New or vice versa. Write one DATA step for creating three SAS data sets. The three data sets must have these characteristics:

One data set contains only the observations of Collapsed_Activities with Member_Id that do not appear in Demographics_New. Name this data set Redundant_Activities and place it in the Work library of SAS Studio. Keep only the variables of Collapsed_Activities in Redundant_Activities.

A second data contains the observations of Collapsed_Activities with Member_Id that also appear in Demographics_New. Name this data set Collapsed_Activities2 and place it in the Work library of SAS Studio. Keep only the variables of Collapsed_Activities in Collapsed_Activities2.

A third data set contains the observations of Demographic_New with Members_ID that do not appear in Collapsed_Activities. Name this data set Inactive_Members and place it in the Work library of SAS Studio. Keep only the variables of Demographic_New in Inactive_Members.

You must accomplish Task 4b with one DATA step only.

A subset of the observations in Redundant_Activities is shown here for illustration purposes:

A subset of the observations in Collapsed_Activities2 is shown here for illustration purposes:

A subset of the observations in Inactive_Members is shown here for illustration purposes:[supanova_question]

5350413.02 Ontario [Census tract] 5350194.02 Ontario [Census tract] Population 2016 12,789 6,240

5350413.02

Ontario

[Census tract]

5350194.02

Ontario

[Census tract]

Population 2016

12,789

6,240

Population change 2011-2016 (%)

73.8

10.3

Population density per km sq

1,977.3

18,013.9

% Male

100%

100%

% Female

100%

100%

Population by major age groups (% 0-14 yrs., -64 yrs., yrs. and over)

0 to 14 years: 29.3%

15 to 64 years: 65%

65 years and over: 5.7%

0 to 14 years: 25.7%

15 to 64 years: 60.7%

65 years and over: 13.5%

Average age of the population

31.4

35.4

Average household size

3.7

3.1

1 person households (count & %)

Count: 190

Percentage: 5.5%

Count:460

Percentage:23.9%

Average household income

$140,852

$57,730

Household income – $200,000 or more

Count:515

Count:20

Prevalence of low income (%)

Based on the Low-income measure, ager tax: 8.1%

Based on the Low-income cut-offs after tax: 6.3%

Based on the Low-income measure, ager tax: 47.2%

Based on the Low-income cut-offs after tax: 37.4%

Total visible minority population (%)

50.1%

81.5%

No certificate, diploma or degree (%)

The population aged 15 years and over in private households: 16.6%

The population aged 25 to 64 years in private households: 10.7%

The population aged 15 years and over in private households: 22.2%

The population aged 25 to 64 years in private households: 18.5%

Marital status – Not married and not living common law (count)

2,730

2,015

COVID-19- Relevant indicators – class of worker

Total labour force aged 15 years or above: 6,785

All classes of workers: 6,670

Self-employed: 960

Total labour force aged 15 years or above: 2,255

All classes of workers: 2,065

Self-employed: 315

COVID-19- Relevant indicators – population density per square kilometre

1,977.3

18,013.9

Age groups–100% data, both sexes

Ethnic origin for the population in private households–25% sample data, both sexes

Occupied private dwellings by structural type of dwelling–100% data

Household total income groups in 2015 for private households–100% data

Part C Discussion

Part D Prospective market areas

Census tracts 5350413.02 & 5350194.02

a) Private automobiles

Owning a private automobiles are considered based on couple factors including the average income, household size (per person to family), journey to work (public transit), education is associated with income level and population density

b) compact furniture

Household size (1 person household)

The size of the dwellings

Low income families and minorities (racism, discrimination)

c) Dating services

Marital status (single or couples)

Age will affected the frequency of using the dating service

Socio economic status (financial status)

d) Recreation & Entertainment[supanova_question]

HRM 4033 Managing Communications and Emotional Intelligence for HRM ASSESSMENT 3 PROJECT

HRM 4033 Managing Communications and Emotional Intelligence for HRM

ASSESSMENT 3 PROJECT REPORT and PROJECT INTERVIEW

Project Details:

Individual Project Report of not less than 2500 words and Individual Project Presentation;

Pick 1 topic from: Communicating Strategically; Team Building; Crisis Communication; Conflict Resolution; Developing Others; Collaboration.

Section 1: Write a case scenario of not less than 500 words on the topic you have chosen. Tell the story of a problem. Remember to give the background to the problem.

Section 2: Apply the emotional intelligence tools you have learnt on this course to the situation. (not less than 1000 words)

Section 3: Analyse the role of communication and emotional intelligence in describing the solution to the problem. (not less than 750 words)

Section 4: Evaluate your solution or solutions to the problem (not less than 250 words).

Individual Project Report Rubric:

Section 1: Write a case scenario of not less than 500 words on the topic you have chosen. Tell the story of a problem. Remember to give the background to the problem. 10 marks

Scenario includes all topics taught in this course

Scenario includes many topics taught in this course

Scenario includes few topics taught in this course

Scenario includes a very few number of topics taught in this course

Scenario does not include topics taught in this course

9-10 marks

6-8 marks

3-5 marks

0-2 marks

0 marks

Total: /10

Section 2: Apply the emotional intelligence tools you have learnt on this course to the situation. (not less than 1000 words) 40 marks

9-10 marks

6-8 marks

3-5 marks

0-2 marks

0 marks

Student very satisfactorily applies the emotional intelligence tool of self-awareness

Student satisfactorily applies the emotional intelligence tool of self-awareness

Student moderately applies the emotional intelligence tool of self-awareness

Student poorly applies the emotional intelligence tool of self-awareness

Student does not apply the emotional intelligence tool of self-awareness

Student very satisfactorily applies the emotional intelligences tool of awareness of others

Student satisfactorily applies the emotional intelligences tool of awareness of others

Student moderately applies the emotional intelligences tool of awareness of others

Student poorly applies the emotional intelligences tool of awareness of others

Student does not apply the emotional intelligences tool of awareness of others

Student very satisfactorily applies the emotional intelligence tool of empathy

Student satisfactorily applies the emotional intelligence tool of empathy

Student moderately applies the emotional intelligence tool of empathy

Student poorly applies the emotional intelligence tool of empathy

Student does not apply the emotional intelligence tool of empathy

Student very satisfactorily applies the emotional intelligence tool of communication

Student satisfactorily applies the emotional intelligence tool of communication

Student moderately applies the emotional intelligence tool of communication

Student poorly applies the emotional intelligence tool of communication

Student does not apply the emotional intelligence tool of communication

Total: /40

Section 3: Analyse the role of communication and emotional intelligence in describing the solution to the problem. (not less than 750 words) 30 marks

22-30

15-21

8-14

1-7

0

Solution very satisfactorily reflects the role of communication

Solution satisfactorily reflect the role of communication

Solution moderately reflects the role of communication

Solution poorly reflects the role of communication

Solution does not reflect the role of communication

Solution very satisfactorily reflects the role of emotional intelligence

Solution satisfactorily reflect the role of emotional intelligence

Solution moderately reflects the role of emotional intelligence

Solution poorly reflects the role of emotional intelligence

Solution does not reflect the role of emotional intelligence

Total: /30

Section 4: Evaluate your solution or solutions to the problem (not less than 250 words). 20 marks

16-20

11-15

6-10

0-5

0

Very good evaluation

Good evaluation

Poor Evaluation

Very Poor Evaluation

No evaluation

Total: /20

Overall Total: /100

Individual Interview Rubric:

9-10

6-8

3-5

0-2

0

Knowledge of scenario

Student describes the scenario displaying a very satisfactory level of knowledge

Student describes the scenario displaying a satisfactory level of knowledge

Student describes the scenario displaying a moderate level of knowledge

Student describes the scenario displaying a poor level of knowledge

Student describes the scenario displaying no knowledge

Marks: /10

Knowledge of solution to scenario

Student describes the solution displaying a very satisfactory level of knowledge

Student describes the solution displaying a satisfactory level of knowledge

Student describes the solution displaying a moderate level of knowledge

Student describes the solution displaying a poor level of knowledge

Student describes the solution displaying no knowledge

Marks: /10

Knowledge of evaluation of solution

Student describes the solution displaying a very satisfactory level of knowledge

Student describes the solution displaying a satisfactory level of knowledge

Student describes the solution displaying a moderate level of knowledge

Student describes the solution displaying a poor level of knowledge

Student describes the solution displaying no level of knowledge

Marks: /10

Question 1 by examiner

Very Satisfactory response

Satisfactory response

Moderate response

Poor response

No response

Marks: /10

Question 2 by examiner

Very Satisfactory response

Satisfactory response

Moderate response

Poor response

No response

Marks: /10

Total

/100

/50

3 | Page[supanova_question]

Case Study: Data, Information, Knowledge, Wisdom (DIKW) Analysis in a Real-World Information

Writing Assignment Help Case Study: Data, Information, Knowledge, Wisdom (DIKW) Analysis in a Real-World Information System. Your project should be no more than 4 pages excluding title page, reference, appendix, and peer evaluation. You should include the following information in your paper:

TOPIC FOR DISCUSSION: BLOOD PRESURE MONITOR

• Select a simple information system used in clinical setting

• Identify the purpose of the system and the clinicians who use it.

• Describe how data are captured and displayed, including the timeframe in what data are collected, how and where data are stored, and how data are displayed.

• Identify the strengths and weaknesses of the process to obtain the data, including risks for error in obtaining data. Explain potential reasons for unintended consequences and anticipate potential work-arounds that could threaten patient safety when the system is used incorrectly.

• Describe the knowledge synthesis required to transform these data into information then knowledge to arrive at a clinical decision. Provide a clinical example.

• Explore how wisdom- clinical experiences and expertise- guide clinicians to implement appropriate interventions when analyzing the data and information the system generates. Explain how wisdom influences nursing practice.

Please follow APA 7 formatting. Must cite at least three (3) peer reviewed articles (not exceed 5 years) in addition to your textbook. You are encouraged to use subtitles in your paper to make organization of your paper clearer. [supanova_question]

Purpose The only way to have any successful growth on any social

Purpose

The only way to have any successful growth on any social media tool is to monitor, review and adjust content on a consistent basis. Weekly or monthly reporting (depending on how contingent your business is on social media) should be provided by the social media manager to the rest of the marketing team. 

Reports are based on:

1. Overall business objectives

2. Social Media Marketing objectives

3. Chosen metrics

4. Chosen Key Performance Indicators (KPIs)

(Definitions found on pages 230 & 231 of our textbook) – SEE ATTACHED

While there are many tools available now that can create full reports (see chapter 14 of our textbook), the structure of the report and what specific items are included are 100% dependent on the objectives set by the business. For example, showing how much engagement is going on inside of Instagram has no value if the objective for the company is traffic to their eCommerce website. What would be more helpful would be to show how much traffic came to the eCommerce website via Instagram and what type of Instagram post drove the most conversions. 

Instructions

Using THE SAME company that you used for your first three assignments (APPLE), create an example monthly analytic report.

Part 1

Your report should start with these elements:

1. Overall business objectives – Look and see if the company has posted any goals on their website or any published annual reports. If you are able to talk to an employee ask them if their leadership has spoken about any larger measurable goals. A goal can be as broad as “10% total company sales increase over the next year” or as targeted as “20% increase in market share on college campuses,” “successful launch of our new product,” or “50% increase in sales of red solo cups.” 

2. Social Media Marketing objectives – This is developed based on the overall business objectives but is translated to how social media will impact the objective (ex: “successful launch of our new product” would translate to a brand launch campaign in each social media platform, with possible objectives of “75% of social conversation centering around the new product” and “10% increase on traffic to the eCommerce site from social”) . 

3. Chosen metrics – BE SPECIFIC and list out every single social media platform and the chosen desired metrics (ex: number of likes/shares or engagement rates/click-throughs) for that platform. See page 232 in our textbook for details in choosing metrics and page 234 for a great list of example metrics.

4. Chosen Key Performance Indicators (KPIs) – list out the specific tangible goals we will be looking to achieve and what the indicators are that we have achieved it. Pages 238-9 has some great examples.

Please Note: The chart on page 238 of our textbook has a GREAT example of all of the above elements in action (and well designed). -SEE ATTACHED

Part 2

The actual analytics. Map out the data from each platform for the month and call out how they have measured up to the KPIs. (More instructions below)

Here is an example layout (but please be more creative than this!)

You may be thinking….”But Professor?!?!?! How do I get data from the social media platforms or Google Analytics if I don’t actually work as a social media manager for the company? Chapter 14 shows me all kinds of tools to use but don’t most of them need direct access to the platforms???”

Yes, when you have access to the platforms the social media tools themselves will give you the data you request. Since we don’t have access to the platforms (unless you know someone who works there who does), we are going to have to do our research here and gather information via the social media listening processes instead. For example, if your KPI is an increase in Instagram post likes, then you are going to have to browse through your brand’s Instagram page and compare earlier posts to current posts to gage if their likes have gone up consistently over the past month compared to the month prior. 

There are also a number of “sleuthing” tools available for free that can be used to measure public data that can pertain to your brand, here are a few (you can find your own too):

https://trends.google.com/trends/?geo=US (Links to an external site.) (keyword rankings, etc)

https://www.trackalytics.com/ (Links to an external site.)

https://www.similarweb.com/website/ (Links to an external site.) (put your company website URL in next to their “start” button, it doesn’t have all websites, but it has some)

https://buzzsumo.com/ (Links to an external site.)

https://www.spyfu.com/ (Links to an external site.)

https://builtwith.com/ (Links to an external site.)

Part 3

Your analysis and next steps. Does the data show you met your KPIs? Do you need to tweak your strategy? Should you do more of a certain type of post? 

Write your analysis of the data and what you will be doing as a result.

Grading Criteria

Please Note: STYLE MATTERS. I grade each of these assignments as if they were a report given to the CEO of the company by the social media manager. Make sure your layout is easy to digest and add graphics, example posts, etc. 

Acceptable file formats: PDF (including downloaded Google Slides or Docs), Word Document, Powerpoint. Video files (if used) can be submitted as embeds, separately, or via Google Drive.

Assignments will be graded on how thoroughly they meet the following criteria:

Content

Is a thorough analytic report that would help the chosen company achieve a goal

Accurately presents the target company chosen – and uses the SAME company as previous assignments

Provides sufficient evidence to support the content created

Content created is well presented

Structure

Includes all listed elements

Clearly organized

Style

Is visually appealing (photography and colored layout is appreciated)

Is free of misspellings

Is free of grammatical mistakes[supanova_question]

Assignment 2 Ratio Analysis – Some Recommendations & Data Sources The principal

Assignment 2 Ratio Analysis – Some Recommendations & Data Sources

The principal objective of Assignment 2 is to prepare and present a comprehensive financial ratio analysis. The Assignment Instructions carefully explain what is to be included in the analysis and directions for preparing the written analysis.

To facilitate preparation of Assignment 2, here are some suggestions:

Where to Find Ratio Data

There are numerous sources of company financial and ratio data. One very useful site is Mergent Online, which can be accessed through the UMUC Online Library (accessible through the online classroom). To access the ratio data:

Access the UMUC Library online

Select “Databases by Title”

On the Alphabetical List Select “Mergent Online”

Input Your Company’s Name

Select “Company Financials”

Select “Ratios”

Where to Locate Industry Data

There are various places to find industry data including ratios. It’s probably best to just use the UMGC library database at

 https://libguides.umgc.edu/c.php?g=970568&p=7014314 for finding industry overviews and to

 https://libguides.umgc.edu/c.php?g=970568&p=7115107 for finding industry financial ratios.

How to the Present the Ratio Analysis

The Instructions for Assignment 2 explain the various sections required to be included in the written analysis. These include the Introduction, Presentation of the Ratio Analysis, Strengths and Weaknesses Analysis, Summary, and the List of References.

A key part of Assignment 2 is the presentation of the Ratio Analysis. Here is an example of a ratio analysis. It may offer some ideas about how to present ratio data and conduct that portion of the required analysis. Remember, though, this example only pertains to the ratio analysis section of the report; there are other sections that need to be developed.

Some additions and other comments that may improve this analysis are:

1. Include industry average ratios or competitors’ ratios. This is known as “cross-sectional” analysis. This is required for Assignment 2.

2. It would be useful to include figures “graphs” of the historical ratio data. In addition to the table, this would facilitate the presentation of the “historical” or “trend” ratio analysis.

3. Include a statement or sentence that summarizes the evaluation of each group of ratios – is the company’s performance good, not so good, needing improvement, strong, etc.?

4. As is done with this example, always include the actual ratio data (the actual ratio numbers) in the written analysis in addition to the presentation in the table.

5. Good points about this analysis: (1) It uses the Mergent financial ratio data that are available from the UMUC online Library; (2) The ratios are presented in a table, which is numbered and titled; (3) Several years of ratio data are presented; (4) Actual ratio numbers are used in the written analysis; (5) An effort is made to evaluate the ratio data; and, (6) The ratio analysis is divided into appropriate sub-sections.

***********************************

Trend Analysis

Five-year timelines will be used for historical and forecasted data for comparison purposes (red highlighted items reflect areas of interest):

Annual Growth Rates Analysis

Historical (2011 – 2015). Sales grew 5.4%, 5.6%, 11.5%, and 7.8% during the historical period at an average yearly rate of 7.6% and an actual growth rate of 33.79% over the period. Assets grew 11.6%, 17.4%, 21.0%, and 2.2% at an average yearly rate of 13.05% and an actual growth rate over the period of 62.11%. Common equity decreased from 16.3% to -10.8% over the period (-166.26%) due to negative growth rate in 2015 contributable to a reduction in retained earnings. Earnings, however, was sporadic contributable to a large non-operating loss every third year. Earnings dropped -26.7% in 2012, increased 28.8% in 2013, increased 1% in 2014, but decreased again -44.8% in 2015 for an actual decrease of -47.33% over the period. Microsoft’s sustainable growth rate is lowest in the years of the large non-operating loss and reflects 19.5%, 15.2%, and 2.5% in the last three-years.

Forecasted (2016 – 2020). Sales are projected to decline in 2016 by -6.9% attributed to Microsoft’s transition period, and then continue to grow at a declining rate from 9.3% to 7.2%. Over the period sales are projected to increase 37.30%, which is comparable to the historical timeframe. Assets are predicted to grow 36.32% (a lot slower than the 62.11% historical data); caused by a forecasted negative gain in 2016. Common equity is expected to increase from -3.1% to 6.8% over the period (+319.35%) due to several positive growth rates across the period (a reverse of the historical data). Projected earnings will continue to be sporadic, contributable to the pattern of large non-operating loss every third year. Forecasted earnings are anticipated to increase 67.2% in 2016, increase 12.0% in 2017, decrease -31.3% in 2018, increase 66.7% in 2019 and increase 9.2% in 2020 for a projected growth rate of 40% over the period (a reverse of the historical data). Microsoft’s sustainable growth rate is also lowest in years of a projected continued pattern of large non-operating loss, but expected to average at 10.98% across the forecasted years.

Forecasted (2021 – 2027). Sales are expected to grow at a steady declining rate through the rest of the eVAL forecast from 6.5% until the terminal rate of 3%. Similarly, assets and common equity are also projected to grow from 6.3% to 3% and 6.1% to 3% respectively. Conversely, earnings and sustainable growth rate are forecasted to follow the same pattern as the historical data and earlier forecast.

Profitability and Margin Analysis

Historical (2011 – 2015). The company’s return on equity for 2012 – 2014 averaged .279, decreasing by -47.64% to .144 in 2015 with the $9,650 million non-operating loss.   Similarly, return on assets averaged .227, decreasing by -52.02% to .107 in 2015 for the same reason. Microsoft’s gross margin decreased -13.39% (.814 to .705) over the five-year period, as did the EBIT margin (-22.42%) (.388 to .301) and net operating margin (-59.88%) (.324 to .130). The large drop in net operating margin attributable again to the non-operating loss.

Forecasted (2016 – 2020). The company’s return on equity is expected to increase 8.49% over the period (.259 to .281), with a decrease from .282 to .178 in 2018 consistent with a projected non-operating loss. Similarly, return on assets is forecasted to increase 8.89% over the period (.180 to .196), but decrease from .197 to .124 in 2018 for the same reason. Microsoft’s gross margin is expected to steadily decline -1.76% over the five-year period (.738 to .725), while EBIT margin is anticipated to remain around .308 with no change and net operating margin sporadically increase 2.14% (.234 to .239) with a drop to .152 in 2018 attributable to the patterned non-operating loss.

Forecasted (2021 – 2027). Return on equity and return on assets is projected to continue the same pattern as the historical data and earlier forecast. Gross margin and EBIT margin are expected to continue steadily decreasing (.721 to .705 and .307 to .305 respectively), while net operating margin should maintain the same sporadic pattern.

Turnover Analysis

Historical (2011 – 2015). The company’s net operating asset turnover dropped -17.02% from .987 to .819. Similarly, the net working capital turnover decreased -15.88% from 1.448 to 1.218. The average days receivables dropped slightly by -4.10% during the period (from 76.16 to 73.04 days), while the average days inventory increased 18.48% (from 31 to 36.7 days) and the average days payables dropped -12.66% (from 105.10 to 91.79 days).  The aggregate of these average day measures caused the cash conversion cycle (CCC) to increase 774.17% from 2.06 days to 17.97 days over the period.

Forecasted (2016 – 2020). The company’s projected net operating asset turnover is anticipated to increase 6.63% (from .769 to .820). While, the net working capital turnover is forecasted to change from 1.092 to 1.171 (+7.23%). The forecasted average days receivables is expected to drop -6.81% during the period (from 72.44 to 67.50 days), while the average days inventory and average days payables is anticipated to drop -13.08% (from 42.37 to 36.82 days) and -15.70% (from 98.40 to 82.95 days) respectively. The individual average day projections are expected to cause an increase in the cash conversion cycle by 30.31% (from 16.41 to 21.38 days).

Forecasted (2021 – 2027). Net operating asset turnover is projected to increase from .821 to .822, while net working capital turnover is expected to decline from 1.168 to 1.152. Microsoft’s CCC is forecasted to increase as average days receivables increase from 67.72 to 68.83, average days inventory increases from 36.94 to 37.77, and average day’s payables increases from 83.27 to 85.51 days.

Leverage Analysis

Historical (2011 – 2015). In lieu of repatriating overseas funds at a higher tax rate, Microsoft has begun to increase its leverage to fund corporate initiatives. The company’s debt to equity ratio over the historical period increased 92.17% (.230 to .442), while its CFO to total debt ratio decreased -63.39% (1.844 to .675) due to increased leverage and the large non-operating loss in 2015. Subsequent to debt is a company’s ability to service that debt. Microsoft’s current ratio decreased slightly over the period by -3.96% (2.604 to 2.501). The company’s quick ratio also decreased minimally by -.52% (2.306 to 2.294). The reductions were not enough to significantly affect payments. The EBIT interest coverage increased to 1878.13 in 2015 after four years of negative amounts caused by a positive net income expense.

Forecasted (2016 – 2020). Microsoft is on track to continue to use leverage to fund corporate initiatives. The company’s debt to equity ratio is forecasted to increase slightly by .92% (.432 to .436), while its CFO to total debt ratio can expect to increase 29.43% (from .666 to .862). The forecast for Microsoft’s ability to service its debt is promising. The company’s current ratio is projected to decrease slightly over the period by -.39% (2.526 to 2.516). Likewise, the quick ratio is also projected for a slight decrease of -.47% (2.322 to 2.311). The EBIT interest coverage is anticipated to increase 6.96% (from 1517.46 to 1623.09).

Forecasted (2021 – 2027). Microsoft is expected to continue its leverage in lieu of repatriating funds. Debt to equity is projected to increase from .437 to .442 and CFO to total debt from .616 to .807. The company is forecasted to maintain high current and quick ratios, declining from 2.513 to 2.501 and 2.308 to 2.294 respectively. The EBIT interest coverage is also projected to decline from 1618.26 to 1596.41.

Comparative Analysis (CSI Market, n.d.).

The chart below reflects Microsoft compared to the Software and Programming Industry:

Microsoft falls well below standards in its sales (8.41% to Industry’s 13.37%), earnings (-8.26% to .64%), and free cash flow (.92% to 7.02%) growth rates. The negative 5-year average earnings are attributable to a one-time non-operating loss every third year. Conversely, the company is above industry standards with return on equity (26.73% to 21.59%) and return on assets (14.07% to 11.62%). Microsoft falls slightly below standards in gross margin comparisons (72.32% to 74.38%), but through good expense management beats the industry in operating (30.82% to 25.12%) and net margin (24.53% to 19.05%) comparisons.

The company’s asset turnover beats the industry (.52 to .51), but falls short in its receivable turnover (6.26 to 7.95) and inventory turnover (11.38 to 16.57). While Microsoft’s debt to equity ratio is higher than the industry (.39 to .17) because of increasing debt in lieu of repatriating overseas funds, its debt coverage falls substantially short (.72 to 1.55). And finally, the company does well at beating the industry with short-term liquidity. Its current and quick ratio both beat the industry (2.57 to 2.43 and 2.04 to 1.48 respectively).

Strengths and Weaknesses

Annual Growth Rates Analysis

Microsoft’s sporadic earnings are a weakness to its operations. Microsoft’s financials have been clouded by several one-time charges over the years, resulting in very inconsistent and confusing results; making it hard to forecast trends. Harry (2016) comments that the firm has taken so many one-time charges (due to bad acquisitions) that it seems that it is part of the normal operations. Microsoft shows strengths, however, in its sustainable growth rate averaging 12.4% in actual years and 10.98% in earlier forecasted years. This means at a projected sustainable rate of 10.98% the company can grow at that rate without any additional financing. If the firm wishes to grow faster than this number, they will need to incur additional financing.

Profitability and Margin Analysis

Microsoft is doing well on its return on equity and return on operating assets (except in years of one-time charges). The company beats the industry standard on both accounts. Despite its dropping margins over the actual and forecasted periods, Microsoft still maintains a good gross, EBIT, and net operating margin. The firm beats the industry’s EBIT and net operating margin standards, and falls slightly below the gross margin standard. This shows Microsoft’s strength in expense management, to beat the industry’s EBIT and net operating margins, after being below standard in gross margin. Microsoft needs to eliminate the one-time charge every three years to maintain stable margins across the horizon.

Turnover Analysis

Microsoft’s asset turnover is almost the same as the industry’s – meaning it does well at utilizing its assets to generate sales. The company shows strength in its working capital turnover reflecting a high level of working capital to support the generation of sales across historical and forecasted data.

Microsoft’s cash collection cycle averages 18.8 days actual and 20.4 days in the early forecast, which is a great strength, indicating how fast the company can convert the purchase of inventory to cash from its customers. The company shows efficiency in reducing its days collectible, going from 76.16 days in 2012 to a projected 67.50 days in 2020. Conversely, the firm has increased the day’s inventory from 31 to 36.8 days and decreased the day’s payable from 105.10 to 82.95 over the same period, causing a slight increase to the CCC in the forecasted years. Despite the slight increase, the company’s CCC is still a strong strength. The lower a company’s CCC the better its liquidity and the quicker it can pay its debts.

Leverage Analysis

Microsoft’s debt to equity ratio is at a reasonable level, considering its usage of debt to fund operations. However, the declining CFO to total debt ratio over the historical and forecasted period has dropped to below 1.0 and is a weakness that needs addressing. The low long-term CFO ratio does not impede Microsoft’s short-term liquidity where debt payment is the most concern. The company’s current and quick ratios are great strengths providing excellent liquidity to the firm. The current ratio averages 2.5 actual and forecasted, while the quick ratio averages 2.3 actual and forecasted. Both are above industry standards. Additionally, the company shows strength in its EBIT Interest Coverage – 1878.13 in 2015 and the forecasted period averages 1607.69. This indicates how many times the EBIT will cover the debt service.

Conclusion

The continuous innovation and fast pace of the technology sector and software industry will continue to keep companies fighting for that competitive advantage. Microsoft in particular will continue to find its way as the company transitions its focus to the cloud and integrates its core products into that platform. Overall the forecasted ratios show similar patterns to the historical data. Of major concern is that the analysis points to Microsoft continuing to fluctuate in its earnings if the company doesn’t figure out a way to eliminate the “one-time” charges every third year. This will continue to hamper margins, cloud its financial statements, and may keep investors away.[supanova_question]