This assignment is designed to gain additional proficiency in the use of MS Excel functions and formulas. For this assignment, you will upload your Excel file to Sakai so that it can be accessed by just clicking on the file's link.
This spreadsheet shows data related to the loans to purchase houses in different states. Complete the missing information (blue cells) with the following data. Note: the formulas you utilize should be able to be copied into any of the blue cells within the column and produce the correct result.
a) Except where noted otherwise, format all your monetary results to Accounting Format, including the $ sign, commas and no decimal places.
b) Incentive: A dollar amount which is based on a percentage discount of the house price as indicated on the Data section of the spreadsheet. It's given only to selected customers complying with any of these conditions:
TIP: Consider the use of the AND, OR functions as well as nested IF statements - see References section - when working on this task.
c) Down Payment: A dollar amount calculated as follows:
d) Amount Financed: The difference between the house price and the sum of Incentive plus the Down Payment.
e) Taxes: Calculated in dollars based on the tax rate applied to the house price.
f) Duration (years): Based on the Additional Data table provided.
g) APR (Annual Percentage Rate): Based on the Additional Data table provided. Format as a Percentage with 1 decimal place.
h) Quarterly payment: Calculated using an appropriate payment function. Format using the Currency format, $ sign, commas, 2 decimal places and negative numbers as a red font and parenthesis.
Complete the missing information (yellow cells) by utilizing the proper function to generate a quarterly subtotal and final total. Utilize the formatting as determined by the column.
This spreadsheet lists a schedule of departures and arrivals for some international airports. Complete the missing information (blue cells) with appropriate functions and/or formulas. Notice that in the schedule there is only one flight per airport. Therefore, you may use that fact to select the appropriate function when the ticket price is needed for each passenger. The ticket price may be considered - for performing calculations - as based on the departure airport.
a) Except where noted otherwise, format all your monetary results to Accounting Format, including the $ sign, commas and 2 decimal places.
b) Baggage fees: A dollar amount which is based on the data provided in the Baggage Fees section of the spreadsheet. The percentages indicated are applied to the Ticket Prices indicated on the Departure-Arrival table.
TIP: Consider the use of a nested IF statement when working on this task.
c) Class Fees: A dollar amount calculated based on the Class Fees table. The percentages listed are applied to the Ticket Prices indicated on the Departure Arrival table.
d) Total Fees: Baggage Fees plus Class Fees.
e) Total Price: The initial Ticket Price plus the Total Fees for each passenger.
f) Travel Time: The number of hours traveled by each passenger based on the departure times and arrival times. Format as a Number with 2 decimal places.
Apply conditional formatting as indicated below.
1. Apply yellow fill, bold fonts to the names of the Studios for the movies that list: Weekend Gross greater than $500,000 and Total Gross greater than $10,000,000.
2. Apply green fill, bold fonts to the titles of the movies that show: Total Gross greater than $15,000,000 and Theater Count greater than 20.
3. Apply Data Bars - Green Gradient fill to the Total Gross column cells.
TIP: For items 1 and 2, use the New Rule option of Conditional Formatting with an appropriate function to comply with the conditions indicated.