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 below:

Month Units sold Sales Revenue Net operating income

Jan$-1978246,92020$

Feb$-1982549,5001,224$

Mar$-1984650,7601,812$

Apr$-1995057,0004,724$

May$-191,20072,00011,724$

Jun$-191,45087,00018,724$

Jul$-191,43586,10012,564$

Aug$-191,682100,92018,492$

Sep$-191,695101,70018,804$

Oct$-191,761105,66020,388$

Nov$-191,925115,50024,324$

Dec$-192,400144,00035,724$

Jan$-202,306166,03261,140$

Feb$-202,612188,06472,156$

Mar$-202,895208,44070,764$

Apr$-203,042219,02475,468$

May$-203,126225,07278,156$

Jun$-203,680264,96095,884$

Jul$-204,111295,992109,676$

Aug$-204,687337,464121,108$

Sep$-205,067364,824143,402$

Oct$-205,249377,928149,590$

Nov$-205,825419,400169,174$

Dec$-206,030434,160176,144$

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

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

Units sold Answer

Sales revenue Answer

Operating income Answer

$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 $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$).$

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

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

$5.$ Were the predictions higher or lower than the actual results? Answer

$(\text{'}$m not interested in how to get it done in Excel. I just want the correct solutions$)$