Changes

===== Security Issues =====

===== Security Issues =====

Unlike earlier iterations of the RCC, Version 4 of the Calculator does '''not''' use customized visual basic code and functions to complete calculations and automate features. This makes it easier to share the calculator regardless of the security settings of user departments or agencies. The calculator does make use of [https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a Power Query]  to reference a [[:en:RCC_CPI_Table|CGWiki page]] to check for the most recent version of the Calculator and for CPI updates. This will trigger a security warning the first time Calculator is opened and the data connections will be disabled (as shown below). Enabling content will permit the calculator to reference the GC Wiki when users refresh the version and CPI queries, otherwise the calculator will function as normal, even when the data connections are disabled.     

Unlike earlier iterations of the RCC, Version 4 of the Calculator does '''not''' use customized visual basic code and functions to complete calculations and automate features. This makes it easier to share the calculator regardless of the security settings of user departments or agencies. The calculator does make use of [https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a Power Query]  to reference a [[:en:RCC_CPI_Table|GCWiki page]] to check for the most recent version of the Calculator and for CPI updates. This will trigger a security warning the first time Calculator is opened and the data connections will be disabled (as shown below). Enabling content will permit the calculator to reference the GCWiki when users refresh the version and CPI queries, otherwise the calculator will function as normal, even when the data connections are disabled.     

[[File:Security Warning.jpg|center|Security Warning with Enable Content button|alt=|thumb|900x900px]]

[[File:Security Warning.jpg|center|Security Warning with Enable Content button|alt=|thumb|900x900px]]

===== Checking for New Versions =====

===== Checking for New Versions =====

The Centre of Regulatory Expertise continuously responds to feedback from users to improve the Calculator. So, when starting a new analysis, you should check if a new version is available. You can do this by right-clicking on the “''Right-click and Refresh this box to check for a new version''” button located toward the top of the “Read Me''”'' tab. Right-clicking this button open an options menu. Users should click Refresh to have the Calculator compare the upload date of the active RCC with the most recent version on the webpage. If a newer version is available, the text in cell D24 will read "A new version is available. Go get it now!". Users would then need to access the [[/wiki.gccollab.ca/Regulatory Costing Calculator - Calculateur des coûts de la réglementation|Regulatory Costing Calculator - Calculateur des coûts de la réglementation - wiki]] to download the new version.  If a new version is not available, the text in cell D24 will read "This is the current version, based on the last time the box was refreshed."  

The Centre of Regulatory Expertise continuously responds to feedback from users to improve the Calculator. So, when starting a new analysis, you should check if a new version is available. You can do this by right-clicking on the “''Right-click and Refresh this box to check for a new version''” button located toward the top of the “Read Me''”'' tab. Right-clicking this button open an options menu. Users should click Refresh to have the Calculator compare the upload date of the active RCC with the most recent version on the webpage. If a newer version is available, the text in cell D24 will read "A new version is available. Go get it now!". Users would then need to access the [[/wiki.gccollab.ca/Regulatory Costing Calculator - Calculateur des coûts de la réglementation|Regulatory Costing Calculator - Calculateur des coûts de la réglementation - wiki]] to download the new version.  If a new version is not available, the text in cell D24 will read "''This is the current version, based on the last time the box was refreshed.''"  

[[File:Check for New Version.jpg|border|490x490px|Right Click on the box labelled "Right-click and Refresh this box to check for a new version"|alt=|center]]

[[File:Check for New Version.jpg|border|490x490px|Right Click on the box labelled "Right-click and Refresh this box to check for a new version"|alt=|center]]

===== Adjusting the Calculated Columns Excel User Setting =====

===== Adjusting the Calculated Columns Excel User Setting =====

The new RCC uses tables (learn about them here). Sometimes it is helpful to enter an input value by using a formula directly in a cell in a table, but if a column is entirely blank, then by default Excel will apply a formula entered in row 1 to all other rows in that column. This can be annoying if you only want the formula to apply to a single row, since you will have to delete the formula in subsequent rows. This auto-filling behaviour is called "calculated columns" and the setting can be turned off in Options > Proofing > Autocorrect Options > AutoFormat as you type:

