Report expression.

D

Dennis

I have a budget report that uses this expression:

=Sum(IIf([Annual Plan Actual]=0,0,[Year To Date
Actuals]))/Sum(IIf([Annual Plan Actual]=0,1,[Annual Plan
Actual]))

The problem I am having is not all of the calculations are
accurate as in the following:
Amount Budgeted for Facility & General Operating
$145,537.00 $829,020.92 ($683,483.92
20.8%

The percentage should reflect the over expenditure of the
budget by 569.63%. When the budget is not over expended
the percentage is correct. I have enclosed the report for
review. I hope it formats correctly in the browser. Thanks
Dennis.

RJD Budget Report

Annual Plan Year to Date Year to Date
Pct. Of
Budget Actuals Balance Budget Spent
2 Capital Expenditures
3000 Capitalized Expenses
2341704752 Sales Tax-Fixed Assets $0.00
$0.00 $0.00 0.00%
2341800861 Equipment, Office $0.00
$0.00 $0.00 0.00%
2341703745 Incoming Freight - Fixed Asset
$0.00 $0.00 $0.00 0.00%
Amount Budgeted for Capitalized Expenses
$0.00 $0.00 $0.00 0.0%
4 Pro Forma Expenses - 1
4100 Direct Expenses
9420720482 Direct Inmate Labor $0.00
$0.00 $0.00 0.00%
Amount Budgeted for Direct Expenses $0.00
$0.00 $0.00 0.0%
4300 Indirect Expenses
9410720481 Indirect Inmate Labor $139,765.00
$33,208.54 $106,556.46 23.76%
9320730492 Packing & Shipping Supplies
$0.00 $3,412.00 ($3,412.00) 0.00%
9310730491 Indirect Materials - Supply Items
$0.00 $301,287.47 ($301,287.47) 0.00%
Amount Budgeted for Indirect Expenses
$139,765.00 $337,908.01 ($198,143.01
23.8%
4310 Personal Services
9011103125 Workers Comp $58,815.00
$5,182.64 $53,632.36 8.81%
9011103135 Life Insurance $0.00 $58.32
($58.32) 0.00%
9011103133 Unemployment Insurance $0.00
$1,713.00 ($1,713.00) 0.00%
9011103113 Retirement - Industrial $0.00
$31,102.33 ($31,102.33) 0.00%
9011103106 Retirement(Summary) $0.00
($86.97) $86.97 0.00%
9011103104 Dental Insurance $0.00
$1,617.75 ($1,617.75) 0.00%
9011103103 OASDI $0.00 $9,368.22
($9,368.22) 0.00%
9011101003 Permanent Salaries & Wages
$809,170.00 $184,323.26 $624,846.74
22.78%
9011101083 Overtime Pay $11,222.00
$3,556.30 $7,665.70 31.69%
9011101033 Temporary Help $24,653.00
$13,830.88 $10,822.12 56.10%
9011103105 Health Insurance $0.00
$25,476.55 ($25,476.55) 0.00%
9011103134 Admin. Fee & Other Misc. Fee
$0.00 $120.93 ($120.93) 0.00%
Amount Budgeted for Personal Services
$903,860.00 $276,263.21 $627,596.79
22.9%
4330 Facility & General Operating
9012311238 Office Supplies $0.00 $11,719.91
($11,719.91) 0.00%

Annual Plan Year to Date Year to Date
Pct. Of
Budget Actuals Balance Budget Spent
9012317303 Overtime Meals $0.00 $0.00
$0.00 0.00%
9012317297 Car-Rental, Rail & Taxi $0.00
$0.00 $0.00 0.00%
9012317294 Commercial Air Transportation
$0.00 $0.00 $0.00 0.00%
9012314265 Courier Service & Other Delvry
$0.00 $0.00 $0.00 0.00%
9012314263 Postage $0.00 $24.82 ($24.82)
0.00%
9012313257 Telephone, Cell Phones, Pagers
$0.00 $259.81 ($259.81) 0.00%
9012311239 Not Otherwise Classified
$0.00 $0.00 $0.00 0.00%
9012311223 Library Purchases And Subscrip
$0.00 $0.00 $0.00 0.00%
9012311213 Purch Clerical&Nonprof Svc
$0.00 $35.00 ($35.00) 0.00%
9012321338 Not Otherwise Class. (Serv.)
$0.00 $0.00 $0.00 0.00%
9012326404 C&P-Admin./Technical-NonState
$0.00 $501,882.11 ($501,882.11) 0.00%
9012312245 Printed Forms & Stationary
$0.00 $70,640.40 ($70,640.40) 0.00%
9014701721 Factory/Facility Equipment
$0.00 $20,279.02 ($20,279.02) 0.00%
9017704756 Sales Tax - Accr. Other $0.00
$3,609.89 ($3,609.89) 0.00%
9017704753 Sales Tax Other $137,987.00
$27,606.36 $110,380.64 20.01%
9016703746 Incoming Freight-Not-Inventory
$7,550.00 $2,706.21 $4,843.79
35.84%
9016703744 Incoming Freight-Inventory
$0.00 $0.00 $0.00 0.00%
9012324377 CDC -UT- Not Otherwise Clssfd
$0.00 ($2,052.29) $2,052.29 0.00%
9014701722 Vehicles $0.00 $134.54
($134.54) 0.00%
9012323342 Rent - CDC $0.00 $503.84
($503.84) 0.00%
9013333564 Advertising $0.00 $685.00
($685.00) 0.00%
9013333525 Fuel & Oil $0.00 $0.00
$0.00 0.00%
9012326403 C&P-Acctng & Auditing-NonState
$0.00 $144,529.00 ($144,529.00) 0.00%
9012332466 Computers & Peripherals $0.00
$5,893.44 ($5,893.44) 0.00%
9012332457 Machinery, Implements & Tools
$0.00 $97.15 ($97.15) 0.00%
9012332453 Office Equipment/Furniture
$0.00 $48.69 ($48.69) 0.00%
9012330438 Pro Rata $0.00 $29,522.20
($29,522.20) 0.00%
9012326431 Data Processing $0.00 $7,750.00
($7,750.00) 0.00%
9012326419 Not Otherwise Classified
$0.00 $0.00 $0.00 0.00%
9012332468 Expendable Tools $0.00
$2,434.82 ($2,434.82) 0.00%
9014701723 Office Equipment $0.00
$711.00 ($711.00) 0.00%
Amount Budgeted for Facility & General
Operating $145,537.00 $829,020.92
($683,483.92 20.8%
4350 Depreciation
9035700704 Depr.-Equipment - Office
$0.00 $651.63 ($651.63) 0.00%
9035700703 Depr.-Mach.,Implmnts, Mjr Tool
$0.00 $29,247.84 ($29,247.84) 0.00%
Shoe Factory Budget Report, Fiscal 1997-98 Page 2 of
3

Annual Plan Year to Date Year to Date
Pct. Of
Budget Actuals Balance Budget Spent
9035700702 Depreciation - Improvements
$0.00 $1,323.15 ($1,323.15) 0.00%
Amount Budgeted for Depreciation $0.00
$31,222.62 ($31,222.62) 0.0%
4370 Discounts
9018707781 Discounts Lost $0.00 $14.70
($14.70) 0.00%
9018706771 Discounts Taken- Inventory
$0.00 ($9.55) $9.55 -955000.
9018706770 Discounts Taken- Other
($47,296.00) ($20,940.47) ($26,355.53)
44.28%
9018705760 Discounts Available - Other
$0.00 ($14.70) $14.70 0.00%
Amount Budgeted for Discounts
($47,296.00) ($20,950.02) ($26,345.98)
44.3%
6 Pro Forma -
8300 Other Expense
9740740546 Small Business Late Pmt Pnlty
$0.00 $222.44 ($222.44) 0.00%
Amount Budgeted for Other Expense $0.00
$222.44 ($222.44) 0.0%
$1,141,866.00 $1,453,687.18 ($311,821.18)
127.3%
End
 
M

[MVP] S.Clark

I can't really address your exact problem, because I wasn't all that great
in math. It's a wonder that I can balance my checkbook each month, but, I
do know that Sum(IIF()) is a tough way to get the right answer.

Anytime I make a report that requires a complex calculation, I will base the
report on a table, instead of just a query. I will populate the table is
steps(either query or VBA code), such that I can trace what values came from
where, contributed to the calculation, and of course, the final values.

As you can see, your current calculation is just about impossible to debug.
There is no real way to figure out where something has gone wrong. The only
thing you have to go on is,

"When the budget is not over expended, the percentage is correct."

So, as far as your exact problem, you need to figure out what is happening
when the budget IS "over expended", to find out why the percentage is not
correct. I don't know what "over expended" is, or why there is anything
with the value of 569.63%, so I can't help there.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Dennis said:
I have a budget report that uses this expression:

=Sum(IIf([Annual Plan Actual]=0,0,[Year To Date
Actuals]))/Sum(IIf([Annual Plan Actual]=0,1,[Annual Plan
Actual]))

The problem I am having is not all of the calculations are
accurate as in the following:
Amount Budgeted for Facility & General Operating
$145,537.00 $829,020.92 ($683,483.92
20.8%

The percentage should reflect the over expenditure of the
budget by 569.63%. When the budget is not over expended
the percentage is correct. I have enclosed the report for
review. I hope it formats correctly in the browser. Thanks
Dennis.

RJD Budget Report

Annual Plan Year to Date Year to Date
Pct. Of
Budget Actuals Balance Budget Spent
2 Capital Expenditures
3000 Capitalized Expenses
2341704752 Sales Tax-Fixed Assets $0.00
$0.00 $0.00 0.00%
2341800861 Equipment, Office $0.00
$0.00 $0.00 0.00%
2341703745 Incoming Freight - Fixed Asset
$0.00 $0.00 $0.00 0.00%
Amount Budgeted for Capitalized Expenses
$0.00 $0.00 $0.00 0.0%
4 Pro Forma Expenses - 1
4100 Direct Expenses
9420720482 Direct Inmate Labor $0.00
$0.00 $0.00 0.00%
Amount Budgeted for Direct Expenses $0.00
$0.00 $0.00 0.0%
4300 Indirect Expenses
9410720481 Indirect Inmate Labor $139,765.00
$33,208.54 $106,556.46 23.76%
9320730492 Packing & Shipping Supplies
$0.00 $3,412.00 ($3,412.00) 0.00%
9310730491 Indirect Materials - Supply Items
$0.00 $301,287.47 ($301,287.47) 0.00%
Amount Budgeted for Indirect Expenses
$139,765.00 $337,908.01 ($198,143.01
23.8%
4310 Personal Services
9011103125 Workers Comp $58,815.00
$5,182.64 $53,632.36 8.81%
9011103135 Life Insurance $0.00 $58.32
($58.32) 0.00%
9011103133 Unemployment Insurance $0.00
$1,713.00 ($1,713.00) 0.00%
9011103113 Retirement - Industrial $0.00
$31,102.33 ($31,102.33) 0.00%
9011103106 Retirement(Summary) $0.00
($86.97) $86.97 0.00%
9011103104 Dental Insurance $0.00
$1,617.75 ($1,617.75) 0.00%
9011103103 OASDI $0.00 $9,368.22
($9,368.22) 0.00%
9011101003 Permanent Salaries & Wages
$809,170.00 $184,323.26 $624,846.74
22.78%
9011101083 Overtime Pay $11,222.00
$3,556.30 $7,665.70 31.69%
9011101033 Temporary Help $24,653.00
$13,830.88 $10,822.12 56.10%
9011103105 Health Insurance $0.00
$25,476.55 ($25,476.55) 0.00%
9011103134 Admin. Fee & Other Misc. Fee
$0.00 $120.93 ($120.93) 0.00%
Amount Budgeted for Personal Services
$903,860.00 $276,263.21 $627,596.79
22.9%
4330 Facility & General Operating
9012311238 Office Supplies $0.00 $11,719.91
($11,719.91) 0.00%

Annual Plan Year to Date Year to Date
Pct. Of
Budget Actuals Balance Budget Spent
9012317303 Overtime Meals $0.00 $0.00
$0.00 0.00%
9012317297 Car-Rental, Rail & Taxi $0.00
$0.00 $0.00 0.00%
9012317294 Commercial Air Transportation
$0.00 $0.00 $0.00 0.00%
9012314265 Courier Service & Other Delvry
$0.00 $0.00 $0.00 0.00%
9012314263 Postage $0.00 $24.82 ($24.82)
0.00%
9012313257 Telephone, Cell Phones, Pagers
$0.00 $259.81 ($259.81) 0.00%
9012311239 Not Otherwise Classified
$0.00 $0.00 $0.00 0.00%
9012311223 Library Purchases And Subscrip
$0.00 $0.00 $0.00 0.00%
9012311213 Purch Clerical&Nonprof Svc
$0.00 $35.00 ($35.00) 0.00%
9012321338 Not Otherwise Class. (Serv.)
$0.00 $0.00 $0.00 0.00%
9012326404 C&P-Admin./Technical-NonState
$0.00 $501,882.11 ($501,882.11) 0.00%
9012312245 Printed Forms & Stationary
$0.00 $70,640.40 ($70,640.40) 0.00%
9014701721 Factory/Facility Equipment
$0.00 $20,279.02 ($20,279.02) 0.00%
9017704756 Sales Tax - Accr. Other $0.00
$3,609.89 ($3,609.89) 0.00%
9017704753 Sales Tax Other $137,987.00
$27,606.36 $110,380.64 20.01%
9016703746 Incoming Freight-Not-Inventory
$7,550.00 $2,706.21 $4,843.79
35.84%
9016703744 Incoming Freight-Inventory
$0.00 $0.00 $0.00 0.00%
9012324377 CDC -UT- Not Otherwise Clssfd
$0.00 ($2,052.29) $2,052.29 0.00%
9014701722 Vehicles $0.00 $134.54
($134.54) 0.00%
9012323342 Rent - CDC $0.00 $503.84
($503.84) 0.00%
9013333564 Advertising $0.00 $685.00
($685.00) 0.00%
9013333525 Fuel & Oil $0.00 $0.00
$0.00 0.00%
9012326403 C&P-Acctng & Auditing-NonState
$0.00 $144,529.00 ($144,529.00) 0.00%
9012332466 Computers & Peripherals $0.00
$5,893.44 ($5,893.44) 0.00%
9012332457 Machinery, Implements & Tools
$0.00 $97.15 ($97.15) 0.00%
9012332453 Office Equipment/Furniture
$0.00 $48.69 ($48.69) 0.00%
9012330438 Pro Rata $0.00 $29,522.20
($29,522.20) 0.00%
9012326431 Data Processing $0.00 $7,750.00
($7,750.00) 0.00%
9012326419 Not Otherwise Classified
$0.00 $0.00 $0.00 0.00%
9012332468 Expendable Tools $0.00
$2,434.82 ($2,434.82) 0.00%
9014701723 Office Equipment $0.00
$711.00 ($711.00) 0.00%
Amount Budgeted for Facility & General
Operating $145,537.00 $829,020.92
($683,483.92 20.8%
4350 Depreciation
9035700704 Depr.-Equipment - Office
$0.00 $651.63 ($651.63) 0.00%
9035700703 Depr.-Mach.,Implmnts, Mjr Tool
$0.00 $29,247.84 ($29,247.84) 0.00%
Shoe Factory Budget Report, Fiscal 1997-98 Page 2 of
3

Annual Plan Year to Date Year to Date
Pct. Of
Budget Actuals Balance Budget Spent
9035700702 Depreciation - Improvements
$0.00 $1,323.15 ($1,323.15) 0.00%
Amount Budgeted for Depreciation $0.00
$31,222.62 ($31,222.62) 0.0%
4370 Discounts
9018707781 Discounts Lost $0.00 $14.70
($14.70) 0.00%
9018706771 Discounts Taken- Inventory
$0.00 ($9.55) $9.55 -955000.
9018706770 Discounts Taken- Other
($47,296.00) ($20,940.47) ($26,355.53)
44.28%
9018705760 Discounts Available - Other
$0.00 ($14.70) $14.70 0.00%
Amount Budgeted for Discounts
($47,296.00) ($20,950.02) ($26,345.98)
44.3%
6 Pro Forma -
8300 Other Expense
9740740546 Small Business Late Pmt Pnlty
$0.00 $222.44 ($222.44) 0.00%
Amount Budgeted for Other Expense $0.00
$222.44 ($222.44) 0.0%
$1,141,866.00 $1,453,687.18 ($311,821.18)
127.3%
End
Click to expand...
 
Top