Business Analytics

 

post has two assignments

Scenario

Cleveland Corp. Is an American car parts manufacturer with a factory in Adelaide which employs 2,000 people.The company wishes to study its full-time work force by developing an employee profile that measures factors such as salary , job satisfaction, and care retrogress .
You will be playing the role of John Smith , a Senior Analy stat the company . Senior management of Cleveland Corp has asked you to conduct a survey of their employees .You decide to take a randomise ample of450staffandconductthesurveyviacompanyinternalmail.The results are saved in the data set “Assignment 1 and 2 DATASET- Cleveland.xlsx”.
George Tim ,Human Resources Manager at C level and ,has written to your egarding the C level and employee survey . His letter to you is reproduced below.

For Attention: John Smith, Senior Analyst
From: Tim George, Human Resources Manager
Regarding: Analysis of employee survey
Dear John,

Regarding the recently collected data from the C level and Corp’s employees urvey , please provide me with answerstothefollowingquestions.Yourresponseswillassistourdepartmentinconstructingthe employee profile required by senior management.

1. Please provide an overall summary of employees’ salaries.

2. Please provide an estimate of the proportion of employees who are “Moderately satisfied” or “Very satisfied ”with their job at Cleveland Corp.

3. Senior management is also in terested in how salary is related to gender . Does there appear to be any relationship between an employee’s salary and their gender?

4. Can you please provide an overall summary of how Cleveland Corp employees rated their relations with the management?
5. Senior management sinter ested in which numerical variables, if any, see mtoinfluencean employee’s salary .Please provide a summary of these relationships if any.

Present your written findings in a report(no more than two pages), with an appendix (no more than eight pages) containing the results of your analysis, by no later than 5.00pm on Thursday 17 August 2017.

Part1: Data Analysis

To prepare a reply to the manager’s letter, you will need to use Excel to analyse the data set “Assignment 1 and 2 DATASET – Cleveland.xlsx”thoroughly.
Apply techniques you have learned during lectures and tutorials in Weeks 1 to 4 such as:
• tables
• graphs, and
• summary measures.

The manager has asked for a numberof tasks to be done.The following guidelines for each question should be followed:

1. provide an overall summary of employees’ salaries.
You will need to use numerical analysis methods to summarise over all salaries. In the report, you will also need to write aboutaverages, variability etc. as presented in the examples in the relevant lecture and tutorial.
2. Provide an estimate of the proportion of employees’ job satisfaction(that is,who are“Moderately satisfied” or “Very satisfied “with their job)at Cleveland.
You will need to use categorical variable analysis methods to provide this estimate. In the report, you will also need testate the estimate and discuss the limitations of this type of estimate.
3. Senior management is also in retested in how salary is related to gender.Does there appear to be any relationship be twee Nan employee’s salary and their gender?
You will need to focus on whether there is a difference or not. If yes, make sure you describe them. If not, you should explain why you think there is no difference.
4. Can you please provide an overall summary of how Cleveland Corp employees rated their relations with the management?
You will need to use categorical analysis methods to summarise the variable. In the report you also need to briefly describe your findings. Examples can be found in the relevant lecture and tutorial.
5. Senior management is interested in which numerical variables seem to influence an employee’s salary. Please provide a summary of these relationships if any.
Once more you will need to use techniques for the analysis of two variables. You will need to analysesix (6) numerical relationships. In each case, the type of the relationship – if there is one – and its direction and strength are of interest. Make sure you comment on what you find in each case.

GENERAL GUIDELINES:
• Before doing any analysis ,classify all your variables as either numerical or categorical .The techniques you will use depend on the type of data you have.
• As mentioned above, than alysis section(Part 1) yousubmitshouldbenomorethan8pagesofcomputeroutput.Youmightproducemuchmorethanthisinitially,butyoushouldtrimit down to only show the most relevant results.
• When satisfied ,you should copy-and-paste your analysis output in to a Word document.
• Your analysis should be presented in the same sequenceas the manager’s questions, and everything should be clearly labelled and grouped around each question.
• Also, make sure, there are no table or graph “breaks” across two pages.
• Poorly presented or unorganised analysis or excessive output (more than 8 pages) will be penalised.
• Save your computer analysis frequently (every 10 to 15 minutes).

Part2: Report (that is, yourreply to the Manager’s letter)

Write a letter to the manager, explaining essentialin form ationand conclusions from your data analysis.Youareallowednomorethantwopagestoconveyyourwrittenfindings.

• Keep the Englishes impel and the explanation brief.
• Avoid the use of technical statistical terminology. Your reader will not necessarily understand even simples statistic cater ms, thus your task is to explain your analysis using plain, understandable language.

General instructions:

