CMPTR Excel 2016 | Chapter 15: SAM Project la In cell P9, enter a formula using the SUM function that calculates the revenue collected by Silver Screen Cinemas for the event. This formula should add together the following values: a. The total sales revenue from Standard ticket sales (G9*19) multiplied by the standard ticket allowance percentage (cell Q4). b. The total sales revenue from VIP ticket sales (K9*M9) multiplied by the VIP ticket allowance percentage (cell Q5). Use absolute references to cells 04 and Q5 in your formula. Copy the formula in cell P9 to the range P10:P17, pasting only the formulas and number formats. 7 Silver Screen Cinemas provides one house staff member for every fifteen movie attendees (represented by the total number of tickets sold). plus five additional staff members. In cell Q9, enter a formula without using a function to calculate the total number of house staff members required for the event. a . The formula should divide the total number of tickets sold (cell N9) by 15 and then add 5 to that value. b. Since you cannot have a fraction of a staff member, round the outcome of the formula to the nearest whole number by reducing the number of decimal places displayed in cell Q9 to 0. Copy the formula in cell Q9 to the range Q10:017, pasting only the formulas and number formats. Special events held at Silver Screen Cinemas require additional setup time. In cell R9, enter a formula using the IF function to automatically determine whether the event requires extra setup. a. If the Event Type (in cell E9) is equal to Special, the IF function should return the value Yes. b. If the Event Type (in cell E9) does not equal Special, the IF function should return the value No. Copy the formula in cell R9 into the range R10:R17, pasting only the formula. 9. Hide columns F and K. 10. Switch to the Ticket Orders worksheet. Change the orientation of the worksheet to Landscape. 11 As part of their member benefits, members of Silver Screen Cinemas (known as Silver Subscribers) receive a 5% discount on all tickets purchased In cell G26, enter a formula using the IF function to calculate the discount the customer receives on a ticket order if he or she is an annual supporter. a. If cell B16 is equal to Yes, the IF function should multiply the subtotal value (in cell G25) by-0.05. (Hint: Cell G26 is formatted with the Accounting number style, so negative numbers will be displayed in parentheses.) b. If cell B16 is not equal to Yes, the IF function should return a value of 0 (because the customer will receive no discount)