Melton Manufacturing opened in January $2023.$ 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 sold

Sales revenue

Operating income

$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/2025.$

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 $2025$: Answer

b$.$ Expected sales revenue in June $2025$: $Answer

c$.$ Expected net profits in December $2025$: $Answer

$4.$ Use the Forecast sheets to determine:

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

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

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

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

Were the predictions higher or lower than the actual results? Answer