Using the Excel spreadsheet template provided, build a spreadsheet by preparing the following statements for: Metropolitan Dental Associates Professional Services Budget; Dental Practice; Activity-Based Budgeting Chapter 9 (Textbook): (LO 9-3,9-4,9-5) Metropolitan Dental Associates is a large dental practice in Chicago. The firm's controller is preparing the budget for the next year. The controller projects a total of 48,000 office visits, to be evenly distributed throughout the year. Eighty percent of the visits will be half-hour appointments, and the remainder will be one-hour visits. The average rates for professional dental services are $75 for half-hour appointments and $125 for one-hour office visits. Ninety percent of each month's professional service revenue is collected during the month when services are rendered, and the remainder is collected the month following service. Uncollectible billings are negligible Metropolitan's dental associates earn $105 per hour Metropolitan uses activity-based budgeting to budget office overhead and administrative expenses. Two cost drivers are used: office visits and direct professional labor. The cost-driver rates are as follows Patient registration and records All other overhead and administrative expenses $3.60 $6.50 per office visit (of any length) per direct professional labor hour Required 1. Prepare the direct-professional-labor budget schedule for the month of June. 2. Prepare the budget schedule for cash collections during June for professional services rendered during May and June. 3. Prepare the overhead and administrative expense budget schedule for the month of June. Directions: Use the Data to build your cell formula computations for items 1, 2 and 3. In put your forumulas in the "blue boxes." 9 10 12 13 14 15 16 17 18 19 20 21 Professional Services Budget; Dental Practice; Activity Based Budgeting 23 24 25 26 27 28 1. Direct professional labor budget schedule for the month of June Office visits per month 8 Professional services in June: 0 1-hour visits 11/2 hour visits 2 Total direct professional labor hours hours hours Hourly rate for dental associates Total direct professional labor cost 5 2. Cash collections during June for professional services rendered during May and June May June 9 1/2 hour visits O Billing rate 1 Total billings for 1/2-hour visits 3 1-hour visits 4 Billing rate 5 Total billings for 1-hour visits 7 Total billings during the month 8 Percentage of month's billings collected during June 9 Collections during June 1 Total collections in June 3. Overhead and administrative expense budget schedule for the month of June 5 Patient registration and records 6 Other overhead and adminstrative expenses 7 Total overhead and adminstrative expenses visits hours pervist per hour