This template is to be used for importing budgets and forecasts to your ZoneReporting semantic model.
One goal we have is to configure the first upload so future uploads require little or no “touch” by the ZoneReporting team unless you modify your process and template. This means that, except for the period starting date values, all column headings and column order must be identical across all uploaded files now and in future periods.
This template can be used for all data to be included in the Budgets table. The budget amount uploaded will be the [Budget Amount] measure in the model. The amounts uploaded may be either currency amounts or quantities.
If your amounts are quantities, you may need to supplement the measures in the base ZoneReporting model by calculating the difference between Budget Amount and some measure of actual quantity coming from your transactions (which might include a transaction quantity or transaction quantity multiplied by unit rate or unit of measure, for example). You might also wish to supplement the measures in the base model with different formats (e.g., with/without currency symbols, rounded to thousands, digits after the decimal point).
Instructions by Element
Filename: The filename you use has no impact on the Power BI semantic model; you want to ensure that each upload intending to overwrite a previous upload uses the identical name and each upload intended to add additional data has a unique name. The file should be in comma-separated values format and should end with the extension CSV.
Column A: The name of the data being uploaded. Each upload file can contain as many different types of uploaded data as you wish. Typical values for this column include Annual Budget, Q1 2024 Forecast, Q2 2024 Forecast, Units Budget by Customer, and Units Forecast by Category. We recommend you aid your users by limiting the use of abbreviations and acronyms to those in common use across your organization.
Hint: This year’s financial budget and next year’s financial budget can both use the name “Annual Budget” as the dates in the column headers will identify the relevant periods. A financial budget and a units budget should have different names.
Columns B – Q: The dimensions by which your budgets/forecasts will be sliced. You may use as many or as few though at least one dimension must be provided. In a typical income statement budget, this will mean at least the Account Internal ID plus the date/period columns. You must include the internal IDs for all dimensions you wish to use. Including the description of the dimensions is for ease of use by the person submitting the file to the ZoneReporting team; the descriptions will not be uploaded to the semantic model because the dimension table(s) in the model contain that information.
For the initial upload, you may add, subtract, or rename dimension columns but future uploads must conform exactly to the standard set by the first upload as to column name and order. If you wish to change the dimensions in future uploads, you will need to decide whether to recast past budgets/forecasts, delete the prior period data, or if merely adding new dimensions, add blank columns with the same column headings to the past period files. Consult with the ZoneReporting Customizations & Optimizations team if you need to change the formats and/or content.
If you are only budgeting by GL account this year but anticipate budgeting by GL account plus customer segment or item category in future years, please include empty columns with column headings for those dimensions to minimize the need for modification in future years.
If your financial budget uses GL account and customer segment and your units budget includes item category, your template would include all three dimensions with blanks on the rows where not relevant.
Columns R – end: The remaining columns, beginning in the column immediately to the right of the last dimension column (columns R – AC in the original template), have as their headings the starting dates of your accounting periods in row 1. These dates are to be formatted MM/DD/YYYY or DD/MM/YYYY, consistent with your NetSuite configuration.
These dates may represent future years only if already configured in NetSuite.
The amounts appearing below the period dates should be numeric amounts rounded to the granularity appropriate to your data because the file uploaded in CSV format will only contain the displayed values. For example, if your total budgeted amount for the year is 325,000, the displayed value of 27,083 in each period’s column will result in a total of 324,996 which, though close, will likely cause issues with end users. In this case, you will want to either adjust the integer values for each period or format the upload file to display 27083.33333 to ensure the annual total is as expected. Generally, rounding to 5 digits after the decimal point should be sufficient.
Hint: If your budget amounts represent units, you will generally want to ensure your per-period budget amounts are integers and not fractions to avoid reporting issues resulting from variances in fractions of a unit.
Do not include a Totals column or row.
Formatting positive/negative values: When reporting on budget vs actual on your income statement, the ‘Actual’ and ‘Budget v Actual’ measures are based on the [Actual] measure in the ZoneReporting semantic model which displays values based on the “natural” balance of the account (positive values for balances that match the natural balance of the account and negative values for balances that don’t match the natural balance of the account). As a result, you should enter both budgeted income and budgeted expenses as positive amounts in your budget upload file. Contra account budgets will be entered as negative amounts. Quantity budgets may be entered as appropriate for the measures with which they are to be compared. Quantities received and quantities fulfilled are often both reported as a positive number with quantity on-hand being the difference (rather than the sum) so budget quantities received or fulfilled (shipped) will typically both be entered as positive amounts.
Uploading your budget/forecast data: Your data should be exported from Excel into one or more comma-separated values (CSV) files. For your initial upload, you can email the CSV file(s) to the ZoneReporting team member who provided the template to you.
In future periods, you can upload budget/forecast files without any assistance from ZoneReporting as long as your formatting remains identical to the original. You’ll probably find uploading easiest if you download and install Microsoft Azure Storage Explorer which operates like the Windows File Explorer. The download page is at https://aka.ms/storageexplorer. Your IT department will need to authorize you and provide instructions for connecting to the Azure Blob Storage container named specific for budgets like “zone-budgets”.
Once you’ve uploaded your file(s), the next regularly scheduled refresh of the data model will ingest the new data.
Modifying your budget/forecast upload: If you want to modify your budget/forecast data, for example by adding additional dimensions, please send an email to zrconsulting@zoneandco.com.
Special requests: If you have any questions or special requests, please send an email to zrconsulting@zoneandco.com.
Key points to check:
- File format and column headings (other than period start dates) are exactly as in prior periods and as you expect in future periods.
- Period start dates are formatted mm/dd/yyyy or dd/mm/yyyy (consistent with NetSuite).
- No total columns or rows.
- All necessary internal IDs are populated.
- Column A contains the Name of the budget/forecast in every row.
- All amounts/quantities are numeric and formatted with appropriate number of digits following the decimal point.
- Each file being uploaded is exported from Excel as a comma-separated values (CSV) file.