The new RCC uses tables (learn about them here). Sometimes it is helpful to enter an input value by using a formula directly in a cell in a table, but if a column is entirely blank, then Excel will apply a formula entered in row 1 to all other rows in that column by default. This can be annoying if you only want the formula to apply to a single row, since you will have to delete the formula in subsequent rows. This auto-filling behaviour is called "calculated columns" and the setting can be turned off in Options > Proofing > Autocorrect Options > AutoFormat as you type:

[[File:Excel User Setting.png|center|alt=|thumb|600x600px]]

[[File:Excel User Setting.png|center|alt=|thumb|600x600px]]

The last box should be unchecked ("Fill formulas in tables to create calculated columns).  

The last box should be unchecked ("Fill formulas in tables to create calculated columns).  

Alternatively, whenever a calculated column is created a little box will pop up in the table, and the setting can be changed there by clicking "Stop Automatically Creating Calculated Columns":

Alternatively, whenever a calculated column is created a little box will pop up in the table, and the setting can be changed there by clicking "Stop Automatically Creating Calculated Columns":

While the Calculator is an Excel application, a user can enter, edit, and delete almost all inputs.  

While the Calculator is an Excel application, a user can enter, edit, and delete almost all inputs.  

Users can enter inputs to the Calculator directly in the cells of the spreadsheet as one would do in a traditional Excel file. Cells for user input are formatted with a white fill (see below) on worksheet with blue tabs (Initiative, Stakeholders, Activities). For tables that automatically expand as data is entered a cell with "'''''Type Here ⮚'''''" on the left edge of the table indicates the row where values should be inputted. Cells that are shaded grey contain formulas or pre-set parameters. Although they are not protected and the user should not change them.

Users can enter inputs to the Calculator directly in the cells of the spreadsheet as one would do in a traditional Excel file. Cells for user input are formatted with a white, or blank, fill (see below) on worksheets with blue tabs (Initiative, Stakeholders, Activities). For tables that automatically expand as data is entered a cell with "'''''Type Here ⮚'''''" on the left edge of the table indicates the row where values should be inputted. Cells that are shaded grey contain formulas or pre-set parameters. Although they are not protected and the user should not change them.

===== Deleting Rows =====

===== Deleting Rows =====

Collections of inputs in the Calculator appear in rows with user input and pre-set functions. For example, the business counts and growth rates for a particular stakeholder group will be contained in a single row in the table on the Stakeholders sheet, and various details of a particular activity will be contained in a single row in a table on the Activities sheet. There are two ways delete rows.  

Collections of inputs in the Calculator appear in rows with user input and pre-set functions. For example, the business counts and growth rates for a particular stakeholder group will be contained in a single row in the table on the Stakeholders sheet, and various details of a particular activity will be contained in a single row in a table on the Activities sheet. There are two ways delete rows.  

The preferred way: users can right click on any cell in the row and select <u>D</u>elete -> Table <u>R</u>ow.

The preferred way: users can right click on any cell in the row and select <u>D</u>elete -> Table <u>R</u>ow.

[[File:Deletetablerow.png|alt=|center|frame]]

[[File:Deletetablerow.png|alt=|center|frame]]

The other way: right click on the sheet's row index on the left side and delete the entire row.  

The other way: right click on the sheet's row index on the left side and delete the entire row.  

This information is entered directly on the spreadsheet. If you wish to copy and paste from another document, it is best to click on the appropriate cell and press “F2”.  The F2 key is a short-cut in Excel to edit the selected cell’s contents. By doing this you will retain formatting such as carriage returns (i.e., new lines), which if pasted directly into a spreadsheet adds content over multiple rows. If you are typing the results directly into the cell you can enter a carriage return in a cell by pressing “alt-enter”.  

This information is entered directly on the spreadsheet. If you wish to copy and paste from another document, it is best to click on the appropriate cell and press “F2”.  The F2 key is a short-cut in Excel to edit the selected cell’s contents. By doing this you will retain formatting such as carriage returns (i.e., new lines), which if pasted directly into a spreadsheet adds content over multiple rows. If you are typing the results directly into the cell you can enter a carriage return in a cell by pressing “alt-enter”.  

[[File:Initiative Details Tab.png|alt=Initiative Details Tab|border|left|frameless|702x702px|Initiative Details Tab]]

[[File:Initiative Details Tab.png|alt=Initiative Details Tab|border|left|frameless|702x702px|Initiative Details Tab]]

'''Brief description of the regulatory change''': Put in a '''clear and concise description''' so TBS analysts and future analysts can look at this and understand. The RCC does not use this information. Because the Calculator is designed to measure incremental business impacts, this description should focus on how the regulations would cause businesses to change the way they operate. For context purposes, the user may also wish to briefly describe the objectives of the regulation.  Never assume that a second party, who may review the Calculator, has access to supplemental documents such as the Regulatory Impact Analysis Statement. To the extent possible, make the RCC a stand-alone product that is easy to follow. In summary, the regulatory context should briefly describe: 1.what the regulation does; 2. what is its intended outcome; and 3. how it will impact businesses.

'''Brief description of the regulatory change''': Put in a '''clear and concise description''' so TBS analysts and future analysts can look at this and understand. The RCC does not use this information. Because the Calculator is designed to measure incremental business impacts, this description should focus on how the regulations would cause businesses to change the way they operate. For context purposes, the user may also wish to briefly describe the objectives of the regulation.  Never assume that a second party, who may review the Calculator, has access to supplemental documents such as the Regulatory Impact Analysis Statement. To the extent possible, make the RCC a stand-alone product that is easy to follow. In summary, the regulatory context should briefly describe: 1.what the regulation does; 2. what is its intended outcome; and 3. how it will impact businesses.

•'''Number of regulations repealed.''' This is a drop-down list. It is only filled if the regulatory changes selected is Multiple Repeal and Replace.

•'''Number of regulations repealed.''' This is a drop-down list. It is only filled if the ''Type of Regulatory Change'' selected is ''Multiple Repeal and Replace''.

•'''Number of Regulations Introduced'''. The automatically when the regulatory change is selected. If the proposal is introducing more than one regulation, a separate calculator should be used for each regulation.

•'''Number of Regulations Introduced'''. This is updated automatically when the regulatory change is selected. If the proposal is introducing more than one regulation, a separate calculator should be used for each regulation.

•'''Price Year.''' The year of prices (cost of labour, cost of units) used in the analysis. Prices used to estimate impacts on the ''Activities'' are all converted to the same year on the reporting tabs. Prices for labour cost data in the Calculator are from 2021. If a different price year is selected the calculator will deflate or inflate these values using the Consumer Price Index to the selected price level. The price year for the one-for-one rule is determined by the [[/laws-lois.justice.gc.ca/eng/regulations/SOR-2015-202/index.html|Red Tape Reduction Regulations]] (currently 2012), it is not affected by this selection.

•'''Price Year'''. The year of prices that annual impacts are reported. Prices used to estimate impacts on the ''Activities'' are all converted to the same year on the Annual Impact tab. Prices for labour cost data in the Calculator are from 2021. If a different price year is selected the calculator will deflate or inflate these values using the Consumer Price Index to the selected price level. The price year for the one-for-one rule is determined by the [[/laws-lois.justice.gc.ca/eng/regulations/SOR-2015-202/index.html|Red Tape Reduction Regulations]] (currently 2012), it is not affected by this selection.

•'''Year of Registration'''. The year that your department anticipates the regulation will be registered. This is the ''Start Year of Analysis'' when entering periods in the the Activities Sheet.  

•'''Year of Registration'''. The year that your department anticipates the regulation will be registered. This is the ''Start Year of Analysis'' when entering periods in the the ''Activities Sheet''.  

•'''Present Value Base Year'''. The is the year that impacts are discounted back to. Typically, this is the same as the registration year, but there is an option to use something different.

•'''Present Value Base Year'''. The is the year that impacts are discounted back to. Typically, this is the same as the registration year, but there is an option to use something different.

•'''Length of Analytical Period.''' This is the number of years over which you will estimate impacts. TBS policy requires a minimum ten years for Cost Benefit Analysis (CBA). The Red Tape Reduction Regulations require a ten-year period for INs and OUTs under the one-for-one Rule and cost estimates.

•'''Length of Analytical Period.''' This is the number of years over which you will estimate impacts. TBS policy requires a minimum ten years for Cost Benefit Analysis (CBA). The Red Tape Reduction Regulations require a ten-year period for INs and OUTs under the One-for-One Rule.

•'''Discount Rate'''. The percent per year that the value of future impacts will be reduced. This value should normally be set to 7% (real discount rate), as required by TBS CBA policy and the one-for-one rule and small business lens analysis. However, other discount rates can be used. This does not affect the One-for-One Rule calculation. By default, that uses 7%.

•'''Discount Rate'''. The percent per year that the value of future impacts will be reduced. This value should normally be set to 7% (real discount rate), as required by TBS CBA policy and the One-for-One rule. However, other discount rates can be used. This does not affect the One-for-One Rule calculation. By default, that uses 7%.

==== Identify Regulated Community – Setting Stakeholder Groups ====

==== Step Two: Identify Regulated Community – Stakeholder Profile ====

The next step in the process is to identify, group and estimate the number of businesses that the regulatory proposal will affect.  

The next step in the process is to identify, group and estimate the number of businesses that the regulatory proposal will affect.  

====== Annual Breakdown ======

====== Annual Breakdown ======

You can view the '''Annual Stakeholder Count''' which is broken down further to the right on the Stakeholders sheet. This breakdown is an output only, meaning that if you delete it / override it with different information, that will not affect the cost estimates on subsequent sheets.

You can view the '''Annual Stakeholder Count''' which is broken down further to the right on the Stakeholders sheet, as shown in the image below. This breakdown is an output only, meaning that if you delete it / override it with different information, that will not affect the cost estimates on subsequent sheets.

It is recommended that users view the annual stakeholder count to validate that the growth rates were entered correctly. For example, if you try to enter 3% annual growth but accidentally entered 300% annual growth, the estimated stakeholder counts towards the end of the analytical period will be so high that you should realize something is wrong.  

It is recommended that users view the annual stakeholder count to validate that the growth rates were entered correctly. For example, if you try to enter 3% annual growth but accidentally entered 300% annual growth, the estimated stakeholder counts towards the end of the analytical period will be so high that you should realize something is wrong.  

Finally, there is the navigation arrow to move to the next step or click the '''Activities tab.'''

 

 

 

Finally, there is the navigation arrow to move to the next step or click the '''Activities tab.'''  

==== Entering Activities ====

==== Entering Activities ====

This section discusses Step Three of the Calculator implementation process. This information can be entered directly into the spreadsheet in the '''Activities''' tab.

This section discusses Step Three of the Calculator implementation process. This information can be entered directly into the spreadsheet in the '''Activities''' tab.

Note: feel free to adjust the zoom level, font size, and/or column widths to match your personal preferences. It is possible to adjust these things to fit all fields on your screen at once. If all of your activities will include labour, you can hide the columns related to capital inputs (M, N, O). Conversely, if you are only entering capital activities, you can hide the columns related to labour (R, S, T). The Error Check and Assumptions/Notes columns can also be hidden, but just be sure to scan for any error messages before submitting your work to TBS.  

Note: feel free to adjust the zoom level, font size, and/or column widths to match your personal preferences. It is possible to adjust these things to fit all fields on your screen at once. If all of your activities will include labour, you can hide the columns related to capital inputs (M, N, O). Conversely, if you are only entering capital activities, you can hide the columns related to labour (R, S, T). The Error Check and Assumptions/Notes columns can also be hidden, but just be sure to scan for any error messages before submitting your work to TBS.