YO19_Excel_Ch01_Assessment_TCO_Instructions.docx

Grader – Instructions Excel 2019 Project

YO19_Excel_Ch01_Assessment_TCO

Project Description:

Most people own, or will at some time own, an automobile. Few actually take the time to calculate what owning an automobile actually costs, in other words the total cost of ownership. This is an important calculation for both individuals and for businesses. In this project, you will complete the development of an automobile total cost of ownership worksheet for your supervisor, Jan Bassy, CFO at your place of employment.

Steps to Perform:

Step	Instructions	Points Possible
1	Start Excel. Download and open the file named Excel_Ch01_Assessment_TCO.xlsx. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.	0
2	Rename Sheet1 Documentation and then rename Sheet2 AutoTCO	1.6
3	To ensure there are no blank worksheets in the workbook, delete the Sheet3 worksheet.	0.8
4	In the AutoTCO worksheet, enter the values as shown below into the specified cells: Data Item Cell Value Miles Driven / year E4 15000 Fuel Cost / Gallon E5 3.15 MPG E6 29	1.8
5	To create a worksheet title, merge and center the worksheet heading across cell range A1:F1.	2
6	To save yourself time, copy and paste data to other cells within a worksheet or workbook. Select cell range C16:C22, and then copy the selected range to the Clipboard. Paste the copied range to cell range D16:F16. Note, you are copying and pasting the formulas in cell range C16:C22.	2
7	Select cell range B15:C15. Using the fill handle, copy the range through cell F15.	2
8	Insert a row above row 15 to allow space to add a descriptive title to the section.	2
9	In cell B15, type 5-year Total Cost of Ownership Analysis Apply Center Across Selection to cell range B15:F15.	1.6
10	In cell D6, insert the note Miles per gallon	1
11	Headers and footers can add more information to a worksheet. Add the File Name code in the left footer of both the AutoTCO and Documentation worksheets.	1.2
12	In the Documentation worksheet, enter the values as shown below into the specified cells: C8 Completed Ms. Bassy’s Automobile Total Cost of Ownership worksheet B20 AutoTCO	0.8
13	At times text in a cell can be longer than the width of the cell. Wrapping the text in a cell will allow the text to stack within the cell. In cell C8 of the Documentation worksheet, apply Wrap Text.	0.4
14	Changing the widths of columns can enhance the look of a worksheet. On the AutoTCO worksheet, change the width of Column A to 20 Change the width of Columns B:F to 12.	0.8
15	Documentation worksheets are usually the last (right) sheet of a workbook. Move the AutoTCO worksheet to the left of the Documentation worksheet to make the Documentation worksheet the last worksheet in the workbook.	0.6
16	Prepare the worksheets for printing by changing the orientation of the AutoTCO worksheet to Landscape Orientation. For the Documentation worksheet, set the orientation to Landscape Orientation. On the Documentation worksheet, set the page scaling to Fit All Columns on One Page.	1.4
17	Save and close the Excel_Ch01_Assessment_TCO workbook. Exit Excel. Submit the file as directed.	0

Total Points

Created On: 05/16/2020 1 YO19_Excel_CH01_Assessment – TCO 1.0

McDonald_Excel_Ch01_Assessment_TCO.xlsx

Sheet1

Kallio Auto Sales
Create Date	By Whom	Description	Workbook Name
5/18/22	Jan Bassy	Automobile TCO Analysis	Excel_Ch01_Assessment_TCO.xlsx
Mod. Date	By Whom	Mod. Description	Last Version Backup Name Author: VERSION BACKUP NAME : Before modifying any worksheet, save the original workbook with the following name format: Original name_yyyymmdd
Create Date	Sheet Name	Creator	Purpose
5/18/22	Sheet2	Jan Bassy	5-year TCO for an automobile

Sheet2

Automobile Total Cost of Ownership Calculator
Model:	Impala LT
Purchase Price:	$ 31,115	Miles Driven / year:
% Down Payment:	10%	Fuel Cost / Gallon:
Annual Interest Rate:	4.75%	MPG:
Loan Term (months):	60	Oil Change Miles:	5,000
5-year Residual Value:	50%	Oil Change Cost:	$ 35
License Cost:	$ 125	Purchase Tax Rate:	5%
Ins. Increase / Year	2%	Paperwork Fees:	$ 150
Year	1	2	3	4	5
Depreciation Rate:	35%	20%	17%	15%	13%
Maintenance:	0%	0%	0%	3%	4%
Year 1	Year 2
Depreciation:	$5,445.13	$3,111.50
Financing:	$1,220.46	$973.71
Taxes & Fees:	$1,675.18	$125.00
Fuel:	ERROR:#DIV/0!	ERROR:#DIV/0!
Insurance:	$1,200.00	$1,224.00
Maintenance:	$0.00	$0.00
TOTAL COST:	ERROR:#DIV/0!	ERROR:#DIV/0!

YO19_Excel_Ch01_Assessment_TCO