Question Details

[solution] » Office 2013 - myitlab:grader - Instructions Excel Project EXP

Brief item decscription

Step-by-step solution file


Item details:

Office 2013 - myitlab:grader - Instructions Excel Project EXP
More:

need help with the steps in order to complete this assignment please


Office 2013 ? myitlab:grader ? Instructions Excel Project EXP E Capstone 1 - European Excursions 2.5

 

Project Description:

 

You work for a travel company that specializes in arranging travel accommodations for student

 

tours and vacations in exciting destinations such as Canada, Rome, and the Czech Republic. You

 

created a workbook to store agent names, student IDs, and tour codes. The workbook also contains

 

a work- sheet to store lookup tables. You will complete the workbook for your manager?s approval.

 

You will insert formulas and a variety of functions, convert data to a table, sort and filter the table,

 

and prepare a chart.

 

Instructions:

 

For the purpose of grading the project you are required to perform the following tasks:

 

Step Points

 

Possible Instructions 1 Start Excel. Open exploring_ecap_grader_h1_Trips.xlsx and save the workbook as

 

e00a1Trips_LastFirst. Make sure the Lookup Tables sheet is active. 0.000 2 Assign the range name rates to the base price and commission range. Assign the

 

range name tours to the data for the package, tour description, departure, and

 

base cost. 2.000 3 Click cell D13 on the Data sheet and insert a VLOOKUP function that looks up the

 

tour code, compares it to the tours table, and returns the description. Copy the

 

function down through cell D22. 4.000 4 Click cell E13 and insert a lookup function that looks up the tour code, compares it

 

to the tours table, and returns the departure date. Copy the function down through

 

cell E22. 4.000 5 Click cell F13 and insert a lookup function that looks up the tour code, compares it

 

to the tours table, and returns the base cost of the trip. Copy the function down

 

through cell F22. 4.000 6 7 8 9 In cell G13, insert a formula that adds taxes and fees to the base cost of the trip (in

 

cell F13) by using the percentage value in the input area. Use a mixed reference to

 

the cell containing 20% in the input area above the data. Copy the formula down

 

through cell G22.

 

In cell H13, insert a PMT function to calculate the payments for students who want

 

to pay for their trips in three installments. Use the interest rate and months in the

 

input area above the data. Use appropriate relative, mixed, and/or absolute cell

 

references in the formula. Make sure the result is a positive value. Copy the

 

function down through cell H22.

 

Click cell I13 and calculate the agent commission using the base cost of the trip

 

and a VLOOKUP function that returns the commission rate based on the base cost

 

of the trip using the rates lookup table. The function should then calculate the

 

monetary value of the commission. Copy the function down through cell I22.

 

Merge and center the main title on the first row over all data columns on the Data

 

sheet. Apply bold and 18 pt font size. Merge and center the subtitle on the second

 

row over all data columns. Updated: 02/26/2015 1 4.000 4.000 4.000 6.000 Current_Instruction.docx Office 2013 ? myitlab:grader ? Instructions Step Excel Project Points

 

Possible Instructions 10 Apply Currency number format to the monetary values in columns F, G, H, and I.

 

Hide the Tour Code column. 4.000 11 Wrap text in the range F12:I12. Set the column widths for these columns to 11, if

 

necessary. Adjust the row height, if necessary. Freeze the panes so that the row of

 

column labels does not scroll offscreen. 4.000 12 Apply the Light Red Fill with Dark Red Text conditional formatting to values in the

 

Total Cost with Taxes column when the values are above average. 4.000 13 Insert a function to calculate the total for all trips (including taxes) in cell G5, the

 

average trip cost in cell G6, and the median trip cost in cell G7. 4.000 14 Insert a function to calculate the lowest trip cost in cell G8 and the highest trip

 

cost in cell G9. Click cell G10 and enter a function to display today's date. 6.000 15 Copy the Data sheet and place the copied sheet before the Summary sheet.

 

Remove the conditional formatting rule on the Data (2) sheet. 4.000 16 Convert the data range in the Data (2) sheet to a table. Apply the Table Style

 

Medium 21 style to the table. 4.000 17 Sort the table by departure date from oldest to newest and then alphabetically by

 

trip description. Apply a filter to display trips arranged by agents Avery and Ross

 

only. 4.000 18 Display a total row. Select a function that calculates totals for all monetary

 

columns. 3.000 19 Create Line sparklines in column H in the Summary sheet to display six-month

 

trends for each agent. Show the high point in each sparkline. Apply the SparkLine

 

Style Accent 1, Darker 50% style. If needed, apply the Blue, Accent 1 high point

 

marker color. 6.000 20 Insert an IF function in column I that displays a $500 bonus if an agent?s average

 

sales are greater than the average of all sales for the six months. Use two nested

 

AVERAGE functions in the logical_test argument of the IF function to make the

 

comparison. 5.000 21 Create a clustered column chart of the agents and their six-month sales, using the

 

chart type that displays the months on the category axis. Move the chart to a new

 

chart sheet named Sales Chart. 6.000 22 Apply the Layout 1 chart layout. Type January-June 2016 Sales by Agent for the

 

chart title. Apply the Style 14 chart style. 6.000 Updated: 02/26/2015 2 Current_Instruction.docx Office 2013 ? myitlab:grader ? Instructions Step Excel Project Points

 

Possible Instructions 23 Create a footer with the sheet tab code in the center, and the file name code on

 

the right side of each sheet. 4.000 24 Apply 0.2" left and right margins and scale to one page (height and width) for the

 

Data and Data (2) sheets. Select Landscape orientation for the Data (2) sheet. 4.000 25 Save the workbook. Ensure that the worksheets are named correctly and in the

 

following order: Lookup Tables, Data, Data (2), Sales Chart, and then Summary.

 

Close the workbook and exit Excel. Submit the workbook as directed. 0.000 Total Points Updated: 02/26/2015 3 100.000 Current_Instruction.docx

 







About this question:
STATUS
Answered
QUALITY
Approved
ANSWER RATING

This question was answered on: Feb 21, 2020

PRICE: $24

Solution~00065870361.zip (18.37 KB)

Buy this answer for only: $24

This attachment is locked

We have a ready expert answer for this paper which you can use for in-depth understanding, research editing or paraphrasing. You can buy it or order for a fresh, original and plagiarism-free copy (Deadline assured. Flexible pricing. TurnItIn Report provided)

Pay using PayPal (No PayPal account Required) or your credit card. All your purchases are securely protected by PayPal.
SiteLock

Need a similar solution fast, written anew from scratch? Place your own custom order

We have top-notch tutors who can help you with your essay at a reasonable cost and then you can simply use that essay as a template to build your own arguments. This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student. New solution orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

Order Now