• There portistobe written as a standalone document(that is, assume for the scenario that the manager will only read your report, andnotlookatyouranalysisoutput).Thus,youshouldnothaveanydirectreferencesto your an alysisin your report.
• When writing your reply ,make sure that you actually provide the information the manager requested in her letter to you. That is, answer the manager’s questions.
• Do not include e computer out put table sand chartsin your response report.
• Numberyouranswersinyourreport1,2a, …etc., to match these quence of the manager’s requests.
• Include anintroduction at the start of the report and a summary/ conclusionatt heend.
• Markswillbedeductedfortheuseoftechnicalterms,poorgrammar,awkwardsentencestructure,poorspelling andpunctuation,irrelevantmaterial,poorpresentation/organisationandlettersthatareovertwo (2) pageslong.

Assignment ChecklistBEFORE YOU FINISH:

Part 1 (Data Analysis)

1. Have you applied the correct analysis techniques?
2. Is the analysis in the same order as the manager’s requests?
3. Is every table and every graph clearly labelled?
4. Are there tables or graphs that are broken across 2 pages?
(If yes, make sure you change that!)
5. Is the analysis output no more than 8 pages long?
Part 2 (Report)

1. Is your report written in simple English?
2. Is it free of grammar and spelling mistakes?
3. Is it free of technical language?
4. Is it free of Excel output (tables, graphs)?
5. Are the answers numbered and in the same order as the manager’s requests?
6. Is thereport no more than 2 pages long?

Assignment submission:
Your submission will comprise two (2) parts:
• Part 1:Data Analysis (max. 8 pages)
• Part 2 : Report (max. 2 pages).

Both parts may be contained in a single file, but their content must be separated. That is, you will submit one MS-Word document only. This MS-Word document will have two parts. Failure to separate Part 1 and Part 2 will be penalised. Furthermore, marks will be deducted for each part where the page limit is exceeded.
The Assignment must be submitted both,
i. electronically and
ii. in print format by the due date.
o 10% of available marks will be deducted if the assignment is only submitted in one format.
Electronic submission:
There will be a link in Moodle under Week 6 labelled “Assignment 1 Online Submission”.
Print copy submission:
1) Your print copy assignment submission must be identical to your electronic submission!
Differences will be penalised.
2) You must complete the Deakin College Assignment Cover Sheet (Deakin College Student Portal: Student -> Forms -> Forms).
3) The completed Assignment Cover Sheets must be stapled together with the print copy. Loose paper is not acceptable. Your lecturer assumes no responsibility for lost pages!
4) The print copy must be placed in your lecturer’s locker.
(For Burwood students: level 4, building LA. Assignments will not be accepted at Deakin College Reception).
Students must maintain backup copies of all their assignment work. Electronic loss of data is common but is NOT a satisfactory excuse for an extension of the submission date.

Checking before your SUBMISSION:

1. Are the files identical with the printed assignment?
2. Have you completed the Deakin College Assignment Cover Sheet (Deakin College Portal: Students -> Forms -> Forms -> Assignment Cover Sheet.pdf)?
3. Is the entire assignment, with the completed cover sheet(s) on top, stapled together?
4. Did you submit your files online in the Deakin College portal using the link “Assignment 1 Submission “under Week 6 and an identical print copy to your lecturer’s locker?

Late submission of assignments:

Extension for medical reasons
If your work is late due to medical reasons, you will not need to seek the prior approval from your lecturer. However, you must attach a Doctor’s certificate to your assignment with a note that the work is late for medical reasons.

Please note that, in these circumstances, the medical certificate must cover the entire period of the extension, from the time the assignment was due until the time it is submitted.

Penalties for late submissions
Assignments received late and without prior approval, will be penalised. A penalty of 5% of the available marks will be deducted for every day the assignment is late. Assignments received after one (1) calendar week and without an approved extension, will not be accepted, and therefore considered not submitted.

2:Revenue Management

Using Excel, begin by filling in the spreadsheet provided with all of the relevant information from the case study. Include the zeros where no value is applied.
Show all of the required formulas; fill in all the cells on the spreadsheet.

1) Which group, if any, should the Diamond Peak Hotel accept? Explain your answer based on: (6 Marks)
a. Total Revenue
b. RevPAR
c. Total Contribution

2) Based purely on information that is not a part of your financial analysis, which group do you feel the General Manager should choose? Indicate a least three reasons for your decision that are evidenced in the narrative. (10 Marks)

3) On an overall basis what would your decision be if you were the General Manager? Explain your thinking in detail. (10 Marks)

Total: /81
Case Study: “Comparing Apples and Oranges: Which Group Yields the Best Profit? Which Group Do We Sell To?”

