"

Chapter 2 – Aggregates and Rounding

Introduction

In Chapter 2 we will examine how to perform a variety of calculations to derive aggregate data, which means we want to compile a series of numbers as sums or averages, for example.

  • Use basic mathematical formulas to calculate percentages
  • Use autofill to easily copy formulas and data from one cell to many others in a series
  • Use the aggregate functions AVERAGE, MIN, MAX, and SUM
  • Discuss and use ROUND and how that compares to various formatting options (e.g., Accounting format)

Guided Exercises

In Canvas, download the Chapter02_AggregatesAndRounding file. Notice that AutoSave may be on.



A. Target Stores


Percentages_Target Worksheet


Use formulas and cell references to complete each problem. Next year, you might have an apartment and you will need some items to furnish it. You go to Target and purchase the items listed in A6:A10.


    

  1. In cell D6, enter a formula to find the cost of purchasing the item in cell A6. Fill your formula down to cell D10.
    2. 

  2. In cell D11, use a function to find the Subtotal before any discounts or tax.
    3. 

  3. You have a Target RedCard which means you save 5% on every purchase. In cell D12, calculate how much you will save to the nearest penny.
    4. 

  4. In cell D13, find the new amount that you owe.
    5. 

  5. In cell D14, calculate how much you will owe in Sales Tax using the tax rate given in cell H2. Round to the nearest penny.
    6. 

  6. In cell D15, find how much you will owe.
    7. 

  7. The data in cells A20:B28 is the quarter 1 revenue from various departments of the Bloomington Target store. In cell C20, use a formula to calculate each department’s percent of the total revenue from quarter 1. Round to 3 decimal places and format as a percent with 1 decimal place displayed.
    
You may use a SUM at the bottom of column B for your denominator.
    8. 



B. Kittle’s Markups and Commissions


Percentages_Kittles Worksheet


The data in cells A6:B8 represents three items that Kittle’s sells and the wholesale cost that Kittle’s pays the supplier for those items.


    

  1. Kittle’s marks up their cost by the Markup% indicated in cell H2. In cell C6, use a formula to calculate what the customer cost of the Hadley Sofa will be. Fill the formula down to cells C7 and C8.
    2. 

  2. Kittle’s pays the salespeople a commission which is a percentage of how much they sell to customers. The current commission rate is in cell H3. In cell D6, use a formula to calculate how much money a salesperson would make from the sale of a Hadley Sofa. Round to the nearest penny. Fill the formula down to cells D7 and D8.
    3. 



C. University Students and Costs


UniversityNumbers Worksheet


This worksheet gives four pieces of data from some public universities in the United States.


    

  1. In cell H3, use a function to find the total number of full-time students.
    2. 

  2. In cell H4, use a function to find the average number of full-time students enrolled in a public university. Round this value to the nearest whole number.
    3. 

  3. In cell H5, use a function to find what the highest enrollment of full-time students was.
    4. 

  4. In cell H6, use a function to find what the lowest enrollment of full-time students was.
    5. 

  5. Format the out-of-state tuition and room & board costs as Accounting with 0 decimal places displayed.
    6. 

  6. In cell K4, use a function to find the average room & board costs from these public universities. Round this value to 2 decimal places and display 2 decimal places.
    7. 

  7. In cell K5, use a function to find the highest room & board costs.
    8. 

  8. In cell K6, use a function to find the lowest room & board costs.
    9. 

  9. If the values in K4:K6 did not automatically format as Accounting, format them as Accounting.
    10. 



If you are going to stop working here, make sure your file is saved. When you close it, if there is any unsaved work, Excel will prompt you to save. Make sure the most recently worked file is in your IU OneDrive.




More Practice






What you should work on before our next class






In your IU OneDrive, open the Chapter02_AggregatesAndRounding file.


You have a job interview, but you don’t have a suit to wear! Luckily, you have a 20% off coupon at Kohl’s this week. Use the NewSuit worksheet to figure out what the total spent is going to be.


    

  1. In cell D5, enter a formula to find the cost of purchasing the item in cell A5. Fill your formula down to cell D10.
    2. 

  2. In cell D11, use a function to find the Subtotal before any discounts or tax.
    3. 

  3. The discount percent is in cell G2. In cell D12, calculate how much you will save to the nearest penny. Make sure you reference cell G2 just in case the percent changes.
    4. 

  4. In cell D13, find the new amount that you owe.
    5. 

  5. In cell D14, calculate how much you will owe in Sales Tax using the tax rate given in cell G3. Round to the nearest penny.
    6. 

  6. In cell D15, find how much you will owe.
    7. 



Remember the student survey that you were asked to complete before classes started (or during the first week of classes)? Some of the data is on the StudentData worksheet. Use your Excel skills to perform the following tasks:


    

  1. Center the text in cell A1 across A1:D1. Change the font to 16 pt with a green fill color.
    2. 

  2. In the gray filled cells in column F, use functions to answer the questions in column G. For problems that ask for an average, round the answers to 1 decimal place.
    3. 










					
		
					

						

							

								
License

								
K160: Introduction to Modeling and Solving Business Problems 1e Copyright © 2024 by Trustees of Indiana University. All Rights Reserved.
							

														

								
Share This Book