Overview For this assignment you are acting as the HR manager for Asha Co., a horse blanket manufacturing company which sells its products to various retailers throughout North America. Customer demand for horse blankets has been quite steady over the past several years and this is not expected to change. You have been provided with sales projections and have been tasked to forecast the labour demand and supply requirements of the organization for the next three years. Instructions PART A: FORECASTING DEMAND 1. Open the Excel file HRP - FORECASTING ASSIGNMENT (on DC Connect) and navigate to the first sheet TREND/RATIO ANALYSIS (See the explanation of trend/ratio analysis in the text: pp. 106-108.) Calculate the ERR (Employee Requirement Ratio) for the "known" years 2019- 2022. Calculate the forecasted HR DEMAND (# OF EMPLOYEES) for years 2023-2025 using the 2022 ERR and the forecasted SALES figures. Here is the data: TREND/RATIO ANALYSIS SALES YEAR ($ THOUSANDS) HR DEMAND (# OF EMPLOYEES) 2019 $2,800 155 2020 $3,050 171 2021 $3,195 166 2022 $3,300 177 FORECAST 2023 $3,500 2024 $3,600 2025 $3,850 ERR (SALES/EMPLOYEE) PART B: FORECASTING SUPPLY 2. Now that you have forecasted the demand for labour, you turn your attention to ascertaining HR supply. You have decided to use a MARKOV ANALYSIS. Markov analysis looks at historical patterns of employee movement between jobs to determine our internal supply for a particular job type, as well as seeing where there might be problems with too much or too little movement between jobs. 3. Navigate to the second sheet of the Excel Forecasting Assignment file named MARKOV ANALYSIS, you will be updating the data as per below (See Table 6.1 on page 147 for an example of a Markov analysis). Enter the initial numbers of employees per job family (Job A, B, C, D). Use these numbers and the movement percentages in the transition probability matrix to obtain forecasted numbers of employees who will be in job families A, B, C, and D at the end of this year. As with trend analysis, we can track percentages of employee movement based on last year or on an average of a few previous years. TRANSITION PROBABILITY MATRIX MARKOV ANALYSIS START OF YEAR END OF YEAR JOB A JOB B JOB C JOB D EXIT JOB A 80% 10% 5% 0% 5% JOB B 10% 70% 0% 10% 10% JOB C 0% 0% 90% 5% 5% JOB D 0% 0% 0% 90% 10% EXPECTED MOVEMENTS OF EMPLOYEES END OF YEAR INITIAL # OF EMPLOYEES JOB A JOB B JOB C JOB D EXIT JOB A 200 JOB B 70 JOB C 60 JOB D 100 PREDICTED # OF EMPLOYEES AT YEAR END Next, complete the second table (Markov Analysis #2) but for this forecast, the management team wants you to include numbers for an additional plant. Change the initial numbers of employees per job type to: Job A: Job B: Job C: Job D: 1,478 886 592 1,017 To receive full marks for this section your spreadsheet must include formulas - no static numbers - as modelled in-class during week 5. PART C: Analysis Human Resources Planning Forecasting Assignment 4. Asha Co. decided to use a quantitative method for forecasting demand. Do you agree with that decision? Why? Your answer should include a comparison between quantitative and qualitative methods. 5. What conclusions can you draw from the demand analysis? 6. What is the Markov Analysis telling you in terms of supply? Are there any areas of concern? What are the external hiring needs? NOTES: You will be submitting one excel file containing 2 sheets or tabs: The first sheet will contain your Trend/Ratio Analysis Data The second sheet will contain your Markov Analysis You will also be submitting one word file containing the answers to questions to Part C. To receive full marks, you must include formulas in your excel files. This is an individual assignment and accounts for 14% of your final course grade