Suppose your manager asks you to create a spreadsheet to compute a production schedule.

Your schedule should stipulate a production quantity for seven products that is based on sales projections made by three regional managers at your company’s three sales regions.

a. Create a separate worksheet for each sales region. Use the data in the Word file Ex28, which you can download from the text’s Web site. This file contains each manager’s monthly sales projections for the past year, actual sales results for those same months, and projections for sales for each month in the coming quarter.

b. Create a separate worksheet for each manager’s data. Import the data from Word into Excel.

c. On each of the worksheets, use the data from the prior four quarters to compute the discrepancy between the actual sales and the sale projections.

This discrepancy can be computed in several ways: You could calculate an overall average, or you could calculate an average per quarter or per month. You could also weight recent discrepancies more heavily than earlier ones. Choose a method that you think is most appropriate. Explain why you chose the method you did.

d. Modify your worksheets to use the discrepancy factors to compute an adjusted forecast for the coming quarter. Thus, each of your spreadsheets will show the raw forecast and the adjusted forecast for each month in the coming quarter.

e. Create a fourth worksheet that totals sales projections for all of the regions. Show both the unadjusted forecast and the adjusted forecast for each region and for the company overall.

Show month and quarter totals.

f. Create a bar graph showing total monthly production.

Display the unadjusted and adjusted forecasts using different colored bars.