Conditional Logic Exercises IS 201 Due: April 20, 2020 1 Conditional Logic Exercises Individual Submission Only Instructions: For each of the scenarios below: 1) Write an IF/THEN/ELSE pseudocode statement to help you specify the requirements of the conditional logic 2) Convert that statement into an Excel formula The syntax of the IF function in Excel is =IF(Logical Test, [Value if True], [Value if False]) Pseudocode consists of using regular English to describe the various parts of your program. Consider the example of going to the cinema to see Star Wars. First you would check if the movie is showing. If it is, you then you will see that movie. If not, then you will check to see if there is a Star Trek movie showing. If it is, go and see that movie. Otherwise, go home. You could write this scenario in pseudocode as follows: IF Star Wars is showing THEN Go and see Star Wars ELSE IF Star Trek is showing THEN Go and see Star Trek ELSE Go home Keep in mind the following rules when constructing your pseudocode: Each nested IF statement should be indented in your pseudocode Each IF statement should have a THEN branch and an ELSE branch which at as the (do this if TRUE and do this if FALSE, respectively) The first two scenarios give you a flowchart to help you picture the situation. Conditional Logic Exercises IS 201 Due: April 20, 2020 2 Scenario 1. Determine the Total Price. For an item that is shipped to the USA, there will be a 5% surcharge on the total price (Quantity * UnitPrice). For all other countries there will not be a surcharge. Quantity * UnitPrice Quantity * UnitPrice * 1.05 Conditional Logic Exercises IS 201 Due: April 20, 2020 3 Scenario 2. Determine the Total Price. For an item that is shipped to the USA, there will be a 5% surcharge on the total price (Quantity * UnitPrice). For items shipped to Mexico there will be a 6% surcharge. For all other countries there will not be a surcharge. Quantity * UnitPrice * 1.05 Quantity * UnitPrice Quantity * UnitPrice * 1.06 False True Is ShipCountry = USA? False Is ShipCountry = Mexico? True Conditional Logic Exercises IS 201 Due: April 20, 2020 4 Scenario 3. Calculate the new annual salary for an employee. Imagine that this formula would be placed in column D of the worksheet, as shown in the sample below. If the employee is salaried (designated by an S in column C) then the new annual salary is the current annual salary (in column B) increased by 10% If the employee is hourly (designated by an H in column C), then the new annual salary is the current annual salary decreased by 5% If the employee is neither salaried nor hourly (column C for that employee is not "S" or "H"), then the salary is unchanged. A sample of the output with the new annual salary calculated is shown in the worksheet below. Conditional Logic Exercises IS 201 Due: April 20, 2020 5 Scenario 4. Determine and calculate commission and total remuneration. For sales greater than $5,000, a 10% commission of sales is paid For sales less than or equal to $5,000, a 5% commission of sales is paid Total remuneration is equal to commission plus sales. Base your solution on the data in the worksheet below: Conditional Logic Exercises IS 201 Due: April 20, 2020 6 Scenario 5. Calculate the deduction from an employees income based on their income level. The deduction rate is 15% if the income is less than $29,701. The deduction rate is 25% if the employees income is greater than or equal to $29,701, but less than $71,950. The deduction rate is 28% if the employees income is greater than or equal to $71,950. Conditional Logic Exercises IS 201 Due: April 20, 2020 7 Scenario 6. Calculate the new amount due. The original amount due is equal to the UnitPrice * Quantity. If the discount code equals 10, subtract 10% of the original amount due from the original amount due to get the new amount due If the discount code equals 5, subtract 15% of the original amount due from the original amount due to get the new amount due If the discount code is equal to anything other than 10 or 5, then just keep the original amount due. Base your solution on the data in the worksheet below: Conditional Logic Exercises IS 201 Due: April 20, 2020 8 Scenario 7. Calculate the property tax for a property based on assessed value. Property tax is equal to the assessed value multiplied by the tax rate divided by 12 (assume that assessed value is a whole number). You will need to determine a formula to calculate the Tax Rates in column C and then use those values to calculate the Property Taxes in column D. Assessed Value Tax Rate < $60,000 8% of assessed value $60,000 - 104,999 10% of assessed value $105,000 - 245,999 13% of assessed value > = $246,000 16% of assessed value Conditional Logic Exercises IS 201 Due: April 20, 2020 9 Scenario 8. How would your Excel code change if you put the tax values into variables on the worksheet as shown below? You will need to compute a new formula for the Tax Rates in column E (columns C and D were calculated in the previous scenario). The Property Tax calculation in column F would be the same as in column D, so no need to compute a new formula for column F. Note: pseudocode is not necessary for this scenario.