Work Through The Problems In Excel And Enter Your Ans 907691

Work Through The Problems In Excel Enter Your Answers In The Followi

Work through the problems in Excel. Enter your answers in the following areas below. Attach your Word file and Excel spreadsheet where indicated. YOU MUST SUBMIT YOUR EXCEL SPREADSHEET OR OTHER WORK IN ORDER TO RECEIVE CREDIT FOR THE ASSIGNMENT. FAILURE TO SUBMIT THE EXCEL SPREADSHEET OR OTHER WORK WILL RESULT IN A GRADE OF 0. You will only have access to the problems once. Therefore, complete all of the problems in Excel, and a Word document, then enter your answers and upload your Excel file.

Paper For Above instruction

Problem 1: Plotting Monthly Data

National Scan, Inc., sells radio frequency inventory tags. The company recorded its monthly sales over a seven-month period with the following data:

  • February: 19,000
  • March: 18,000
  • April: 15,000
  • May: 20,000
  • June: 18,000
  • July: 22,000
  • August: 20,000

Using Excel, plot the monthly sales data to visualize the trend. Copy the generated graph and paste it into your Word document as part of your submission.

Problem 2: Forecast September Sales

Using the provided sales data, forecast the sales for September via various methods, rounding all results to two decimal places:

  1. The naive approach (using August sales as the forecast for September)
  2. A five-month moving average
  3. A weighted moving average with weights 0.60 for August, 0.30 for July, and 0.10 for June
  4. Exponential smoothing with a smoothing constant of 0.20, assuming a March forecast of 18,000
  5. A linear trend equation obtained via Excel's trendline function; then use this to forecast September sales.

Problem 3: Evaluation of Forecasting Methods

Identify which forecasting method appears least appropriate for this scenario and justify your choice. Additionally, discuss what the use of the term "sales" presumes about the nature of the data, especially in relation to demand.

  • Least appropriate method justification
  • Implications of using "sales" versus "demand"

Problem 4: Equipment Usage Forecasting with Exponential Smoothing

A dry cleaner forecasts equipment usage at its main plant using exponential smoothing with a smoothing constant of 0.1. August usage was forecasted at 88% capacity, while actual usage was 89.6%. Forecast for September:

  1. Calculate the forecast for September based on August's forecast and actual usage.
  2. If actual September usage is 92%, forecast October's usage.

Problem 5: Forecasting Job Requests

The electrical contractor recorded the number of job requests over the last five weeks:

  • Week 1: (data needed)
  • Week 2: (data needed)
  • Week 3: (data needed)
  • Week 4: (data needed)
  • Week 5: (data needed)

Forecast the number of requests for Week 6 using:

  1. The naive method
  2. The four-month moving average
  3. Exponential smoothing with alpha = 0.30; using 20 as Week 2 forecast

Problem 6: Air Travel on Mountain Airlines

Given data for the past 18 weeks of air travel, explain why an averaging technique would not be suitable for forecasting future passenger numbers.

Problem 7: Forecast for Mountain Airlines

Applying an appropriate forecasting method, predict the number of passengers for the next three weeks. Provide rationale for your choice of method and your forecasts.

Problem 8: Linear Regression for Customer Service Staffing

Download and analyze the provided dataset using Excel’s data analysis toolpak to find the trendline equation:

  1. Determine the linear equation of the trendline y = x + (rounded to three decimal places)
  2. Use the model to forecast customer service requirements for June of Year 2 (rounded to two decimal places)
  3. Estimate the number of customer service representatives needed for June of Year 2

Problem 9: Submission of Spreadsheet

Upload your completed Excel spreadsheet and supporting documents. Failure to do so will result in a score of zero.

Problem 10: Submission of Word Document

Upload your Word document with the linear regression analysis for the aforementioned dataset.

Problem 11: Upload Separate Files if Needed

If your linear regression work is on a different Excel file from other problems, upload that second Excel file here.

Related Assignments