BA9 Module 22 Assignment Instructions

1. See Assignment 1 on page 230 of the text; additional guidance is provided below.

This is assignment 1 on page 230.

develop an allocation and allotment plan. to complete this exercise, use the file provided by your instructor entitled budget tools 2e module 22 operating plans. on the tab labeled exercise in spreadsheet is the proposed budget for a health care finance program. this budget spends a lot of money on health care claims in object code 2241 and pays a lot of money for claims processing in object code 2501. The legislature has provided $30 million for new health care claims benefits but no money for estimated $25000 in processing costs for new claims; these costs are to be met through administrative savings. The legislature has also demanded other administrative efficiencies, but little guidance has been given for achieving them. Based on past payroll patterns, you may want to anticipate that 48% of expenses will be incurred in the first half of the year and 25% will be incurred in the second half. Benefit ratios are provided in the spreadsheet. There are no special purchases. The building lease for the department office is $125,000 per month. Net legislative adjustments are $28.75 million. As analyst, you are not sure what some of the expenditures are for, so your initial proposal will likely be revised, but you have been tasked with making an initial proposal. Complete this assignment using the provided spreadsheet.

2. Develop an Allocation plan onlyassociated with the budget as enacted by the Legislature.

3. Use the worksheet excel file provided:

• Module 22 Allocation Exercise (blue columns ONLY)

4. Allocation Exercise(blue) - Legislative actions in response to the proposed budget request:

• First, all numbers on the spreadsheet are expressed in $Thousands, which means that $30,000,000 appears as $30,000 and the last three zeros are implied.

• Proposal column - do notmake any changes to this column of the worksheet. This is your original proposed budget request and it does not change.

• Legislature column the onlyitem that should be in this column is the net change to the proposed budget request made by the Legislature, in this case $28,750,000 should be reflected in line 9999 Legislative Adjustment (Net).

• Adjust column this column is where all of the changes to the original proposed budget occur and the total of these changes should equal the net change to the proposed budget request made by the Legislature, or $28,750,000.

• Operating Plan column this column will automatically calculate the final budget operating plan, Proposal column + Adjust column = Operating Plan column

$434,820,000 (Proposal) + $28,750,000 (Adjust) = $463,570,000 (Operating Plan)

Directions for changes in Adjust column

• The Legislature granted the $30 million request to cover the cost of increased health care claims, this should be reflected in line 2241.

• The Legislature did NOTgrant the $25,000 request for claims processing costs associated with increased claims, code 2501; instead, the Legislature mandated the increased cost must be achieved through administrative savings. Therefore, you will reflect the cost in line 2501, but you must addthe $25,000 to the other administrative savings the Legislature directed be achieved (below).

• The Legislature mandated other administrative savings in the amount of $1,250,000.

Note: Management has provided the following guidance to allocate the mandated administrative savings of $1.25 million and the additional savings of $25 thousand needed to cover the increased cost for claims processing:

• $450,000 in savings is to come from Salaries, code 2102, and associated reductions in Payroll Taxes & Benefits totaling $126,900, code 2140 (show calculations for each code 2141 through 2145).

• Remaining savings of $698,100 will be allocated as follows:

• $50,600 travel, code 2151
• $50,000 memberships, code 2164
• $285,000 other furniture & equipment, code 2202
• $25,000 supplies, code 2203
• $50,000 duplicating & printing
• $50,000 consulting fees, code 2306
• $187,500 software, code 2502

• Net legislative adjustments to the budget request total an increase of $28,750,000.

Let me know how I can send you the spreadsheet please.

