30. Use queries to extract sales data by model type and month. Use a spreadsheet to forecast the sales of each quantity of model type by month for the next year. Hint: Use Format([OrderDate],"yyyy-mm") to get the month number.
Query (Race, overall):
SELECT Format([OrderDate],"yyyy-mm") AS YearMonth, Count(Bicycle.SerialNumber) AS CountOfSerialNumber
FROM Bicycle
WHERE (((Bicycle.ModelType)="Race"))
GROUP BY Format([OrderDate],"yyyy-mm");
Query (Race, monthly):
TRANSFORM Count(Bicycle.SerialNumber) AS CountOfSerialNumber
SELECT Year([OrderDate]) AS [Year]
FROM Bicycle
WHERE (((Bicycle.ModelType)="Race"))
GROUP BY Year([OrderDate])
PIVOT Month([OrderDate]);
File: C09Ex30.xlsx.
Probably better to look at the data plot and recognize that sales might flatten, so better to use cubic instead of linear. But linear with monthly percentages is a start.
File: C09Ex30.xlsx for simple linear.