I am using Excel as part of a computer class. The last assignment is more of an intermediate Excel... this is difficult because I don't use the program that often, not enough to have the first clue how to accomplish this assignment correctly.
Click on this link Expense Report, save it on your computer in your usual work folder, and then open it in Excel. This partially completed workbook contains an incomplete expense report that needs formatting changed, boxes filled in with appropriate information and formulas created for correct calculations.
First, click on the small box that is to the left of the 1 and above the letter A in the column, row indicator areas. This will highlight the entire spreadsheet.
Next, position your cursor in between columns A and B so that your cursor looks like a plus sign with arrows on the left and right. Double click here to make the columns fit around the existing data. To finish, click anywhere in the worksheet once to remove the highlight from the entire page.
Create a border around the data entry portion of this spreadsheet. This is done by selecting cells A8:M12. Put a border around the outside of this area only.
Fill in all blank boxes with appropriate data within the A8:M12 data range. For text or date information, provide appropriate text or date entries. Reasonable numbers can be put in, or the number 0 is valid. For example, lunch should not cost 678 dollars. That is unreasonable.
Format all of the dollar figure numbers as 2 digit United States currency.
In cell I4, type the words, "Allowance Per Mile." In cell L4, type the number 0.40. Format this number as currency. Select cells I4:K4 and use the merge and center button to format this.
In the Daily Totals column, calculate the cost for each day. To do this, type this formula: =SUM(C8:H8,J8:M8). Copy this formula down to the other appropriate rows below.
Next, calculate to totals for each category. In row 14 under the Brkfast column, add all of the numbers above it using a SUM formula, then copy it across under each and every column.
In cell J8, calculate the cost allowance per mile under the mileage$ column for each date. You need to multiply the mileage by the mileage allowance. Be sure to use an absolute reference when referring to the mileage allowance since it is not in the table and only occurs once at the top of the sheet.
Copy this formula down to each of the four columns directly below cell J8.
Make all headings Bold.
Create a report title by typing the words, "Expense Report" in cells E2. Select cells E2:J2 and use the Merge and Center button to center the title across the report. Change its font size to 14. Also, use the Merge and Center button to place the words "Transportation" above "Air and Ground" and "Meals" above "Brkfast, Lunch, and Dinner." Don't forget to format these words to match the other headings.
Format the dates into the full month name, day and year format.
Finally, repeat steps 2 and 3 so that the spreadsheet has all of the data showing in it.
Save your completed report as Expense Report 1.
Submit the assignment.
Open your old IP4 assignment and Save As "Expense Report 3" in your working folder on your computer.
In this exercise you are going to create a chart showing the expenses in the categories listed below. You first need to calculate the data in a summary section of your spreadsheet. The data in the summary section will be used to create the chart.
First, in cells O16:O21, enter the following labels (headers): "Summary", "Travel", "Meals", "Entertain", "Phone", "Other".
Cells P17 through P21 will hold the data you will calculate to get the total expense for each category. For example, if you went on a business trip, you would need to enter the amount you spent on travel in one cell, the amount you spent on meals in another cell, and so on. Then you would use the SUM function to get a total of your expenses for the trip. Put the totals for each category next to the labels you've created. These formulas go in cells P17 through P21.
For Travel, include expenses for Ground and Air transportation, Lodging, and Mileage $. Be sure to only include dollars in your formula.
For Meals, include expenses for Brkfast, Lunch, and Dinner.
Do the same or similar for Meals, Entertain and other. If a category does not require the addition of one or more totals, do not use SUM or any other built in functions.
Finally, in cell P22, enter a function to show the total of cells P17:P21.You can now use this summary to create the pie chart.
Create a pie chart to show the graphical representation of the expenses, using the figure below as a guide. Create the chart on its own chart sheet, named Pie Chart. Make sure that there is a legend used in the pie chart. Include category names and percentages for data labels on your chart. Hint: Be sure not only to include your detail items in your data range for the chart, but also include the category names.
Add Line Callouts as shown in the figure. (Click the Insert menu, then the Shapes icon)
Travel consists of Air + Ground + Lodging + Mileage $.
Meals consist of Brkfast + Lunch + Dinner.
Rename the sheet tab for the data spreadsheet to Expense Report.
Save your final report as Expense Report 4
Submit your report.
Bookmarks