Excel File Edit View Insert Format Tools Data Window Help * 75% C) Q Sun 5:19 PM Q Search Sheet 9 Module 22 Operation Share A Insert Page Layout Formulas Data Review View Cut copy AutoSum A Fill 18 AA Wrap Text B 1 u, _._, ___ | .. Merge & Center, $, % .:+o Conditional Format Cell Insert Delete Format Sort & Filter Formatting as Table Styles A1 X jr Allocation Allocation Column Column4Column5Column6Column7 Fixed Ratrio Proposal 2 Column1 | Column8 Column2 Numbers in $Thousands 2100 Salaries & W 2101 Officers & Directors 2102 Salaries 2103 Total Salaries& W 90.00 10,000.00 328.00 10.418.00 90.00 10,000.00 328.00 12 2140 Payroll Taxes & Benefits 2141 FICA 2142 H 2143 Unemployment &Worker's Comp 2144 Health 2145 Other Benefits Total Payroll Taxes & Benefits 645.92 151.06 57.30 1,562.70 520.90 $ 2,937.88 13,355.88 151.06 57.30 1,562.70 520.90 2,937.88 13,355.88 19 2161 Trave 2163 Training & Conferences 2164 Membersh Total Travel & Traini 725.00 238.00 145.00 725.00 238.00 145.00 1.108.00 $ 1,108.00$ Exercise+ 125% 1055 4 23456789 *75%C4) Sun 5:20 PM Q Search Sheet Excel File Edit View Insert Format Tools Data Window Help 9 Module 22 Operation Share A Insert Page Layout Formulas Data Review View Table Cut copy Eorma, B 1 u, AutoSum A Wrap Text Custom Merge & Center. $ , % , .04% conditional Format Cel Insert Delete Format Sort & Filter Clear Formatting as Table Styles Allocation Column Column4Column5Column6Column7 Column8 2 Column1 | 25 Total Travel & Training Column2 1,108.00I$ 1,108.00 2200 2202 Other Furniture & Equipment 2203 Supplies 2204 Telephone 2205 Duplicating & Printing 2206 Postage&Shipping 2207 Equip. Rental & Maintenance 2208 Subscriptions & Fees ce Expenses 500.00 400.00 455.00 333.00 345.00 169.00 500.00 400.00 455.00 333.00 345.00 169.00 987.00 3,189.00 2220 Oc 2221 Rent 2223 Utilities 2225 Janitorial & Similar Total Occ $ 1,500.00 475.00 123.00 2,098.00 S 1,500.00 475.00 123.00 2,098.00 $ 400,000.00 $ 400,000.00 $ 400,000.00 $400,000.00 2241 Health Care Claims 2300 Professional Services & Related Exercise + 125% 4 *76%G) Sun 5:20 PM Q Search Sheet Excel File Edit View Insert Format Tools Data Window Help BA9 Module 22 Operation Share A Insert Page Layout Formulas Data Review View Table Cut copy AutoSum A Fill Clear 10 AA Wrap Text Custom Format B I U 1 u, i-, _ , Merge & Center. $ , % , ..o Conditional Format Cel Insert Delete Format Sort & Filter Formatting as Table Styles H47 Allocation Column Column4Column5Column6Column7 Column8 2 Column1 47 48 Column2 2300 Professional Services & Related 2301 Financial Services 2305 Legal Fees 2306 Consultant Fees Total Professional Services & Related 961.00 333.00 499.00 1,793.00 S 961.00 333.00 449.00 50 51 52 2500 Information Management 2501 Computer Claims Processing $10,000.00 $ 2,575.00 2502 Software 2503 Maintenance, Computer Related 2504 Misc.&Other Total Information M Total Non-Personnel 5.0010,025.00 187.50 $ 2,387.50 158.00 543.00 13,113.50 212.50) 421,251.50 158.00 $ 543.00 $ 13,276.00 $ $ 421 464.00 58 59 60 61 212.50) 434.607.38 Legislative Adjustments (Net) MUST BE Zero 64 65 $ 10,418 $ 13,356 $ 28,750.00 S 28,962.50 2100 Salaries&Wages 2140 Payroll Taxes& Benefits Total Personnel 10,418 2,938 13,356 Exercise+ 125% Excel File Edit View Insert Format Tools Data Window Help 76% CO Sun 5:21 PM Q Search Sheet aE BA9 Module 22 Operation Home Insert Page Layout Formulas Data Review View Share A AutoSum A Wrap Text Copy B 1 u, -, d.,_, _ $, % .:+.o Conditional Format . Cel Formatting as Table Styles Merge & Center, Insert Delete Format Sort & Filter Eorma, Clear Allocation Columnd Colum n4d Column5d Colum n6 2 Column1 Column2 Column7 Column8 Total Personnel 13,356 $ 13,356 2160 Travel&Training 2200 Office Expenses 2200 Occupancy 2240 Insurance 2300 Professional Services & Related 2500 Information Ma Total Non-Personnel Tota Total Grand Total 1,108 3,189 2,098 400,000 $ 3,189 2,098 $400,000 $ (163) $ 28,96 28,750 434,607 13,114 421,252 421.464 28,750 28,750 434,820 $ 90 l Exercise+ 125% 4 Excel File Edit View Insert Format Tools Data Window Help * 75% C) Q Sun 5:19 PM Q Search Sheet 9 Module 22 Operation Share A Insert Page Layout Formulas Data Review View Cut copy AutoSum A Fill 18 AA Wrap Text B 1 u, _._, ___ | .. Merge & Center, $, % .:+o Conditional Format Cell Insert Delete Format Sort & Filter Formatting as Table Styles A1 X jr Allocation Allocation Column Column4Column5Column6Column7 Fixed Ratrio Proposal 2 Column1 | Column8 Column2 Numbers in $Thousands 2100 Salaries & W 2101 Officers & Directors 2102 Salaries 2103 Total Salaries& W 90.00 10,000.00 328.00 10.418.00 90.00 10,000.00 328.00 12 2140 Payroll Taxes & Benefits 2141 FICA 2142 H 2143 Unemployment &Worker's Comp 2144 Health 2145 Other Benefits Total Payroll Taxes & Benefits 645.92 151.06 57.30 1,562.70 520.90 $ 2,937.88 13,355.88 151.06 57.30 1,562.70 520.90 2,937.88 13,355.88 19 2161 Trave 2163 Training & Conferences 2164 Membersh Total Travel & Traini 725.00 238.00 145.00 725.00 238.00 145.00 1.108.00 $ 1,108.00$ Exercise+ 125% 1055 4 23456789 *75%C4) Sun 5:20 PM Q Search Sheet Excel File Edit View Insert Format Tools Data Window Help 9 Module 22 Operation Share A Insert Page Layout Formulas Data Review View Table Cut copy Eorma, B 1 u, AutoSum A Wrap Text Custom Merge & Center. $ , % , .04% conditional Format Cel Insert Delete Format Sort & Filter Clear Formatting as Table Styles Allocation Column Column4Column5Column6Column7 Column8 2 Column1 | 25 Total Travel & Training Column2 1,108.00I$ 1,108.00 2200 2202 Other Furniture & Equipment 2203 Supplies 2204 Telephone 2205 Duplicating & Printing 2206 Postage&Shipping 2207 Equip. Rental & Maintenance 2208 Subscriptions & Fees ce Expenses 500.00 400.00 455.00 333.00 345.00 169.00 500.00 400.00 455.00 333.00 345.00 169.00 987.00 3,189.00 2220 Oc 2221 Rent 2223 Utilities 2225 Janitorial & Similar Total Occ $ 1,500.00 475.00 123.00 2,098.00 S 1,500.00 475.00 123.00 2,098.00 $ 400,000.00 $ 400,000.00 $ 400,000.00 $400,000.00 2241 Health Care Claims 2300 Professional Services & Related Exercise + 125% 4 *76%G) Sun 5:20 PM Q Search Sheet Excel File Edit View Insert Format Tools Data Window Help BA9 Module 22 Operation Share A Insert Page Layout Formulas Data Review View Table Cut copy AutoSum A Fill Clear 10 AA Wrap Text Custom Format B I U 1 u, i-, _ , Merge & Center. $ , % , ..o Conditional Format Cel Insert Delete Format Sort & Filter Formatting as Table Styles H47 Allocation Column Column4Column5Column6Column7 Column8 2 Column1 47 48 Column2 2300 Professional Services & Related 2301 Financial Services 2305 Legal Fees 2306 Consultant Fees Total Professional Services & Related 961.00 333.00 499.00 1,793.00 S 961.00 333.00 449.00 50 51 52 2500 Information Management 2501 Computer Claims Processing $10,000.00 $ 2,575.00 2502 Software 2503 Maintenance, Computer Related 2504 Misc.&Other Total Information M Total Non-Personnel 5.0010,025.00 187.50 $ 2,387.50 158.00 543.00 13,113.50 212.50) 421,251.50 158.00 $ 543.00 $ 13,276.00 $ $ 421 464.00 58 59 60 61 212.50) 434.607.38 Legislative Adjustments (Net) MUST BE Zero 64 65 $ 10,418 $ 13,356 $ 28,750.00 S 28,962.50 2100 Salaries&Wages 2140 Payroll Taxes& Benefits Total Personnel 10,418 2,938 13,356 Exercise+ 125% Excel File Edit View Insert Format Tools Data Window Help 76% CO Sun 5:21 PM Q Search Sheet aE BA9 Module 22 Operation Home Insert Page Layout Formulas Data Review View Share A AutoSum A Wrap Text Copy B 1 u, -, d.,_, _ $, % .:+.o Conditional Format . Cel Formatting as Table Styles Merge & Center, Insert Delete Format Sort & Filter Eorma, Clear Allocation Columnd Colum n4d Column5d Colum n6 2 Column1 Column2 Column7 Column8 Total Personnel 13,356 $ 13,356 2160 Travel&Training 2200 Office Expenses 2200 Occupancy 2240 Insurance 2300 Professional Services & Related 2500 Information Ma Total Non-Personnel Tota Total Grand Total 1,108 3,189 2,098 400,000 $ 3,189 2,098 $400,000 $ (163) $ 28,96 28,750 434,607 13,114 421,252 421.464 28,750 28,750 434,820 $ 90 l Exercise+ 125% 4