Function Problems

How to Create a Year-End Forecast in WPS Office Excel Using Historical Data

Phi Hung VoPhi Hung Vo Nov 13, 2026 869 views

Question details

The user needs to forecast year-end results for an April-to-March reporting year using historical monthly data, current-year performance, and the current run rate. The method should also estimate the current month based on equivalent months from previous years.

How to Create a Year-End Forecast in WPS Office Excel Using Historical Data
Product
WPS Office Excel
Device & OS
not provided
Scenario
The user has historical monthly values, current-year actuals, and a current run rate, and needs to project the remaining months of the reporting year.
Observed behavior
The desired outcome is a complete year-end forecast and a current-month estimate based on equivalent prior-year months.
Before you start

Prepare one chronological table containing historical monthly values and current-year monthly values, with dates recognized by WPS Office Excel. Check that the timeline is sorted oldest to newest and that future forecast months are left blank as target cells.

Solution 1Recommended

Create a Year-End Forecast with FORECAST.ETS on a Unified Timeline

Use this method when you have historical monthly data and current-year monthly values and need a seasonally aware forecast for the remaining months of the reporting year.

FORECAST.ETS accounts for seasonality and trends, making it suitable for financial or operational year-end projections. The key is to arrange all known values in one chronological series rather than splitting years into separate columns.

1
Open the forecast workbook

Open WPS Office Excel and select the worksheet containing your historical and current-year monthly data.

2
Build one chronological timeline

Create two columns: one for dates, such as the first day of each month, and one for the corresponding values, sorted from earliest to latest.

3
Enter the FORECAST.ETS formula

Click the first empty value cell for a future month, then enter =FORECAST.ETS(target_date, values_range, timeline_range) and press Enter.

4
Fill the remaining months

Drag the fill handle down through the remaining months of the April-to-March reporting year to populate each forecast value.

Create a Year-End Forecast with FORECAST.ETS on a Unified Timeline
Seasonal forecast created: WPS Office Excel returns monthly forecast values that reflect both trend and seasonality from your historical data.
Year-end forecasting in WPS Office

Create Your Year-End Forecast in WPS Office Excel

You can create the year-end forecast directly in WPS Office Excel. Open your existing XLSX workbook, build a chronological data series, and use the same FORECAST.ETS function and run-rate formulas to populate the remaining months of your April-to-March reporting year.

  1. 1. Download WPS Office: Click Download WPS Office Free and install the suite on your Windows or macOS computer.
  2. 2. Open your Excel workbook: Launch WPS Office Excel and open the .xlsx file containing your historical monthly data and current-year values.
  3. 3. Apply the forecast function: Use the FORECAST.ETS formula or your preferred run-rate method to project the remaining April-to-March reporting months.
  4. 4. Review and save: Review the forecast series, then save the workbook in Excel .xlsx format to share with your team.
Direct use of Excel-compatible FORECAST.ETS and run-rate formulas for year-end forecasting.Full compatibility with .xlsx files, including formulas, charts, and formatting.Free, lightweight, and familiar spreadsheet interface with a low learning curve.Works across desktop and mobile devices so you can review or update forecasts anywhere.
microsoft office alternative - wps office

Frequently Asked Questions

Does WPS Office Excel support the FORECAST.ETS function?

Yes. Open your workbook in WPS Office Excel and enter =FORECAST.ETS(target_date, values_range, timeline_range), or find it in the Function Library on the Formulas tab.

How should I arrange data for a year-end forecast in Excel?

Place all known values in one chronological series with recognized dates in one column and values in the next column, sorted oldest to newest. Leave future months blank so the forecast formula can fill them.

Can I use this method for a fiscal year ending in March?

Yes. Keep the target dates within your April-to-March reporting year and use known values from the same months to preserve seasonality in the forecast.

What if my historical data has missing months?

Fill or interpolate missing months when possible. FORECAST.ETS can handle some gaps, but a complete and sorted timeline usually gives more stable results.

How do I estimate only the current month instead of the full year?

Calculate the current month's run rate, compare it with the equivalent prior-year months, and apply the resulting variance factor to the remaining reporting period.