The Diamond Peak Hotel, one of 45 hotels in the Host Marriott management company, was bustling with business this Thursday afternoon as the hour of the daily revenue meeting drew closer. For the second straight year November was proving to be a busy month with a large number of transient business guests filling up every room not already claimed by a group. Indeed, the staff was busy checking out a fraternity group today and preparing for the arrival of a gymnastics team this evening.
Several members of the sales staff were eagerly anticipating the day’s meeting, excited about the good news they were ready to share with the revenue management team of the 575 room convention hotel.
Both of the hotel’s group sales managers, Tyrell Glouchester and Demetrius Glava, scurried to pull together the data they needed to answer any questions that might come up concerning the group business they were on the verge of selling. Both knew that though the business was still twelve months away, the sale would help put them closer to their yearly sales goals-and the bonuses that came with the accomplishment of those goals.
At 4:30 p.m., the revenue management team-Bill McAddlepot, General Manager; Ellen Zurpava, Director of Sales; the two group Sales Managers; Paramendra Bhura, Sales Manager for transient sales; Robin Douglas, the Reservations Manager; and Grace Fritzman, the Food & Beverage Director gathered in the meeting room with friendly smiles and a few weary comments about the fraternity group that was currently checking out of the hotel.
Bill quickly called the meeting to order, reviewing the actual performance of the latest five-day forecast. After a brief discussion about the current budget, Bill asked what new business the team had generated today. Tyrell and Demetrius spoke up in unison, sparking a laugh among their co-workers as they then both offered to defer to the other.

Tyrell yielded the floor and Demetrius began, “I received a call this morning from a Rev. Michael Brown. He’s planning his denomination’s annual minister’s revival meeting and would like to book it at our hotel. They’re looking for 300 rooms for a four-day period from Monday through Thursday night in the first week of next October. They would also want. . . ”
“When did you say the group wanted the hotel?” Tyrell interrupted.
“The first week of next October, Monday through Thursday,” Demetrius said. “I checked with Robin this morning-so far the only group we have booked is the return of our fraternity buddies who are checking out today. They have 125 rooms booked at $130, higher than our budgeted group ADR of $120 for that month, which gives us a little bit of leeway.”

“The fraternity group is a pretty firm booking, too,” said Ellen. “I was surprised to see that they actually ended up reserving more rooms than they had contracted for. Fortunately, we had the extra rooms for them this year.”
Tyrell shot Robin a questioning look, but was unable to catch her eyes as she seemed suddenly engrossed in one of the reports that Bill had handed out at the beginning of the meeting. He wasn’t certain, but she seemed to be suppressing a smirk.
Demetrius continued, “Other than a big dinner on the final night that is budgeted at $10000.00, the ministers will mostly use the hotel dining rooms and area restaurants for their food and beverage needs, which historically added $8000 per day for the first three days, and another $4000 on the Thursday, in F&B outlet revenue. However, they’ll want to reserve all of our remaining available meeting rooms for the duration of their stay and will be purchasing refreshment breaks on each day. The room rentals is $1600 per day and the Catering would run $1200 per day. I pulled their group history from our sister property in Minneapolis where they held their revival meetings for the past five years. Rev. Brown said they were looking for somewhere warmer for next year’s meeting in the hopes of finding a permanent home for the annual meeting.

“One of the more interesting aspects of their history is that they tend to pick up a lot of shoulder dates in early arrivals and late departures. Last year they picked up 50 unrequested rooms on the previous Sunday night, a night that has typically been a slow one for us.”
“Sounds like a great piece of business,” Ellen said. “What rate did you quote them?”
“They are slightly price-sensitive,” Demetrius said. “We ended up discussing a rate of $109.
“Four hundred rooms, you said?” asked Bill. “With the other group of 125, that would leave us 50 rooms for the peak business days. Robin, what is our transient ADR budget for next October?”
“October is a pretty easy month to forecast,” she answered. “We can easily fill 150 rooms most of those nights at the $135 rate.”
“Wait a minute,” interrupted Grace who was looking slightly deflated. “Before we discuss this revival meeting any further, I think you need to hear what Tyrell is bringing to the table. Tyrell, didn’t you say your group was for the first of October?”
Tyrell took a deep breath and began the pitch that he had quickly been revising since Demetrius first revealed the dates for his group, “Funny, that, isn’t it? I also talked to Robin this morning about a group sale I’m getting ready to make. She didn’t mention anything about the revival.”
Robin shrugged, “You asked whether we had anything booked for that day, I told you what we had booked.”
“I also got a call this morning, but it was from Mr. Jonathan Snow, the meeting planner for Floopy.com. Their engineers work all around the country out of their homes. Twice a year, they are required to attend a strategy meeting for three days that is held every spring in Canada, and every fall in the South. He would like to hold their next fall meeting at our hotel. He’d book 300 rooms and would purchase lunch, dinner, and coffee breaks on each of the three days of the conference, for a total of $12000 per day. They really like to wine and dine their engineers. On the final night, they’d purchase a huge gourmet banquet that runs an additional $5000. They’d also host a hospitality suite every night with an open bar that historically sells $3500 per night. Many of the attendees also bring their families along, which adds an additional $3000 a day in F&B outlet revenues. They have extensive audiovisual needs and would be purchasing staff services as well as renting the majority of our meeting room space. As a rough estimate, Grace and I determined that we might be looking at $2400 per day in Room and Equipment rental. With 300 rooms being picked up and the current group of 125, that would leave us with 150 rooms for our transient guests, so we wouldn’t have to displace any of the higher rated business. And yes, Grace, they want the Tuesday through Thursday of the first week in October.”

