To assess your understanding of the material covered in the chapter, complete the following assignment.

Basic Monthly Budget for Medical Office

Download Data File:  PR1 Data

Creating and maintaining budgets are common practices in many careers. Budgets play a critical role in helping a business or household control expenditures. In this exercise you will create a budget for a hypothetical medical office while reviewing the skills covered in this chapter.

1. Open the file name PR1 Data, then Save As PR1 Medical Office Budget.
2. Activate all the cell locations in the Sheet1 worksheet by clicking the Select All button in the upper left corner of the worksheet.
3. In the Home tab of the Ribbon, set the font style to Arial and the font size to 12 points.  Then click any cell to Deselect.
4. Increase the width of Column A so all the entries in the range A3:A8 are visible. Place the mouse pointer between the letter A and letter B of Column A and Column B. When the mouse pointer changes to a double arrow, left click and drag it to the right until the character width is approximately 18.00.
5. Enter Quarter 1 in cell B2.
6. Use AutoFill to complete the headings in the range C2:E2. Activate cell B2 and place the mouse pointer over the Fill Handle.
7. Select the range B2:E2 and click the Format button in the Home tab of the Ribbon. Click the Column Width option, type 11.57 in the Column Width dialog box, and then click the OK button in the Column Width dialog box.
8. Enter the words Medical Office Budget in cell A1.
9. Insert a blank column between Columns A and B by clicking on any cell in Column B. Then, click the drop-down arrow of the Insert button in the Home tab of the Ribbon. Click the Insert Sheet Columns option.
10. Enter the words Budget Cost in cell B2.
11. Adjust the width of Column B to approximately 12.0 characters.
12. Select the range A1:F1 and click the Merge & Center button in the Home tab of the Ribbon to merge the cells in that range.
13. Make the following format adjustments to the range A1:F1: bold; italics; change the font size to 14 points; change the cell fill color to Aqua, Accent 5, Darker 50%; and change the font color to white.
14. Increase the height of Row 1 to approximately 24.75 points.
15. Make the following format adjustment to the range A2:F2: bold; and fill color to Tan, Background 2, Darker 10%. Center the column titles so that they are horizontally centered in each cell.
16. Select B2 and choose the Wrap Text button in the Home tab of the Ribbon. Increase the height of Row 2 to approximately 30 points.
17. Copy cell C3 and paste the contents into the range D3:F3.
18. Copy the contents in the range C6:C8 by highlighting the range and clicking the Copy button in the Home tab of the Ribbon. Then, highlight the range D6:F8 and click the Paste button in the Home tab of the Ribbon.
19. Calculate the total budget for all four quarters for the salaries. Click cell B3 and click the down arrow on the AutoSum button in the Formulas tab of the Ribbon. Click the Sum option from the drop-down list. Then, highlight the range C3:F3 and press the ENTER key on your keyboard.
20. Copy the formula in cell B3 and paste them into the range B4:B8.
21. Format the range B3:F8 with Accounting format and zero decimal places.
    If any of the cells display pound symbols (######), simply widen the column to display the values again.
22. Select the range A1:F8 and click the down arrow next to the Borders button in the Home tab of the Ribbon. Select the All Borders option from the drop-down list.
23. Double click the Sheet1 worksheet tab to change the name of Sheet1 to the word Budget, and press the ENTER key.  Delete any unnecessary worksheets.
24. Change the orientation of the Budget worksheet so it prints landscape instead of portrait.
25. Use Fit to 1 page so the Budget worksheet prints on one piece of paper, if it does not already.
26. Add a header to the Budget worksheet that shows the date in the upper left corner and your name in the center.
27. Add a footer to the Budget worksheet that shows the page number in the lower right corner.
28. Check the spelling on the worksheet and make any necessary changes. Save PR1 Medical Office Budget workbook.
29. Compare your work to the screenshot below and then submit the PR1 Medical Office Budget workbook as directed by your instructor.

Attribution

Adapted by Barbara Lave from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.