Case study:
The University Bookstore is owned and operated by State University through an independent corporation with its own board of directors. The bookstore has three locations on or near the State University campus. It stocks a range of items including textbooks, trade books, logo apparel, drawing and educational supplies, and computers and related products including printers, modems, and software. The bookstore has a program to sell personal computers to incoming freshmen and other students at a substantial educational discount partly passed on from computer manufacturers. This means that the bookstore just covers computer costs with a very small profit margin remaining.
Each summer all incoming freshmen and their parents come to the State campus for a three-day orientation program. The students come in groups of one hundred throughout the summer. During their visit, the students and their parents are given details about the bookstore's computer purchase program. Some students place their computer orders for the fall semester at this time, while others wait until later in the summer. The bookstore also receives orders from returning students throughout the summer. This program presents a challenging supply chain management problem for the bookstore.
Orders come in throughout the summer, many only a few weeks before school starts in the fall, and the computer suppliers require at least six weeks for delivery. Thus, the bookstore must forecast computer demand to build up inventory to meet student demand in the fall. The student computer program and the forecast of computer demand has repercussions all along the bookstore supply chain. The bookstore has a warehouse near campus where it must store all computers since it has no storage space at its retail locations. Ordering too many computers not only ties up the bookstore's cash reserves, but also takes up limited storage space and limits inventories for other bookstore products during the bookstore's busiest sales period. Since the bookstore has such a low profit margin on computers, its bottom line depends on these other products. As competition for good students has increased, the university has become very quality-conscious and insists that all university facilities provide exemplary student service, which for the bookstore means meeting all student demands for computers when the fall semester starts. The number of computers ordered also affects the number of temporary warehouse and bookstore workers that must be hired for handling and assisting with PC installations. The number of truck trips from the warehouse to the bookstore each day of fall registration is also affected by computer sales.
The bookstore student computer purchase program has been in place for fourteen years. Although the student population has remained stable during this period, computer sales have been somewhat volatile. Following is the historical sales data for computers during the first month of fall registration:
year computer sold year computer sold
1 518 8 792
2 651 9 877
3 708 10 693
4 921 11 841
5 775 12 1009
6 810 13 902
7 856 14 1103
QUESTIONS:
Problem 1: Develop an appropriate forecast model for the bookstore manager to use to forecast computer demand for the next fall semester. Show work for the following forecast techniques:
a. Moving average (n = 3)
b. Moving average (n = 5)
c. Linear trend line
d. Exponential smoothing (alpha = .3)
e. Exponential smoothing (alpha = .5)
f. Adjusted Exponential smoothing (alpha = .3, beta = .4)
g. Adjusted Exponential smoothing (alpha = .4, beta = .5)
Problem 2: Complete all above forecast techniques using MS Excel
a) Label everything appropriately. You do not need to make each forecast technique a separate tab on the sheet. However, please label columns correctly.
b) Place your name in the document
Problem 3: Use the forecast technique with the lowest MAD to determine the most appropriate model.
a) Solve the forecast model for the lowest MAD and indicate how accurate it appears to be.