“We obviously can’t have both groups,” Grace said. “It seems pretty obvious to me that the corporate group would be more profitable to us. I mean, the ministers probably won’t even drink anything, which would destroy my beverage budget for the month.”

Bill’s brow furrowed as he flipped to a new page on his notebook. “Let’s get some of these details straight before we make any hasty decisions. We currently have 125 rooms sold for that week. Robin, tell me again the details for our current reservations that week.”

Robin glanced down at her forecast, already flipped open to the week under consideration. “Both Tyrell and Demetrius came to me today, so I’ve had the afternoon to review some of our forecasts and to update them with this month’s historical data.”

“Must be nice,” Tyrell muttered. “You could have warned us.”
Robin ignored Tyrell and continued, “The Kappa Kappa Kappa fraternity has reserved 125 rooms at $130. Most of their activities take them out of the hotel, so they will be using two meeting rooms only on Wednesday afternoon. They arrive on Monday and check out on Friday.”

“Robin suggested I review my forecasts for that week,” Paramendra spoke up. “Now I see why. My projections say that we can count on 50 transient rooms on Sunday, 80 on Monday, 150 on Tuesday and Wednesday, and 90 on Thursday. There are a few factors that would affect Friday, but an intelligent estimate would place us at 100 expected. Most of those will pay our rack rate of $135. I would be uncomfortable with accepting either of these groups-we’re going to lower our ADR for those nights. Perhaps we should hold out for a smaller group that will pay a higher rate. ”

“Tyrell, what rate will the corporate group accept?” asked Bill. “We were discussing a rate of $120 a night,” Tyrell answered.
“Demetrius, did the group history give you any indication about how many rooms the religious group would actually pick up?” Bill asked.
“Yes,” Demetrius responded. “On the peak days, they picked up the 300 reserved rooms, with a lower pick-up rate on the shoulder dates. In addition to the 50 rooms that they picked up as a Sunday check-in, they also had about 20 rooms that stayed over on Friday night.”
“Has anyone checked the Convention Bureau calendar to find out what is happening in town that week?” Ellen asked. When no one responded, she turned to Bill. “I’ll check on that this afternoon, I need to talk to Mrs. Tanster about a convention we’re hosting here next month anyway. I can let you know by tomorrow morning what sort of demand we might be looking for in that week. As far as I know, there is nothing major happening that week that would affect our business.”
“Thank you, Ellen. We’ll need to know what else is going on in the city before we can make a decision. I agree with Grace, we can’t accept both groups for those dates. Tyrell, Demetrius, could you both determine whether either of these groups have any flexibility with the scheduling of their events? Perhaps we could get one of them to move their group a week forward or backward.”
“It would have to be backward,” said Ellen. “We have the Soccer Coaches Association coming in the next week and we’ve already signed a contract for 350 rooms at $125 each.”
“All right, see if either group can move to the end of September. It does sound as though both groups are looking for a place to do return business with on an annual basis. It would be nice to capture both.”
“Be sure to account for the fact that the religious group has 80% multiple occupancy, which will mean more meals in the hotel dining rooms,” Demetrius said. “Also, Rev. Brown mentioned that they were expanding their invitations to their European and Asian ministers this year. That might reduce the number of group shed and the international guests will probably come in earlier and stay later-helping us fill up on our shoulder dates.”
“I’ll also account for the fact that they’re going to take all of my meeting space and not buy as many banquet services,” said Grace.
“Once you’ve figured out an accurate projection, Grace, bring me the totals-I’d like to have them by 10 a.m. tomorrow,” Bill said. “I also need the two of you to determine what the total room revenue for each group will be. Can you please calculate that for me? Once we’ve gathered this data, we’ll take a look at it and we can decide at tomorrow’s meeting which group we should take, if either.”

American car parts manufacturer