代写Computing Term 2 Advanced Excel Practice Exam代写C/C++程序

Computing Term 2 Advanced Excel Practice Exam

Time: 40 Minutes + 5 minutes reading time

Marks: 60

Note: This exam has 1 file, containing one instruction sheet and three (3) worksheets.

MyCar RENTALS

After opening the file MyCar Rentals.xlsx, save as Z#######MyCar Rentals.xlsx where Z####### is your ZID, and work on the file with your ZID. In addition to the instruction worksheet, this workbook has 3 worksheets: Car Model Hired, Statistics and Car Loan Calculator. SAVE your work frequently while you are completing this activity. Each worksheet contains the same instructions to follow that are on this instruction sheet. You may solve the worksheets in any order, but should complete the stepson the worksheet in the same order as the instructions. If you cannot solve a step, move to the nextstep and go back if you have time. You may add extra columns if you wish, to assist with calculation.

Part A - Car Model Hired (25 Marks)

MyCar Rentals is a car hire company that hires different types of cars.

The worksheet Car Model Hired shows details of customers who have hired cars during the first quarter of 2021.

Insert appropriate functions and formulas, using absolute cell referencing where necessary, to complete this sheet as follows:

1. Age at Time of Return (column C)

•    Use the date hired to calculate each customer’sage at the time they returned the car. Show age as a whole number.

2. Total Days Hired (column H)

•    Use the date hired and the date returned to calculate the number of days each customer hired the car.

3. Cost Per Day (column I)

•    Using the information provided below the spreadsheet, calculate the cost per day.

4. Cost Per Km (column J)

•    Using the information provided below the spreadsheet, calculate the cost per kilometre.

5. Total Cost (column K)

•    Calculate the total cost for each customer.  This will vary based on the following:

a.    The number of days hired and the cost per day

b.    The number of kilometres travelled and the cost per kilometre

c.    Drivers aged 26 or under at the time of hiring the car pay an additional charge of 30% on the Cost Per Day

6. SUMMARY (from row 36)

• Calculate the Total Cost for each Car Model hired

7. Format the spreadsheet as follows:

• Main Heading in Row 1:  merge and centre the main heading across all columns in the

worksheet. Increase the font size to 16pts, bold, and add a light colour shading to the cell.

• Column Headings in Row 3: Wrap the text for all column headings, and align with the data in the column. Format the column headings to bold, with middle alignment. Apply a light colour shading. Ensure all heading text is visible and is not cutoff.

• Format all cells containing monetary values to Currency with 2 decimal places.

•    Add borders and shading to the Car Model table and the Summary table. Ensure all heading text is proper aligned, visible and is not cut off.

Save your file.

Part B - Statistics (10 Marks)

This worksheet has yearly statistics for income generated from the hire of cars from 2016 to 2020. These statistics show the income generated by Car Type as well as by its Use (Leisure or Commercial).

1.    Use the data on the worksheet to create the combination chart shown below comparing each Car Type, as well as the income by Use.

•    Pay careful attention to data selected, titles and formatting. The chart colours maybe different on your computer - this is not important.

•    Change the line marker type to a square, and increase the size of the markers to Size 8.

• Choose a smoothed line for both lines.

•    Format the values on both axes to show 0 decimal places - this must be

changed on the chart only - do not alter the decimal places in the spreadsheet values.

•    Change the primary axis options and secondary axis options so they are displayed in units like below

•    Move the chart to another sheet named Income Chart

Save your file.

Part C - Car Loan Calculator (25 Marks)

MyCar Rentals sells some of its hire cars after a period of time and offers customers loans for the cost of the cars. Customers who wish to buy a car can use this calculator to compare loan repayments, to see whether they can afford the repayments for their preferred car. Customers only need to choose the car model and enter the amount they can pay each week to payoff the loan. Assume that the customer needs to take a loan for the full cost of the car.

Prepare this sheet as follows:

1. Car Model (B4)

•    The customer must choose from a drop-down list of cars available for sale. (The Car List is

shown at the bottom of the spreadsheet.) Include suitable input and error messages. For this activity, choose the car model Kia Picanto from the list.

2.    How much can you afford to repay each week? (E4)

•    The customer must type in a value that they can afford to pay each week. This must be a whole number. Include suitable input and error messages. For this activity, type in the value $100.

Tip: format this cell to currency.

Insert appropriate functions or formulas that will automatically calculate the following:

3. Cost of Car (C4)

•    This cell will display the cost of the car based on the car model chosen by the customer referring to the Cars for Sale table.

4. Monthly Repayment including Insurance (D4)

•    This cell will display the monthly repayment (assume 4 weeks per month) to be made for the full cost of the car, based on theYearly Interest Rate (row 14), Loan Period (Years) (row 15), and Insurance (Monthly) (row 11).

5. Affordable Loan? (F4)

•    If the loan is affordable (the customer is able to repay the monthly repayment including insurance) the cell should display Yes.  If the loan is not affordable, the word No will be  displayed.

•    Apply conditional formatting so that the cell is shaded yellow and the text is red if No is displayed

•    Test this formula by changing the values in cells B4 and E4.

6. Formatting

•    Format this worksheet so that all information is clear and easy to read.

•    All money values should be formatted to currency with no decimal places.