Illustrated Excel 2013 Unit E: SAM Project 1b SkyeWay Café ANALYZE WORKSHEET DATA USING FORMULAS 1

Subject:MS ExcelPrice:19.87 Bought3

Share With

Illustrated Excel 2013 Unit E: SAM Project 1b

SkyeWay Café

ANALYZE WORKSHEET DATA USING FORMULAS

1. 

On the New Desserts worksheet, name the range B4:B13 Quantity_Sold, and limit the scope

of the name to the New Desserts worksheet.

2. 

In cell D4, enter a formula to determine the gross revenues by multiplying the quantity sold

in cell B4 by the price in cell C4, using the cell name Quantity_Sold in the formula. Copy the

formula in cell D4 into the range D5:D13.

3. Delete the range name Item from the workbook. (Hint: Do not delete the cell contents in

this range, only the custom range name.)

4. In cell H4, create a formula using the IF function to determine the reorder quantity based on

the following criteria:

a. A reorder quantity is assigned to items with a gross profit (in cell F4) higher than $150.

b. If the gross profit is greater than $150, the reorder quantity is calculated by multiplying

the quantity sold (in cell B4) by 1.1.

c. If the gross profit is $150 or lower, the reorder quantity is 0.

Copy the formula in cell H4 into the range H5:H13.

5. 

Select the range B4:B13. Use the Quick Analysis tool to create a formula that will total the

values in that range. The formula should use the SUM function and should appear in the cell

B14.

6. 

In cell B18, use the COUNTIF function to count the number of items with a gross profit

greater than $150. (Hint: Use F4:F13 as the range in your formula.)

7. 

In cell B19, use the AVERAGEIF function to calculate the average profit per unit for those

items with a gross profit greater than $150. (Hint: Use F4:F13 as the range and G4:G13 as

the average_range in your formula.)

8. 

In cell B21, use the SUMIF function to calculate the gross profits of items with a price

greater than $1.50. (Hint: Use the range C4:C13 as the range, F4:F13 as the Sum_range, and

do not include the trailing zero in 1.50 in your formula.)

9. 

Format cells B21 and B22 with the Currency Number format using the $ symbol and two

decimal places.

10. 

On the All Desserts worksheet, in cell D4, create a formula using the SUM function and a 3-D

reference to total cell F14 on the New Desserts worksheet and cell F12 on the Existing

Desserts worksheet.

11. 

Format cell D4 with the Accounting Number format, using the $ symbol and two decimal

places.

12. 

In cell B4, enter the text Week 1 and use Flash Fill to enter the text into the range B5:B9.

13. 

On the Loan worksheet, use the PMT function in cell B7 to determine the monthly payment

using the loan information shown in the range B4:B6. (Hint: In your formula, remember to

divide the rate value in cell B5 by 12 and use a negative value for the pv value.) Copy the

formula from B7 into the range C7:D7.

14. 

In cell B8, enter a formula to calculate the total payments by multiplying the Monthly

Payments by the Term in Months for the Expand Current Location option. Copy the formula

from B8 into the range C8:D8. (Hint: If necessary, after completing step 14, change the

number formatting of the range B8:D8 to display 0 decimal places.)

15. 

In cell B9, enter a formula that calculates the total interest by subtracting the Loan Amount

value from the Total Payments value for the Expand Current Location option. Copy the

formula in B9 to the range C9:D9.