Quantitative Methods (STAT-201)
Ministry of Education Saudi Electronic University College of Science & Theoretical Studies ___________________________________________________________________ Quantitative Methods (STAT-201) Assignment 3: Excel QM (Social-Responsible) 2nd Semester, 1444-1445 (2023-2024) Due date: 06 May 2024 (Time: 11:00 PM) Student’s Name Student’s ID Section/CRN Location Marking Scheme Question Score Q-1 2 Q-2 2 Q-3 2 Q-4 2 Obtained Score Note: You are required to fill your full name, ID, and CRN. Message: Social responsibility is a framework of ethics in which an individual collaborates and works with other organizations and individuals for the betterment of the community. There are numerous ways in which an organization can exhibit social responsibility; for instance, by promoting volunteerism, facilitating donations, employing ethical employment practices, and implementing environmental improvements. It introduces the subjective obligation that entails maintaining a delicate equilibrium between one’s ecosystem and the economy, as well as the potential compromises that may be made between economic progress and societal and environmental wellbeing. This transcends business organizations and affects all individuals whose actions influence the environment. Restricted – مقيد Ministry of Education Saudi Electronic University College of Science & Theoretical Studies Solve the following questions using Excel QM. (2×4 = 8 marks) 1. Helping others through blood donation is a form of social solidarity or social responsibility. The blood donors cite a sense of social responsibility as their reason for donating blood, and as a result, the communities value and reward this activity. The following table shows the number of blood donors in a university from 2011–2023. Develop a 3-year moving average forecast and obtain the related statistical measures using excel QM. Solution: Restricted – مقيد year Blood Donors 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 116 105 146 159 108 194 227 219 234 224 248 253 265 311 Ministry of Education Saudi Electronic University College of Science & Theoretical Studies 2. University X has a unique on-campus dispensary that provides medical care. On a typical working day, patients arrive at the dispensary at an average rate of 2 patients per hour. There is one single nurse at the dispensary, and the average time required to give a consultation is 12 minutes. It is assumed that the service times can be described by the exponential distribution, and the arrival process by a Poisson distribution. You are requested to find: a. The average time in line, b. The average number of patients in line, c. The average time in the system, d. The average number of patients in the system, e. The probability that the dispensary is empty, f. The utilization factor of the system. Solution: Restricted – مقيد Ministry of Education Saudi Electronic University College of Science & Theoretical Studies 3. Assume that you are a statistical consultant and want to provide a volunteer consultation to a start-up company as a social responsibility consultation. The company is planning to purchase a uniform outfit for their employees, and they obtained the following quantity discount table with three discount options: DISCOUNT NUMBER DISCOUNT QUANTITY DISCOUNT COST (SAR) 1 0 to 49 100 2 50 to 99 90 3 100 and over 85 Annual demand is 400 outfits, ordering cost is 50, and holding cost is 25% of the cost of the outfit. Use Excel QM to find the discount option with the minimum total cost. Solution: Restricted – مقيد Ministry of Education Saudi Electronic University College of Science & Theoretical Studies 4. To help needy people through charity is a form of social responsibility. The act of doing charity to help others is recognized and rewarded by all communities of the society. The following table shows the amount collected through charity and the amount spent on social development (in million dollars) in the last 8 years. Find the correlation and coefficient of determination for the given data and obtain the related statistical measures using excel QM. Year 1 2 3 4 5 6 7 8 Solution: Restricted – مقيد Amount collected(y) 18 14 12 15 10 8 9 6 Amount spent(x) 15 10 9 12 5 4 6 3 How to get started with Excel OM/ QM for PC Version 5.3 Type “Pearsonhighered.com/render”in the address bar and click on ‘Student Resources’ Click on “Excel OM/QM for PCs. Version 5.3” of Software Downloads Double Click on Installer saved on your PC , then click on ‘More info’ and click on ‘Run Anyway’ Excel QM v5.3 will be installed on the PC. Excel QM will appear in ribbon of Excel sheet Week-2(Chapter-3) Decision Theory Decision Theory: Example of Thompson Lumber Company With a favorable market, Thompson thinks a large plant would result in a net profit of $200,000 to his firm and with unfavorable market, the large plant would result in a net loss of $180,000. A small plant would result in a net profit of $100,000 in a favorable market but a net loss of $20,000 would occur if the market was unfavorable. Table 3.1 Decision Table with Conditional Values for Thompson Lumber ALTERNATIVE Construct a large plant Construct a small plant Do nothing STATE OF NATURE FAVORABLE UNFAVORABLE MARKET MARKET ($) ($) 200,000 −180,000 100,000 −20,000 0 0 Questions a) Which alternative would be chosen by Optimistic Criteria? b) Which alternative would be chosen by Pessimistic Criteria? c) Which alternative would be the best decision according to criteria of realism with coefficient of realism 0.8? d) Which alternative would be the best decision according to minimax regret criteria? e) What is the best alternative using the expected monetary value analysis? f) Compute the Expected Value of Perfect Information. g) What is the best alternative using the expected opportunity loss analysis? Decision Theory Decision Theory Decision Theory Decision Theory Decision Theory Decision Theory: Solutions using Excel QM Week-3 (Chapter-5) Forecasting Forecasting : Moving Average Method WALLACE GARDEN SUPPLY EXAMPLE: Storage shed sales at Wallace Garden Supply are shown in the middle column of Table. Find a forecast for next January using different Moving Average Methods. MONTH January February March April May June July August September October November December Actual Shed Sales 10 12 13 16 19 23 26 30 28 18 16 14 Forecasting : Moving Average Method Forecasting : Moving Average Method Forecasting: Moving Average Method Forecasting : 1-Month Moving Average (Naïve) Forecasting : 2-Month Moving Average Forecasting : 3-Month Moving Average Forecasting : Weighted Moving Average Method WALLACE GARDEN SUPPLY EXAMPLE: Storage shed sales at Wallace Garden Supply are shown in the middle column of Table. Wallace Garden Supply decides to use a 3-month weighted moving average forecast with weights of 3 for the most recent observation, 2 for the next observation and 1 for the most distant observation. Find a forecast for next January using different Weighted Moving Average Methods. MONTH Actual Shed Sales January February March April May June July August September October November December 10 12 13 16 19 23 26 30 28 18 16 14 Forecasting : Weighted Moving Average Method Forecasting : 3-Month Weighted Moving Average With Tracking Signal & Graph Forecasting : 3-Month Weighted Moving Average With Tracking Signal & Graph Forecasting : 3-Month Weighted Moving Average With Tracking Signal & Graph Forecasting : 4-Month Weighted Moving Average With Tracking Signal & Graph Forecasting : 4-Month Weighted Moving Average With Tracking Signal & Graph Forecasting: Exponential Smoothing PORT OF BALTIMORE EXAMPLE :The port of Baltimore has unloaded large quantities of grain from ships during the past eight quarters. The port’s operations manager wants to test the use of exponential smoothing to see how well the technique works in predicting tonnage unloaded. He assumes that the forecast of grain unloaded in the first quarter was 175 tons. Quarter 1 2 3 4 5 6 7 8 Actual Tonnage Loaded 180 168 159 175 190 205 180 182 Forecasting: Exponential Smoothing Forecasting: Exponential Smoothing Forecasting: Exponential Smoothing for 𝛼 = 0.1 Forecasting: Exponential Smoothing for 𝛼 = 0.5 Week-4(Chapter-4) Regression Analysis Regression Analysis: Example of Triple A construction Company Triple A Construction Company renovates old homes in Albany. Over time, the company has found that its dollar volume of renovation work is dependent on the Albany area payroll. The figures for Triple A’s revenues and the amount of money earned by wage earners in Albany for the past six years are presented in Table. Economists have predicted the local area payroll to be $600 million next year and Triple A wants to plan accordingly. Triple A’s Sales ($100,000) 6 8 9 5 4.5 9.5 Local Payroll($100,000,000) 3 4 6 4 2 5 Regression Analysis Regression Analysis Regression Analysis Week-5 (Chapter-13) Waiting Line Models M/M/1 Model (Example of Arnold’s Muffler Shop) Arnold’s mechanic, Reid Blank, is able to install new mufflers on cars at an average rate of 3 per hour, or about 1 every 20 minutes. Customers needing this service on their cars arrive at the shop on the average of 2 per hour. Larry Arnold, the shop owner, studied queuing models in an MBA program and feels that all seven of the conditions for a singlechannel model are met. Then find the following: a. What is the average time for cars waiting in the line? b. What is the average number of cars in the line? c. What is the average number of cars in the system? d. What is the average time for cars waiting in the system? e. What is the probability that the system is busy? f. What is the probability that there is no cars in the system? Solution Solution Using Excel QM Now plug in our numbers to Excel QM. Open Excel QM, then select the Excel QM tab By Chapter Waiting Lines Single Channel Model. A Spreadsheet Initialization window will appear. Be sure you tick the box for Probabilities and click OK. A spreadsheet table will appear. Now we simply enter our arrival rate (2) and service rate (3) and the program will make the calculations for us (note that we are not given any costs here so we can just leave those fields blank): M/M/m Model(Example of Arnold’s Muffler Shop) Arnold’s Muffler Shop has two mechanic who are able to install new mufflers on cars at an average rate of 3 per hour, or about 1 every 20 minutes. Customers needing this service on their cars arrive at the shop on the average of 2 per hour. Larry Arnold, the shop owner, studied queuing models in an MBA program and feels that all the conditions for a multi-channel model are met. Then find the following: a. What is the average time for cars waiting in the line? b. What is the average number of cars in the line? c. What is the average number of cars in the system? d. What is the average time for cars waiting in the system? e. What is the probability that the system is busy? f. What is the probability that there is no cars in the system? Solution Using Excel QM Now plug in our numbers to Excel QM. Open Excel QM, then select the Excel QM tab By Chapter Waiting Lines Multi Channel Model. A Spreadsheet Initialization window will appear. Be sure you tick the box for Probabilities and click OK. A spreadsheet table will appear as follows: Now simply enter our arrival rate (2) , service rate (3) and number of channels (2), program will make the calculations for us (note that we are not given any costs here so we can just leave those fields blank):
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.