sert Formulas

The Detailed Expenses section contains the amount budgeted for the trip, the actual expenses reported by the traveler, percentage of the budget spent on each item, and the amount the actual expense went over or under budget. You will insert formulas for this section. Some budgeted amounts are calculated based on the inputs. Other budgeted amounts, such as airfare, are estimates.

Enter the amount budgeted for Mileage to/from Airport incell B12. The amount is based on the mileage rate and roundtrip to the airport from the Standard Inputs section.

Enter the amount budgeted for Airport Parking in cellB13. This amount is based on the airport parking daily rate and the number of total days traveling (the number of nights + 1) to include both the departure and return dates. For example, if a person departs on June 1 and returns on June 5, the total number of nights at a hotel is 4, but the total number of days the vehicle is parked at the airport is 5.

Enter the amount budgeted for Hotel Accommodations incell B16. This amount is based on the number of nights, the hotel rate, and the hotel tax rate.

Enter the amount budgeted for Meals incell B17. This amount is based on the daily meal allowance and the total travel days (# of hotel nights + 1).

Enter the % of Budget incell D12. This percentage indicates the percentage of actual expenses to budgeted expenses. Copy the formula to the rangeD13:D18.

Enter the difference between the actual and budgeted expenses incell E12. Copy the formula to therange E13:E18. If the actual expenses exceeded the budgeted expenses, the result should be positive. If the actual expenses were less than the budgeted expense, the result should be negative, indicating under budget.

Add Rows, Indent Labels, and Move Data

The Detailed Expenses section includes a heading Travel to/from Destination. You want to include two more headings to organize the expenses. Then you will indent the items within each category. Furthermore, you want the monetary columns together, so you will insert cells and move the Over or Under column to the right of the Actual column.

Insert a new row 15. TypeDestination Expensesincell A15. Bold the label.

Insert a new row 19. TypeOtherincell A19. Bold the label.

Indent twice the labels in theranges A12:A14,A16:A18, andA20.

Select therange D10:D21and insert cells to shift the selected cells to the right.

Cut therange F10:F21and paste it in therange D10:D21to move the Over or Under data in the new cells you inserted.

Format the Detailed Expenses Section

You are ready to format the values to improve readability. You will apply Accounting Number Format to the monetary values on the first and total rows, Comma Style to the monetary values in the middle rows, and Percent Style for the percentages.

ApplyAccounting Number Formatto theranges B12:D12andB21:D21.

ApplyComma Styleto therangeB13:D20.

ApplyPercent Stylewith one decimal place to therange E12:E20.

Underline therange: B20:D20. Do not use the border feature.

Apply the cell styleBadtocell D21because the traveler went over budget.

Select therange A10:E21and applyThick Outside Borders.

Select therange A10:E10, applyBlue-Gray, Text 2, Lighter 80% fill color, applyCenteralignment, and apply Wrap Text.

Manage the Workbook

You will apply page setup options, insert a footer, and, then duplicate the Expenses statement worksheet.

Spell-check the workbook and make appropriate corrections.

Set a1.5"top margin and select the margin setting to center the data horizontally on the page.

Insert a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side.

Copy the Expenses worksheet, move the new worksheet to the end, and rename itFormulas.

Display the cell formulas on the Formulas worksheet, change to landscape orientation, and adjust column widths. Use the Page Setup dialog box or the Page Layout tab to print gridlines and row and column headings.

Save and close the file. Based on your instructor's directions, submit e01c1Travel_LastFirst.

Travel Expense Report Standard Inputs Daily Meal Allowance Airport Parking Daily Rate Mileage Rate to/from Airport Roundtrip Miles to Airport Detailed Expenses Budget Item Travel to/from Destination Mileage to/from Airport Airport Parking Airfare Shuttle to/from Hotel Hotel Accommodations Meals Incidentals Totals $ $ $ Traveler Inputs Depart Date Return Date # of Nights Hotel Rate/Night Hotel Tax Rate 65.00 9.00 0.60 20.00 Budget Actual 475 50 50 575 12 40.75 534.2 75 710 320 45 1736.95 % of Budget Over or Under 0