Forecasting Using Excel

Melton Manufacturing opened in January $2019.$ Sales have increased significantly in the first two years of operations, and management is now looking to expand production capacity. To finance the purchase of a new factory, they would need to either raise capital or borrow funds.$$They have asked you to make some projections for the next year of operations. They intend to share these with potential investors and lenders.$$Information about unit sales, sales revenues, and net profits for the past two years is included in this link. A video demonstrating Excel tools used to answer the questions in this problem is also available on the website.

$1.$ Create three line graphs in Excel $($one for units sold, one for sales revenue, and one for net operating income$).$Add trendlines to all graphs.

a$.$ Extend the trendline out for $12$ months.

b$.$ Use the Polynomial $($Order $2)$ trendline option for all charts$$

c$.$ To see how closely the trendline matches the data, check the Display R$-$squared value on chart box. The closer the R$-$squared value is to $1,$ the better the match.

Enter the R$-$squared values, rounded to four decimal places.

Units soldAnswer $1$

Sales revenueAnswer $2$

Operating incomeAnswer $3$

$2.$ Create the same three graphs using the Forecast Sheet tool $($line charts$)$ in Excel.$$Hint:The Forecast Sheet tool is found under the Data tab. Highlight data to analyze, click on Forecast sheet, and click on Options to make the following adjustments.

a$.$ Set the Forecast End to $12/1/2021.$

b$.$ Use an $85\%$ Confidence Interval.$$

c$.$ Check the Include forecast statistics box.

d$.$ Leave remaining defaults as is$.$

$3.$ Use the trendline graphs to determine: Hint: To help identify the answers, display gridlines. Consider changing vertical axis bounds.

Note: Round your answers to the nearest thousand dollar.

a$.$ Expected unit sales in October $2021$:$$ Answer $4$

b$.$ Expected sales revenue in June $2021$: $Answer $5$

$$

c$.$ Expected net profits in December $2021$: $Answer $6$

$$

$4.$ Use the Forecast sheets to determine:

a$.$ Range of expected unit sales in October $2021($Upper to lower Confidence bounds$).$ Answer $7$

to$$Answer $8$

$$

b$.$ Expected sales revenue in June $2021($Upper to lower Confidence bounds$)$ $Answer $9$

to $Answer $10$

$$

c$.$ Range of expected net profits in December $2021($Upper to lower Confidence bounds$)$$Answer $11$

to $Answer $12$

$$

$5.$ To evaluate the Forecast sheets, rerun the forecasts. This time change the Forecast Start date to $1/1/2020$ to see what the model would have predicted for $2020.($Leave the confidence level at $85\%.)$ Compare the forecasted results for $2020$ to the actual results for $2020.$

Were the predictions higher or lower than the actual results? Answer $13$Predictions were higher than actual resultsPredictions were lower than actual results