Budget Application Guide
Purpose:
This guide is provided to document the
spreadsheet pages used for preparation of the annual Solano’s
budget. It does not address in detail how the monthly values
are determined but focuses on how the pages are used to
allocate and record data.
Overview:
The layout of a typical budget page is
shown below. The red and green letters/numbers relate to the
spreadsheet rows and columns.
To assist the user, cells are color coded
to indicate the type of data to be entered/displayed.
Green cells indicate that data for the
year being budgeted is to be entered in the cell.
Yellow cells indicate that data for the
year prior is to be entered in the cell.
Red cells indicate that the particular
item does not apply to this specific organization.
Blue cells display the difference in
value between the year being budgeted and the prior year actual
values.
Per spreadsheet custom, white cells with
a zero indicate that the cell content will be produced by a
calculation. Warning! Entering data by typing in a
white
cell will destroy the formula attached to the cell.
Overwriting the formula is allowed when the user has reason to
alter the calculation process. For example, changing the
monthly distribution from the pattern established in the prior year.
As the budget for the upcoming year is
normally prepared in December, prior year data is defined as data
from the current year for the period January through November plus
data from the previous December.
Preparation:
The first step in preparing a budget is to enter prior year historical data. As described in the Introduction to Financial Management flow chart, this information is to be entered in several locations within the spreadsheet page for an entity as each month of the prior year data becomes available in the General Ledger reports. As shown below sales and margin data is entered in the area bound by cells B8 to M22, in the yellow cells. The totals in column N are calculated by the application.
.
As shown below, additional history data is to be entered in Cells B101 to M107. The totals for these items are entered into cells P36 to P43 by the application.
The majority of prior year data is
entered for the expense accounts in the area bound by cells Z123 to
AK227 for the four “stores” and Z123 to AK397 for Corporate
Services. A portion of this area is illustrated below.
Upon completion of entering historical
data the next step is to address staffing information.
Current employee’s names, pay rate,
company contributions for IRA and health insurance and workers comp
rates are entered in the area bound by cells X7 to AO30. Pay
rate is per hour for non-exempt employees and per month for
managers. IRA and health insurance contributions are per
month. Workers comp rate is per payroll dollars.
If IRA, health insurance and WC data is
not available, only employee names and pay rates may be entered at
this time. With this data displayed, the budget preparer shall
meet with each store manager and have him/her indicate any
additions/deletions
and enter the planned scheduling of each employee, IE entering a 1
digit in each month for full time, a 1 plus tenths for planned
overtime, or a fraction of 1 for part time. Planned new hire
data (no name required) is to be entered in Rows 27 to 30.
Planned Personnel Preference seasonal employees are to be
shown/scheduled (no name required) in Rows 31 to 36. Their pay
rate is the total rate invoiced by Personnel Preference. The
referenced area of the spreadsheet page is shown below.
With the aforementioned data entered, the
application will calculate wages, IRA (employee benefits), health
insurance, workers comp, and payroll taxes expense. Payroll
taxes/ month is based on the prior year monthly distribution of the
annual amount.
It should be noted that at this point in the procedure a decision regarding employee COLA and manager bonuses may not been made by the CEO. When guidelines are received, the pay rates must be revised and manager bonus amounts added to manager wages in the proper months (usually October and December). The adjusted monthly wages for the managers is entered by scheduling additional hours in the applicable months. For example a manager might be scheduled for 1.6 instead of 1 for the months of October and December for a 5% annual bonus.
At this point, the budget preparer will
meet with the CEO to develop guidelines or specific values for
sales, margin percentages and expenses for each of the entities. The
following procedure is followed for entering data that was provided
by the CEO and developing the new budget.
1) Enter the annual
sales value in cell N7
2) Enter the monthly
distribution percentages in cells B11 to M11. The prior year
distribution is provided on row 10 for reference. If the prior
year had a severe weather month that caused significant lower than
normal sales volume, or some other factor distorted sales, this can
be corrected when selecting the new values. The total shown
in cell N11 must equal 100%
3) Enter the monthly
distribution of gross profit percentages in cells B15 to M15,
referring to the prior year values displayed in row 14.
Generally, the prior year distribution will be applicable unless
there is significant change in the product mix sold. The
total shown in cell N15 must match the value specified by the CEO.
4) Recording expense
projections follows the same general concept as sales and gross
profit IE the new year’s selected value is, by default, distributed
monthly based on prior year history. This distribution may be
altered by over typing. The new year’s annual amount is
entered in the respective green cell, starting with the Utilities
account, cell AL136.
5) Upon completion of
developing the monthly expenses, the totals for each expense account
should be checked to determine that the guidelines issued by the CEO
have been met. The totals shown in column N are to be reviewed
to assure the process is correct.
At this
point the budget is complete and subject for review by the CEO if
requested. If acceptable, the final step is to provide copies
of applicable reports to the store managers and others.
Distribution is as follows:
1)
CEO – A printed copy of each entities’ budget and the total
corporation report (Columns AT to BG). An email with attached
Financial Management Application
file.
2)
Accounting Manager – Same as the CEO
3)
Store Managers -- A printed copy of their entities’ budget
The
Accounting Manager shall input the monthly budget data into the
General Ledger System and provide monthly and year to date reports
comparing budget to actual performance for each entity and corporate
total.