Question

Chapter 15 The University Bookstore Student Computer Purchase Program Case page 762 The University Bookstore Student Computer Purchase Program: 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 3 day orientation program. The students come in groups of 100 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, whereas, others wait until later in the summer. The bookstore also receives orders from returning students throughout the summer. This program presents a challenging management problem for the bookstore. Orders come in throughout the summer, many old a few weeks before school starts in the fall, and the computer suppliers require at least 6 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 have repercussions all along the bookstore supply chain. The bookstore has a warehouse near campus where it must store all computers because it has no storage space at its retail locations. Ordering too many computers not only this up the bookstore’s cash reserves, it also takes up limited storage space and limits inventories for other bookstore products during the bookstore’s busiest sales period. Because the bookstore has such a low profit margin on computers, its bottom line depends on these other products. Because 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 fall semester starts. The number of computers ordered also affects the number of temporary warehouse and bookstore workers who 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 14 years. Although, the student population has remained stable during this period, computer sales have been somewhat volatile. Following are the historical sales data for computers during the first month of fall registration: 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. Weighted Moving average (50%, 30%, 20%, starting with most recent period) D. Linear trend line E. Exponential smoothing (alpha = .3 2. Adjusted exponential smoothing (alpha = .3, beta = .4) A. Complete all above forecast techniques using MS Excel, QM for Windows or Excel QM B. Label everything appropriately. You do not need to make each forecast technique a separate tab on the sheet. However, please label columns correctly. C. Place your name in the document 3. Identify the forecast technique with the lowest MAD. YEAR

Year- Computers sold

1 518

2 651

3 708

4 921

5 775

6 810

7 856

8 792

9 877

10 693

11 841

12 1009

13 902

14 1103

Answer 1

year	y	MA 3	MA 5	Weighted MA	linear trend	exponential
1	518				643.4	518
2	651				670.3055	518
3	708				697.211	557.9
4	921	625.6667		652.9	724.1165	602.93
5	775	760		803.1	751.022	698.351
6	810	801.3333	714.6	805.4	777.9275	721.3457
7	856	835.3333	773	821.7	804.833	747.942
8	792	813.6667	814	826	831.7385	780.3594
9	877	819.3333	830.8	814.8	858.644	783.8516
10	693	841.6667	822	847.3	885.5495	811.7961
11	841	787.3333	805.6	768	912.455	776.1573
12	1009	803.6667	811.8	803.8	939.3605	795.6101
13	902	847.6667	842.4	895.4	966.266	859.6271
14	1103	917.3333	864.4	921.9	993.1715	872.3389
		1004.667	909.6	1023.9	616.4945	941.5373

formula

year	y	MA 3	MA 5	Weighted MA	linear trend	exponential
1	518				=616.4945+26.9055*A2	518
=1+A2	651				=616.4945+26.9055*A3	=G2+0.3*(B2-G2)
=1+A3	708				=616.4945+26.9055*A4	=G3+0.3*(B3-G3)
=1+A4	921	=AVERAGE(B2:B4)		=0.5*B4+0.3*B3+0.2*B2	=616.4945+26.9055*A5	=G4+0.3*(B4-G4)
=1+A5	775	=AVERAGE(B3:B5)		=0.5*B5+0.3*B4+0.2*B3	=616.4945+26.9055*A6	=G5+0.3*(B5-G5)
=1+A6	810	=AVERAGE(B4:B6)	=AVERAGE(B2:B6)	=0.5*B6+0.3*B5+0.2*B4	=616.4945+26.9055*A7	=G6+0.3*(B6-G6)
=1+A7	856	=AVERAGE(B5:B7)	=AVERAGE(B3:B7)	=0.5*B7+0.3*B6+0.2*B5	=616.4945+26.9055*A8	=G7+0.3*(B7-G7)
=1+A8	792	=AVERAGE(B6:B8)	=AVERAGE(B4:B8)	=0.5*B8+0.3*B7+0.2*B6	=616.4945+26.9055*A9	=G8+0.3*(B8-G8)
=1+A9	877	=AVERAGE(B7:B9)	=AVERAGE(B5:B9)	=0.5*B9+0.3*B8+0.2*B7	=616.4945+26.9055*A10	=G9+0.3*(B9-G9)
=1+A10	693	=AVERAGE(B8:B10)	=AVERAGE(B6:B10)	=0.5*B10+0.3*B9+0.2*B8	=616.4945+26.9055*A11	=G10+0.3*(B10-G10)
=1+A11	841	=AVERAGE(B9:B11)	=AVERAGE(B7:B11)	=0.5*B11+0.3*B10+0.2*B9	=616.4945+26.9055*A12	=G11+0.3*(B11-G11)
=1+A12	1009	=AVERAGE(B10:B12)	=AVERAGE(B8:B12)	=0.5*B12+0.3*B11+0.2*B10	=616.4945+26.9055*A13	=G12+0.3*(B12-G12)
=1+A13	902	=AVERAGE(B11:B13)	=AVERAGE(B9:B13)	=0.5*B13+0.3*B12+0.2*B11	=616.4945+26.9055*A14	=G13+0.3*(B13-G13)
=1+A14	1103	=AVERAGE(B12:B14)	=AVERAGE(B10:B14)	=0.5*B14+0.3*B13+0.2*B12	=616.4945+26.9055*A15	=G14+0.3*(B14-G14)
		=AVERAGE(B13:B15)	=AVERAGE(B11:B15)	=0.5*B15+0.3*B14+0.2*B13	=616.4945+26.9055*A16	=G15+0.3*(B15-G15)

excel regression result

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.754853336
R Square	0.569803559
Adjusted R Square	0.533953856
Standard Error	101.7915866
Observations	14
ANOVA
	df	SS	MS	F	Significance F
Regression	1	164688.5319	164688.5319	15.89423356	0.001804196
Residual	12	124338.3253	10361.52711
Total	13	289026.8571
	Coefficients	Standard Error	t Stat	P-value	Lower 95%
Intercept	616.4945055	57.46325018	10.72850045	1.66672E-07	491.2928388
year	26.90549451	6.748716477	3.986757274	0.001804196	12.20130446