Reference no: EM132374512

Capstone Exercise

You are a division manager for a regional hearing-aid company in Cheyenne, Wyoming. Your sales managers travel frequently to some of the offices in the western region. You need to create a travel expense report for your managers to use to record their budgeted and actual expenses for their travel reports. The draft report contains a title, input areas. and a detailed expense area.

Format the Title and Complete the Input Areas

Your first tasks are to format the title and complete the input area. The input area contains two sections: Standard Inputs that are identical for all travelers and Traveler Inputs that the traveler enters based on his or her trip.

a. Open e01c1Travel and save it as e01c1Travel_lastFirst.

b. Merge and center the title over the range A1:E1 and set the row height for the first row to 40.

c. Apply the Input cell style to the ranges B3:B6, E3:E4, and E6:E7. and then apply the Calculation cell style to cell E5. Part of the borders are removed when you apply these styles.

d. Select the ranges A3:B6 and D3:E7. Apply Thick Outside Borders.

e. Enter 6/1/2018 in cell E3 for the departure date, 6/5/2018 in cell E4 for the return date. 149 in cell E6 tbr the hotel rate per night. and 18% in cell E7 for the hotel tax rate.

f. Enter a formula in cell E5 to calculate the number of days between the return date and the departure date.

Insert 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.

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

b. Enter the amount budgeted for Airport Parking in cell B13. 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, It 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.

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

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

e. Enter the difference between the actual and budgeted expenses in cell E12. Copy the formula to the range 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.

a. Insert a new row 15. Type Destination Expenses in cell A15. Bold the label.

b. Insert a new row 19. Type Other in cell A19. Bold the label.

c. Indent twice the labels in the ranges A12:A14, A16:A18, and A20.

d. Select the range D10:D21 and insert cells to shift the selected cells to the right.

e. Cut the range F10:F21 and paste it in the range D10:D21 to 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.

a. Apply Accounting Number Format to the ranges B12:D12 and B21:D21.

b. Apply Comma Style to the range 1313:D20.

c. Apply Percent Style with one decimal place to the range E12:E20.

d. Underline the range: B20:D20. Do not use the border feature.

e. Apply the cell style Bad to cell D21 because the traveler went over budget.

f. Select the range A10:E21 and apply Thick Outside Borders.

g. Select the range A10:E10, apply Blue-Gray. Text 2, Lighter 80% fill color, apply Center alignment, and apply Wrap Text.

Manage the Workbook

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

a. Spell-check the workbook and make appropriate ciirrections.
 
b. Set a 1.5" top margin and select the margin setting to center the data horizontally on the page.

c. 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.

d. Copy the Expenses worksheet. move the new worksheet to the end, and rename it Formulas.

e. 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.

f. Save and close the file. Based on your instructor's directions. submit e01 clTravel_LastFirst.

Attachment:- e01c1Travel.rar