AC350 Accounting Information Systems

Start Excel. Download and open the file named Excel_Ch03_SpaUsage.xlsx. Grader has automatically added your last name to the beginning of the file name. Save the file to a location where you are storing your files.

On the Monday Sales worksheet, in cell K2, enter a function to insert todays date.

In cell I2, enter a conditional math function to total the sales based on the criteria in cell H2. Use absolute referencing where necessary. Copy the function through cell range I2:I15.

In cell K5, enter a function to count the total number of services per day in column D.

In cell I18, enter a conditional math function to total the sales based on the criteria of service and therapist. This function will need the use of both absolute and mixed references. Copy the function through cell range I18:K31.

In cell L18, total the sales by service, and then copy the function through cell range L18:L31.

In cell M18, enter a logical function to determine if the service is a popular service. If the Total Sales is greater than or equal to 200, then return a result of Yes, or else return a result of No. Copy the function through cell range M18:M31.

On the ServicesPricing worksheet, create a named range SpaPrices for cell range A3:C16.

On the ServicesPricing worksheet, in cell G3, enter an array formula to multiply the cost of each service by the service hours. Format cell range G3:J16 with the Comma cell style.

On the CustomerPricing worksheet, in cell B2, enter a VLOOKUP function to determine the prices for each service. Use the named range SpaPrices and look for an exact match.

As a service to spa members, the spa allows payment plans. On the PaymentPlan worksheet, in cell B6, calculate the monthly payment. Change the monthly payment in cell B6 to an absolute value.

Save the workbook, exit Excel, and submit your file as directed.