DEPARTMENT OF EDUCATION



Forecasting Tool for Key Performance IndicatorPrepared by: Robert John P. Gonzales (SEPS: PPD-PS)User GuideI. The Need to ForecastForecast is an essential step in planning and organizational management. It aids in decision making, and serves as a basis for actions to be taken in any given situations. Forecasting can also develop systems and processes making it continuous, effective and efficient. Forecasting allows organizations to make detailed analysis of the future. Since planning requires the prediction of future events as accurately as possible, the forecasting method itself should be scientific and empirical.This tool was developed to provide a statistical analysis of the historical data, identifying its trend and relative changes to determine future values of the identified Key Performance Indicator set according to DepEd standards. II. The Forecast Tool The Forecast tool generates forecasted values for the next six years starting from the indicated base year. It is designed to provide a scientific basis for the forecasted values using the Linear Method. Below are the definitions and functions of respective cells that contains information about the user and the forecasted values.CellsContainsDefinitionRegionList of Regions in the Philippines.There are currently 18 Regions in the PhilippinesData Set to ForecastKey Performance Indicators.The KPIs that needs to be forecastedBase YearStarting year for forecast.Usually the current year, but any other year can be entered for research purposes (ie. Trend Analysis, Historical Data, etc.)School YearContains the list of inclusive school years 29 years before the base year. Inclusive school years that are indicative of pertinent data for forecasting. This list is auto-generated based on the base year entered.Input DataContains the raw data to be encoded.The raw data encoded are the determinants of the KPIs to be forecasted. Time Index/ IndexIndex for the observation.Indexes the figure listed in the cell as included in the time series. Forecast YearsYears to generate forecasted values on.Six years projection of figures. This list is also auto-generated.Forecast ValuesThe forecasted values as predicted by the Linear Method.Figures are in percentage because KPIs are Rates. Mean Squared ErrorAn accuracy indicator for the forecast.An indicator whether the forecasted value is far from the actual value.Standard ErrorAnother Accuracy indicator for the forecast.a measure of variation for the raw data, providing a measurement for the spread. The smaller the spread, the more accurate the dataset is said to be.R-SquaredAnother Accuracy indicator for the forecast.Indicates how well the regression model from the Linear Method can forecast the data.RemarksYour comments about the forecast (optional)You may enter any short comments in the cell, provided it is related to the forecasted results.This tool hosts a complex yet straightforward method in determining accuracy and reliability of forecasted figures. The summary table indicating the forecasted values together with statistical indicators and the graph must be copied to another excel file and sent to the DepEd Planning Division for data collection, monitoring and management. The procedure on how to generate forecast results will be shown on the next part of this guide.III. Using the Forecast ToolThe Forecast tool was designed to be user friendly, compact and compatible with most MS Excel versions. Forecast values generated can easily be collated on a separate spreadsheet and be used for reporting purposes. Below is a step-by-step guide in using the tool, not only for generating forecast but also to interpret results:Step 1: Select Region, KPI to Forecast and Base Year014478000At this current version of the forecast tool, the function of the region cell is to properly identify from which region the data to be forecasted is from. There will be more information and options to be embedded on this function on the upcoming versions of this tool. Select region from the drop down list.2455545635000This version can generate forecasts on the four KPIs –the Net Enrollment Rate (NER), Cohort Survival Rate (CSR), Completion Rate (CR) and the Transition Rate (TR). For more information regarding these KPIs and how they are computed see appendix A of this guide.0-184400Choose the base year. As defined in the previous part of this guide, it is the starting year of the forecast period. The years are automatically generated, 29 years prior and 6 years after. As a rule of thumb, data points for any time series analysis should be 30. Statistically, it is the value, not too low, not too high, wherein the correlation coefficient approaches the real value of the population comparatively rapidly. As suggested, forecast years is within 6 years. Step 2: Fill-in required input data14462911600The required data to generate the forecast value for the KPI selected is predetermined. The time period of the data to be encoded should match the school year specified. This tool will still generate forecast values even if not all of the necessary figures from each respective Input Data were filled-in, however, the forecast values may be less reliable or inaccurate. It is highly recommended that Input Data is complete. Step 3: Analyze Forecast Results1209040571500The second table generates forecast values of 6 years for the selected KPI which are indexed as the 31st to the 36th data points. These results are then validated by the indicators defined in the previous part of this guide. The MSE or the Mean Squared Error and the SE or the Standard Error both measures the spread and distance of the coefficients used for forecast (more of this on the next part). This tells us that the lower the value, the more reliable the coefficients used are. The R-square is another accuracy indicator that measures how reliable is the Linear Method in generating the forecasted values based on the input data. This indicator should be treated as percentages. The cells turn green if the figures exceed 0.70 (70%) validating the reliability of the forecast values. 0127000The graph below the forecast table shows the trend of the input data. Data points in Green and Blue are for each respective data sets while those in red and orange are the Actual Forecast values. Step 4: Report and Forward ResultsAs soon as forecast values are validated, you may now forward the results to your respective offices and to the DepEd Central Office Planning Service for data management and compilation. You may save separate files for each KPI Forecast or copy over the second table to another Excel file and attach it on a single email. File name should follow the naming convention: (KPIBaseyear).(Region).(Initials of Planning Officer).(MMDDYY).XLXSExample: NER2016.Region12.RJG.08172016.xlxsIt is recommended that a copy of the forecast values be saved on another file for record keeping and data management purposes. If the statistical indicators (ie. MSE, SE and R-square) shows that the forecast values are not reliable, you may still report the figures and send an email for further validation of the results. IV. The Linear MethodWhat is the purpose of the tool without understanding how it works? This part explains what is the Linear Method and how was it embedded on the Forecast tool. The Linear method or also known as the Linear Regression Method is derived from the slope and intercept formula where the coordinates forms the general direction of the line. It is widely used in forecasting non-seasonal and non-cyclical data. Since the KPIs are not seasonal and cyclical but has trend, or is indicative of growth patterns, using this method may generate reliable forecast values. This method simply fits a line to the data point’s historical values and extrapolates it into the future. If the Input Data used does not show continuous pattern of change then it is non-seasonal and non-cyclical. Below are sample graphs that depicts seasonal and non-seasonal types of data. 61205812237100The graph on Orange Soda Sales shows seasonality as sales increases during summer season.663083618700While data on actual enrolment in the Philippines shows no seasonality and is not cyclical. Therefore, this data is fit for using forecasting using the Linear Method.So as not to make the tool complex by using Macros and other add-ins, the tool was developed using the available functions in Excel that are considered component equations of the regression analysis. These are the Slope, Intercept, RSQ, STEYX and the Forecast functions. These were link together to complete the Linear method and generate the forecast values using conditional functions such the IF and LOOKUP functions. V: Final Thoughts This tool is still under development, and is planned to be a part of a broader system of benchmarking and database management. Further communication with regards to this tool’s development is expected. Nevertheless, this tool is fully functional and can generate forecast values that will aid our respective institutions in planning, monitoring and evaluation. Errors and loops may be encountered while using this tool. If you encounter one, kindly notify the developer at the email provided. Appendix A: EBEIS Defined Performance IndicatorsGross Enrolment Ratio in Early Childhood Development ProgramsThis indicator measures the general level of participation of young children in early childhood development programs. It indicates the capacity of the education system to prepare young children for elementary education. The system generates this indicator only up to the level of the legislative districts and above.Enrolment Pre-sch, SY N---------------------------- x 100Population Age 4-5, SY N Where:Enrolment Pre-sch, SY NTotal Pre-school Enrolment (Table A - GESP; Table A1 - PSP)Population Age 4-5, SY NProjected 2002 population from NSONet Intake RateThis indicator gives a more precise measurement of access to primary education of the eligible, primary school-entrance age population than the Apparent Intake Rate.Enrolment Gr 1, Age 6, SY N------------------------------- x 100 Population Age 6, SY N Where:Enrolment Gr 1, Age 6, SY NTotal Age 6 Grade 1 Enrolment (Table B - GESP; Table A2 - PSP)Population Age 6, SY NProjected 2002 population from NSOGross Enrolment Ratio The indicator is used to show the general level of participation in primary education. It is used in place of the Net Enrolment Ratio when data on enrolment by single years of age is not available. It can also be used together with the Net Enrolment Ratio to measure the extent of over-aged and under-aged enrolment. The system generates this indicator up to the level of the legislative districts and above.Total Enrolment All Ages, SY N----------------------------------- x 100 Population Age 6-11, SY N Where:Total Enrolment All Ages, SY NTotal Enrolment (Table B - GESP; Table A2 - PSP)Population Age 6, SY NProjected 2002 population from NSONet Enrolment Ratio The indicator provides a more precise measurement of the extent of participation in primary education of children belonging to the official primary school age.Total Enrolment Ages 6-11, SY N------------------------------------- x 100 Population Age 6-11, SY NWhere:Total Enrolment Ages 6-11, SY NTotal Enrolment (Table B - GESP; Table A2 - PSP)Population Age 6, SY NProjected 2002 population from NSOTransition RateThe indicator assesses the extent by which pupils are able to move to the next higher level of education (i.e. primary to intermediate and elementary to secondary). Care should be exercised in using this indicator at the level of the Division, Municipal and Legislative Districts where migration can increase or reduce the results of the indicator. It is not calculated at the school level for this reason.Primary to Intermediate:Elementary to Secondary: Enrolment Gr 5, SY N--------------------------- x 100Enrolment Gr 4, SY N-1 Enrolment Yr 1, SY N-------------------------- x 100Graduates Gr 6, SY N-1 Where:Enrolment Gr 5, SY NEnrolment Yr 1, SY NAug. 31 Enrolment (Table A - GESP & GSSP; Table A1 & B1 - PSP)Enrolment Gr 4, SY N-1Previous SY March 31 Enrolment + Dropouts (Table D - GESP & GSSP; Table A4 - PSP)Graduates Gr 6, SY N-1Previous SY Promotees/Graduates (Table D - GESP & GSSP; Table A4 - PSP)Completion Rate The Completion Rate measures the percentage of grade/year 1 entrants who graduate in elementary/secondary education. It is available only up to the division level and above. Data for grade/year 1 are based on the predecessor of BEIS, the Unified Data Gathering System (UDGS), which did not have any validation procedures and did not monitor the completeness of the data submitted. Elementary:Secondary: Graduates Gr 6, SY N--------------------------- x 100Enrolment Gr 1, SY N-5 Graduates Yr 4, SY N-------------------------- x 100Enrolment Yr 1, SY N-3 Where:Graduates Gr 6, SY NGraduates Yr 4, SY NPrevious SY promotees/graduates (Table D - GESP & GSSP; Table A4 - PSP)Enrolment Gr 1, SY N-5Enrolment Yr 1, SY N-3- based on UDGS division level data ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download