cost analysis worksheet | BIS/221
Please follow these instructions, attached is the worksheet that has the layout and numbers. Please follow the instructions to the T. The instructor is a bit of a stickler. Only do steps, 1–5 and 7–9. Thank you!!
1.Use the following formulas in cells E4, F4, and G4:
Mileage Cost (cell E4) = Cost per Mile * Miles Driven or = B4 * C4
Total Cost (cell F4) = Maintenance Cost + Mileage Cost or = D4 + E4
Total Cost per Mile (cell G4) = Total Cost / Miles Driven or = F4 / C4
Use the fill handle to copy the three formulas in the range E4:G4 to the range E5:G12.
2.Determine totals for the miles driven, maintenance cost, mileage cost, and total cost in row 13. Copy the formula in cell G12 to G13 to assign the formula in cell G12 to G13 in the total line. If necessary, reapply the Total cell style to cell G13.
3.In the range D14:D16, determine the average value, highest value, and lowest value, respectively, for the values in the range D4:D12. Use the fill handle to copy the three functions to the range E14:G16.
4.Format the worksheet as follows:
a.Change the workbook theme to Vapor Trail by using the Themes button (Page Layout tab | Themes group)
b.Cell A1 — change to Title cell style
c.Cell A2 — change to a font size of 16
d.Cells A1:A2 — Red, Accent 1, Lighter 60% fill color and a thick outside borders
e.Cells B4, D4:G4, and D13:G13 —accounting number format with two decimal places and fixed dollar signs by using the ‘Accounting Number Format’ button (Home tab | Number group)
f.Cells B5:B12, and D5:G12 — comma style format with two decimal places by using the Comma Style button (Home tab | Number group)
g.Cells C4:C13 — comma style format with no decimal places.
h.Cells G4:G12 — apply conditional formatting so that cells with a value greater than 1.85 appear with a red background color and white font
5.If necessary increase the size of any columns that do not properly display data.
6.Switch to Page Layout view. Enter your name, course, laboratory assignment number, and any other information, as specified by your instructor, in the Header area.
7.Preview and print the worksheet in landscape orientation. Save the workbook using the file name, Apply 2-1 Proximity Bus Complete.
8.Use Range Finder to verify the formula in cell G13.
9.Print the range A3:D16. Press CTRL+ACCENT MARK (`) to change the display from the values version of the worksheet to the formulas version. Print the formulas version in landscape orientation on one page by using the Fit to option in the Page tab in the Page Setup dialog box. Press CTRL+ACCENT MARK (`) to change the display of the worksheet back to the values version. Close the workbook without saving it.
10. Besides adding a header to your document, can you think of anything else that could be added when printing the worksheet?
11.Submit the workbook as specified by your instructor.