I need help creating a program using the budget of 850,000 using the info provided in the first picture. I have to fill in the excel spread shown in the picture I provided. I have to include how many workers I would need and the cost? cost of travel and etc. below is an example of what the excel would look like. With a couple definitions.

Case Study 5: Adjusting the Budget Agency X has been doing well financially but wants to provide more services to its clientele. They recently applied for a grant from The New England Health Foundation (NEHF) for $1,000,000 to provide these services. The Foundation has indicated that they are interested in funding AX but can only provide $850,000 of the requested funds. The want a full description of the service that will be provided and they also need to see a budget for how the funds will be allocated both in terms of Personnel and Other than Personnel Services (OTPS). They are requesting a detailed budget with a budget narrative by the end of the work week. NEHF has heard that there has been some mismanagement at the agency and some of the foundation's board are not assured that the money will be spent appropriately. To be sure of the veracity of Agency X in getting this money, they want to see the original budget along with an updated line by line budget showing where Agency X will get the additional $150,000 to cover the cost that the Foundation will not pay for. Assignment The type of services to be provided will be up to you, the department manager. Who your collaborators are (if any), where you get the other funding, and how you structure your program will be up to you. Be creative. You should do some research on approximate cost of items in your budget to make the budget as realistic as possible. Attach or refer to cite on a reference page) anything you used to justify your costs (e.g., Office Max for cost of a desk, Toyota Leasing for a leased vehicle, Progressive for car insurance, etc.). Some things can be estimated (e.g., how much office supplies you will use for the year). Use the Excel spreadsheet to do your budget and narrative. The headings on the spreadsheet are suggestions, with the exception of NEHF, who are the funders for this grant. Make sure you have a narrative that does the math to show how you arrived at your cost for every item even in-kind costs. The A&G for the fiduciary's administrative cost is a fixed cost set at 10% of the budget. Check the Bureau of Labor Statistics for average salaries by industry. Original Budget Revised Budget NEHF Federal (specify) City State In-kind Third- Party** Other Totals The indirect/administrative overhead associated with fiducia managing of funds is calculated at 10% set by the New England Health Foundation 5 NEHF ABG @10% 5 Total Orignal Total Revised include the estimated cost of in kind supports and give basis of estimation includes private insurance Medicaid & Medicare Think of the program you want and what it does then think of the type of people you need to staff it: doctors, nurses, case managers, care coordinator, dentists, whatever. Then to get a realistic picture of what those people "cost" go to the Bureau of Labor Statistics and look up what the typical salary or hourly wage is to get how much one of them in that category will cost you. You can then find out how many of them you will be able to afford for your program. Fringe benefits can also be found at the BLS website (its typically about 37% of the salary) and you add that in the fringe line. Fringe and salaries are all under Personnel. Remember that your program will need more than staff/personnel; that is where OTPS or other than personnel services comes in. Just think about what you will need to run that program beyond the staffing and you will have OTPS. For example, let's say you have a program that will provide ride share to people to and from services. You can either estimate how much an Uber/Lyft ride will cost per person then estimate how many people will need rides per week and multiply that by 52 weeks ($15/ride x 30 rides per week x 52 weeks =$23,400) or you can lease a car from Toyota for $325 per month x 12 months =$3900 and then factor in gas mileage by using the Federal mileage reimbursement rate from the IRS website at .59 cents per mile at an estimate 18000 miles or $10,600. You see? Now you can see which one is cheapest or which one fits in your budget. For each line you should show the math as I did. If any other questions let me know. or $10,600. You see? Now you can see which one is cheapest or which one fits in your budget. For each line you should show the math as I did. If any other questions let me know. The A&G are set amounts that are actually 10% of the total budget (or close to it in some cases depending on the funder). For our budget, it is exactly 10%. So for the original $1,000,000 the A&G is $100,000 and for the revised budget of $850,000 it is $85,000. I am asking for a budget for the $1,000,000 original and a budget for the $850,000 for the revised grant. For each line you have to justify why you are asking for that amount by showing your calculations and describing why you need that item. Remember that Agency X asked for a certain amount and was given a different amount. So you have a choice: (1) Get the $150,000 that NEHF did not give from other sources (Medicaid/Medicare may be one of those sources but not necessarily depending on your program and your needs) or (2) just cut the program down by cutting some services or personnel from the original budget. If you cut the service to fit the new amount you have to put those things in the budget justification such as "we will use two nurses instead of three nurses for the reduced program amounting in the reduction of one salary or -$65,000 and addition - $24,050 for fringe @37%." So that would account for $89,050 of the $150,000 reduction. You are mainly coming up with this yourself. Mostly look at the PowerPoint on budgeting and financing. Al-- --- --- ina ce ------ r voporum payo 20 , Shanks, and Kite. Remember you are creating the program. Give a description of what you plan to do so I know why you are getting the staff and the OTPS that are in your budget. Item Funders (Sources of Revenue) Private In-Kind Medicaid Federal Grants Foundations FFS Total Budget Salary/Fringe 202,500 Personnel Services 34,476 Other than Personnel Services (OTPS) 31,000 93,000 236,976 124,000 84,500 193,531 100,000 30,000 90,000 100,000 50,000 200,000 80,000 35,000 Advocacy Education Serv. Marketing Family Group Mentoring Psych/Therapy Evaluation Mini Grants Audit Meetings Travel Rent Phones Supplies Postage A&G (Admin) Total Cost 11,000 278,031 100,000 120,000 100,000 50,000 280,000 35,000 11,000 15,000 28,000 15,000 4,000 9,000 3,000 80,000 $1.489.007 15,000 20,000 8,000 15,000 4,000 9,000 1,000 80,000 855.000 2,000 265.476 93.000 273.531 An expense budget, which is a prediction of the total expenses that the organization will incur, typically includes such items as labor, supplies, and acuity levels (case mix) and is included on the left-hand side (the debit side) of accounting entries. Remember, an expense is an outflow or an asset that has been used up, and a cost is the resources necessary to provide the service or product you are producing. A revenue budget, shown on the right- hand (credit) side of accounting entries, includes data on forecasted utilization of specific services within the organization and third-party payer mix. Three other terms generally associated with cash budgets are: Cash outflows, which include such expenses as mortgage payments or rents, salaries and wages, benefits, utilities, supplies, and interest paid out; Cash inflows, which include cash payments up front, 30-day and 60-day collections, government appropriations, donations, and any interest earned each month; and, payments up front, 30-day and 60-day collections, government appropriations, donations, and any interest earned each month; and, Ending cash, which comprises both the cash balance at the end of the month and the following month's beginning cash level. The following formula is used to determine this amount: Ending cash = Beginning cash + Cash inflows - Cash outflows. There are two other types of budgets with which health care managers should become familiar. The term statistics budget is given to the initial statistics delineated in the operating plan that forecasts service utilization (by service type, acuity level or case mix, and payer mix), resource use, and policy data (employment data, occupancy rates, staffing ratios, etc.). The capital budget refers to the plan for expenditures for new facilities and equipment (often referred to as fixed assets). The following discussion will focus on the latter. Case Study 5: Adjusting the Budget Agency X has been doing well financially but wants to provide more services to its clientele. They recently applied for a grant from The New England Health Foundation (NEHF) for $1,000,000 to provide these services. The Foundation has indicated that they are interested in funding AX but can only provide $850,000 of the requested funds. The want a full description of the service that will be provided and they also need to see a budget for how the funds will be allocated both in terms of Personnel and Other than Personnel Services (OTPS). They are requesting a detailed budget with a budget narrative by the end of the work week. NEHF has heard that there has been some mismanagement at the agency and some of the foundation's board are not assured that the money will be spent appropriately. To be sure of the veracity of Agency X in getting this money, they want to see the original budget along with an updated line by line budget showing where Agency X will get the additional $150,000 to cover the cost that the Foundation will not pay for. Assignment The type of services to be provided will be up to you, the department manager. Who your collaborators are (if any), where you get the other funding, and how you structure your program will be up to you. Be creative. You should do some research on approximate cost of items in your budget to make the budget as realistic as possible. Attach or refer to cite on a reference page) anything you used to justify your costs (e.g., Office Max for cost of a desk, Toyota Leasing for a leased vehicle, Progressive for car insurance, etc.). Some things can be estimated (e.g., how much office supplies you will use for the year). Use the Excel spreadsheet to do your budget and narrative. The headings on the spreadsheet are suggestions, with the exception of NEHF, who are the funders for this grant. Make sure you have a narrative that does the math to show how you arrived at your cost for every item even in-kind costs. The A&G for the fiduciary's administrative cost is a fixed cost set at 10% of the budget. Check the Bureau of Labor Statistics for average salaries by industry. Original Budget Revised Budget NEHF Federal (specify) City State In-kind Third- Party** Other Totals The indirect/administrative overhead associated with fiducia managing of funds is calculated at 10% set by the New England Health Foundation 5 NEHF ABG @10% 5 Total Orignal Total Revised include the estimated cost of in kind supports and give basis of estimation includes private insurance Medicaid & Medicare Think of the program you want and what it does then think of the type of people you need to staff it: doctors, nurses, case managers, care coordinator, dentists, whatever. Then to get a realistic picture of what those people "cost" go to the Bureau of Labor Statistics and look up what the typical salary or hourly wage is to get how much one of them in that category will cost you. You can then find out how many of them you will be able to afford for your program. Fringe benefits can also be found at the BLS website (its typically about 37% of the salary) and you add that in the fringe line. Fringe and salaries are all under Personnel. Remember that your program will need more than staff/personnel; that is where OTPS or other than personnel services comes in. Just think about what you will need to run that program beyond the staffing and you will have OTPS. For example, let's say you have a program that will provide ride share to people to and from services. You can either estimate how much an Uber/Lyft ride will cost per person then estimate how many people will need rides per week and multiply that by 52 weeks ($15/ride x 30 rides per week x 52 weeks =$23,400) or you can lease a car from Toyota for $325 per month x 12 months =$3900 and then factor in gas mileage by using the Federal mileage reimbursement rate from the IRS website at .59 cents per mile at an estimate 18000 miles or $10,600. You see? Now you can see which one is cheapest or which one fits in your budget. For each line you should show the math as I did. If any other questions let me know. or $10,600. You see? Now you can see which one is cheapest or which one fits in your budget. For each line you should show the math as I did. If any other questions let me know. The A&G are set amounts that are actually 10% of the total budget (or close to it in some cases depending on the funder). For our budget, it is exactly 10%. So for the original $1,000,000 the A&G is $100,000 and for the revised budget of $850,000 it is $85,000. I am asking for a budget for the $1,000,000 original and a budget for the $850,000 for the revised grant. For each line you have to justify why you are asking for that amount by showing your calculations and describing why you need that item. Remember that Agency X asked for a certain amount and was given a different amount. So you have a choice: (1) Get the $150,000 that NEHF did not give from other sources (Medicaid/Medicare may be one of those sources but not necessarily depending on your program and your needs) or (2) just cut the program down by cutting some services or personnel from the original budget. If you cut the service to fit the new amount you have to put those things in the budget justification such as "we will use two nurses instead of three nurses for the reduced program amounting in the reduction of one salary or -$65,000 and addition - $24,050 for fringe @37%." So that would account for $89,050 of the $150,000 reduction. You are mainly coming up with this yourself. Mostly look at the PowerPoint on budgeting and financing. Al-- --- --- ina ce ------ r voporum payo 20 , Shanks, and Kite. Remember you are creating the program. Give a description of what you plan to do so I know why you are getting the staff and the OTPS that are in your budget. Item Funders (Sources of Revenue) Private In-Kind Medicaid Federal Grants Foundations FFS Total Budget Salary/Fringe 202,500 Personnel Services 34,476 Other than Personnel Services (OTPS) 31,000 93,000 236,976 124,000 84,500 193,531 100,000 30,000 90,000 100,000 50,000 200,000 80,000 35,000 Advocacy Education Serv. Marketing Family Group Mentoring Psych/Therapy Evaluation Mini Grants Audit Meetings Travel Rent Phones Supplies Postage A&G (Admin) Total Cost 11,000 278,031 100,000 120,000 100,000 50,000 280,000 35,000 11,000 15,000 28,000 15,000 4,000 9,000 3,000 80,000 $1.489.007 15,000 20,000 8,000 15,000 4,000 9,000 1,000 80,000 855.000 2,000 265.476 93.000 273.531 An expense budget, which is a prediction of the total expenses that the organization will incur, typically includes such items as labor, supplies, and acuity levels (case mix) and is included on the left-hand side (the debit side) of accounting entries. Remember, an expense is an outflow or an asset that has been used up, and a cost is the resources necessary to provide the service or product you are producing. A revenue budget, shown on the right- hand (credit) side of accounting entries, includes data on forecasted utilization of specific services within the organization and third-party payer mix. Three other terms generally associated with cash budgets are: Cash outflows, which include such expenses as mortgage payments or rents, salaries and wages, benefits, utilities, supplies, and interest paid out; Cash inflows, which include cash payments up front, 30-day and 60-day collections, government appropriations, donations, and any interest earned each month; and, payments up front, 30-day and 60-day collections, government appropriations, donations, and any interest earned each month; and, Ending cash, which comprises both the cash balance at the end of the month and the following month's beginning cash level. The following formula is used to determine this amount: Ending cash = Beginning cash + Cash inflows - Cash outflows. There are two other types of budgets with which health care managers should become familiar. The term statistics budget is given to the initial statistics delineated in the operating plan that forecasts service utilization (by service type, acuity level or case mix, and payer mix), resource use, and policy data (employment data, occupancy rates, staffing ratios, etc.). The capital budget refers to the plan for expenditures for new facilities and equipment (often referred to as fixed assets). The following discussion will focus on the latter