Calculation model v2 (pro version) - Preparing calculation model for 2 period
In previous topic, we discussed how to prepare calculation model v2 (pro version) for 1 period. In this topic, we will look what to do with received management report and how to prepare decision for 2 period. To prepare calculation model, perform the following actions according to the check list.
- Open folder on the computer with the calculation model.
- Download management report for 1 period from the championship website (or receive by e-mail) and save it to the folder with the model, for example “W012173.xls”.
- Make a copy of the management report and rename it to the “6.xls” file. Model requires only “6.xls” file, “W012173.xls” file is not required, but it is more convenient to store and organize reports this way.
Received management report for period 2 will need to be renamed to “7.xls”, respectively, for period 3 - “8.xls”, etc.
- Open calculation model on sheet “1” and click on button “Insert decisions” (cell N90). VBA macro will consistently insert into the model all management reports found in the folder from files: “6.xls”, “7.xls”, “8.xls”, “9.xls”, “10.xls”. If some file is missing, macro will generate an error.
- After inserting reports, open model on sheet “1” and click on button “Copy fact” (cell D90). In dialog box that appears, select “1” and click “OK”. VBA macro will transfer actual data from management report for 1 period to the decision on sheet “1”.
To load data after 2 periods, in the dialog box that appears, specify “2” respectively, after 3 periods - “3”, etc.
Switch in cell D1 will be set to position “2” - using actual production plan from the management report. Switch in cell T46 will be set to “1” - using actual deviation of demand from the forecast.
- On sheet “1” you can make plan-fact analysis on basis of the decision made in 1 period.
- Open sheet “2”, after inserting data of management report for 1 period, model is ready to calculate decision for 2 period. For subsequent periods, follow same procedure.