1.It is January 2, 2012 and you have started your first day of work as the Practice Administrator for the Tri-Cities Geriatric Center (a physician practice serving older adults).Your first job is to project a budget for calendar year 2012. In gathering information for this task, you come across budgets for the past 3 years.However, the former Director did not complete budget worksheets (maybe the reason he was fired), so you don't have complete information.

a.Complete the CY 2009, 2010, and 2011 budgets (Table 1a)

b.create P&L statement for CY 2012, explaining why you projected EACH number.

c.Assume that it is now January 2, 2013 and the actual budget numbers were reported for CY 2012 in Table 1b.perform a budget variance report for CY 2012.What does this variance report tell us about revenues and expenses (profit/loss)?What could you have done to increase profit or prevent loss?

Please show how the numbers are obtained such as formulas or equation so I can better understand how it was done. I want to learn it. (preferably in excel document) but anything works.

Table 18 Tri-Citles Gerlatric Center Historical Financial Data CY 2009 CY2011 Number of visits 12,000 12, 120 12,241 Private Insurance visits 9,793 10 TennCare visits 2,400 2,424 11 12 Net Revenue $557,520 $563,090 13 Private revenue $480,000 $484,800 14 TennCare revenue $72,000 $73,440 15 16 Expenses 17 Salaries and wages $134,933 $136,283 $137,644 18 Physician fees $179,512 $181,307 $183,119 14 Malpractice Insurance $32,013 $32,333 $32,656 20 Travel and education $4.488 $4,533 $4.578 21 General insurance $8,377 $8,461 $8,546 22 Utilities $12,267 $12,389 $12,513 23 Equipment and lease $1,496 $1,511 $1,526 24 Other operating expenses $89,756 $90,654 $91,559 25 Total Operating Expenses $462,842 $467,471 $472,141 26 27 Net profit (108s) 28 29Budget Variance Report 5 6 7 Budget CY 2012 Actual CY 2012 Variance CY 2012 8 Number of visits 14,100 9 Private insurance visits 11,300 10 TennCare visits 2,800 11 12 Net Revenue $598,100 13 Private revenue $508,500 14 TennCare revenue $89,600 15 16 Expenses 17 Salaries and wages $147,835 18 Physician fees $201,334 19 Malpractice insurance $34,686 20 Travel and education $3,700 21 General insurance $8,824 22 Utilities $12,267 23 Equipment and lease $1,672 24 Other operating expenses $107,385 25 Total Operating Expenses $517,703 26 27 Net profit (loss) $80,397 28 29 30