Excel Sheet Download 31070 | Excel Exercise8

Partial file snippet.

Sheet 1: Instructions

	
	
	
	
		Excel Skills | Exercises | Daily Sales Report
		

		www.excel-skills.com
	
	
		Instructions
		

		

	
	
		Versions: Excel 2010 & Excel 2007
		

		

	
	
		

		

		

	
	
		Our practical Excel exercises are much more than just exercises! We design our exercises in such a way that they provide the user with a mapping of the Excel features that can be used in order to complete the appropriate task in the most efficient manner possible. We also reference each step in each exercise to the appropriate tutorial that needs to be studied in order to be able to complete the step.
		
	
		

		

		

	
	
		The solutions to our comprehensive exercises are only available to customers who have purchased either a full or training membership. If you have not purchased a membership, we unfortunately cannot provide you with any of the solutions or assist you with any of the steps that are included in the appropriate exercise.
		
	
		

		

		

	
	
		Start the exercise by saving the workbook on your system, then complete step number 1 and each subsequent step in the order as listed on this sheet before comparing your workbook to the solution that we have provided.
		
	
		

		

		

	
	
		Step
		Task
		Tutorial
	
	
		1
		Open the Invoices sheet and view the data from which we will compile a monthly sales report. Note that all the data on the Invoices sheet has been included in an Excel table which will make it easier to specify the cell ranges that need to be included in our formulas.
		

	
	
		2
		We'll start by defining a named range which refers to the Sales Amount column on the Invoices sheet. This is not an absolute necessity but it is sometimes easier to work with a name instead of the structured cell referencing that is associated with Excel tables. Select all the cells (except for the column heading) that form part of the Sales Amount column in the Excel table (the cell range from cell E2 to E60).
		Enter Data / Select Cells : Selecting Cells
	
	
		3
		Insert a named range based on this cell selection and enter the following as a name for the named cell range: InvAmount
		Using Named Ranges : Define Named Ranges
	
	
		4
		Open the Name Manager and select the InvAmount named range. Note the syntax of the cell reference in the "Refers To" section. We've already defined a named range for the Invoice Date column - select the InvDate named range. Make sure that the same structured cell referencing is used for both named ranges (only the column names should differ). If the syntax differs, select the InvAmount named range again and select or enter the correct cell references. Close the Name Manager when you've checked that the named ranges have been created on the same basis.
		Using Named Ranges : Edit Named Range Cells
	
	
		5
		Open the Summary sheet, select cell A5 and enter the following date: 2012/06/01  (Note: use the appropriate date format as per the regional date settings that are specified in your System Control Panel)
		Enter Data / Select Cells : Enter Data
	
	
		6
		Select cell A6 and enter the following date: 2012/06/02  (Note: use the appropriate date format as per the regional date settings that are specified in your System Control Panel)
		Enter Data / Select Cells : Enter Data
	
	
		7
		Use the Auto Fill feature to extend the date series up to 2012/06/30 (from cell A5 to cell A34)
		Copy & Paste Data : Auto Fill
	
	
		8
		Select cell B5 and enter a SUMIFS formula to calculate the total sales amount for the date which has been included in cell A5. Note that we could have used the SUMIF function because the calculation only needs to be based on one criteria but we prefer the syntax of the SUMIFS function.
		Statistical Functions : Sum Based on Multiple Criteria
	
	
		8.1
		Enter the InvAmount named range in the sum range function argument.
		Statistical Functions : Sum Based on Multiple Criteria
	
	
		8.2
		Enter the InvDate named range in the criteria range 1 function argument.
		Statistical Functions : Sum Based on Multiple Criteria
	
	
		8.3
		Link the criteria 1 function argument to the date in cell A5.
		Statistical Functions : Sum Based on Multiple Criteria
	
	
		9
		Copy cell B5.
		Copy & Paste Data : Copy Cells
	
	
		10
		Paste cell B5 into cells B6 to B34.
		Copy & Paste Data : Paste Cells (normal)
	
	
		11
		Select cell B35 and use the Auto Sum feature to add a total for all the daily sales amounts.
		Math Functions : Auto Sum
	
	
		12
		Apply a single top border and a double bottom border to cell B17.
		Format Cells : Format Borders
	
	
		13
		Note that if we add additional invoices to the Excel table on the Invoices sheet and the invoice dates fall into the date range that is included on the Summary sheet, the invoice amounts will automatically be included in the calculations on the Summary sheet because the daily sales totals are calculated based on the entire columns which form part of the Excel table (via the named ranges that we specified and included in our SUMIFS formula).
		

	
	
		14
		Save the workbook and compare your workbook to the solution that we've provided.
		

	



Sheet 2: Invoices

	
	
	
	
	
	
	
		Invoice Number
		Invoice Date
		Customer
		Description
		 Sales Amount 
		Tax Code
		Bank Code
		Account Code
	
	
		INV0051
		6/1/2012
		DF Manufacturing
		Stock Sales
		 8,000.00 
		A
		B1
		IS-100
	
	
		INV0052
		6/1/2012
		CC Supplies
		Stock Sales
		 15,000.00 
		A
		B1
		IS-100
	
	
		INV0053
		6/4/2012
		IT Solutions
		Stock Sales
		 11,200.00 
		E
		B1
		IS-100
	
	
		INV0054
		6/4/2012
		IQ Bonds
		Stock Sales
		 10,200.00 
		A
		B1
		IS-100
	
	
		INV0055
		6/4/2012
		WC Financial Advisors
		Stock Sales
		 17,000.00 
		A
		B1
		IS-100
	
	
		INV0056
		6/5/2012
		DF Manufacturing
		Stock Sales
		 17,420.00 
		A
		B1
		IS-100
	
	
		INV0057
		6/5/2012
		GP Accountants
		Stock Sales
		 3,000.00 
		A
		B1
		IS-100
	
	
		INV0058
		6/8/2012
		Energy Incorporated
		Stock Sales
		 8,230.00 
		A
		B1
		IS-100
	
	
		INV0059
		6/8/2012
		Energy Incorporated
		Stock Sales
		 22,800.00 
		A
		B1
		IS-100
	
	
		INV0060
		6/9/2012
		CC Supplies
		Stock Sales
		 19,050.00 
		A
		B1
		IS-100
	
	
		INV0061
		6/9/2012
		DF Manufacturing
		Stock Sales
		 34,000.00 
		A
		B1
		IS-100
	
	
		INV0062
		6/9/2012
		WC Financial Advisors
		Stock Sales
		 12,540.00 
		A
		B1
		IS-100
	
	
		INV0063
		6/11/2012
		PTY Consultants
		Stock Sales
		 18,000.00 
		A
		B1
		IS-100
	
	
		INV0064
		6/12/2012
		WW Retail
		Stock Sales
		 15,200.00 
		A
		B1
		IS-100
	
	
		INV0065
		6/12/2012
		EC Estate Agents
		Stock Sales
		 14,000.00 
		A
		B1
		IS-100
	
	
		INV0066
		6/12/2012
		The Paint Shop
		Stock Sales
		 12,970.00 
		A
		B1
		IS-100
	
	
		CN00018
		6/13/2012
		IT Solutions
		Stock Sales
		 -3,100.00 
		E
		B1
		IS-100
	
	
		INV0067
		6/13/2012
		Energy Incorporated
		Stock Sales
		 28,000.00 
		A
		B1
		IS-100
	
	
		INV0068
		6/13/2012
		TRF Solutions
		Stock Sales
		 13,200.00 
		A
		B1
		IS-100
	
	
		INV0069
		6/14/2012
		EC Estate Agents
		Stock Sales
		 2,230.00 
		A
		B1
		IS-100
	
	
		INV0070
		6/14/2012
		GP Accountants
		Stock Sales
		 28,800.00 
		A
		B1
		IS-100
	
	
		INV0071
		6/15/2012
		The Paint Shop
		Stock Sales
		 25,500.00 
		A
		B1
		IS-100
	
	
		INV0072
		6/16/2012
		PTY Consultants
		Stock Sales
		 3,120.00 
		A
		B1
		IS-100
	
	
		INV0073
		6/16/2012
		CC Supplies
		Stock Sales
		 13,200.00 
		A
		B1
		IS-100
	
	
		INV0074
		6/17/2012
		XX Building Supplies
		Stock Sales
		 15,400.00 
		A
		B1
		IS-100
	
	
		INV0075
		6/19/2012
		IT Solutions
		Stock Sales
		 8,300.00 
		E
		B1
		IS-100
	
	
		INV0076
		6/19/2012
		PTY Consultants
		Stock Sales
		 14,440.00 
		A
		B1
		IS-100
	
	
		INV0077
		6/19/2012
		GP Accountants
		Stock Sales
		 3,400.00 
		A
		B1
		IS-100
	
	
		INV0078
		6/19/2012
		WC Financial Advisors
		Stock Sales
		 20,100.00 
		A
		B1
		IS-100
	
	
		INV0079
		6/19/2012
		XX Building Supplies
		Stock Sales
		 12,000.00 
		A
		B1
		IS-100
	
	
		INV0080
		6/20/2012
		Energy Incorporated
		Stock Sales
		 8,000.00 
		A
		B1
		IS-100
	
	
		INV0081
		6/20/2012
		CC Supplies
		Stock Sales
		 15,000.00 
		A
		B1
		IS-100
	
	
		INV0082
		6/21/2012
		DF Manufacturing
		Stock Sales
		 11,200.00 
		E
		B1
		IS-100
	
	
		INV0083
		6/22/2012
		WC Financial Advisors
		Stock Sales
		 10,200.00 
		A
		B1
		IS-100
	
	
		INV0084
		6/22/2012
		PTY Consultants
		Stock Sales
		 17,000.00 
		A
		B1
		IS-100
	
	
		INV0085
		6/23/2012
		WW Retail
		Stock Sales
		 17,420.00 
		A
		B1
		IS-100
	
	
		INV0086
		6/23/2012
		EC Estate Agents
		Stock Sales
		 3,000.00 
		A
		B1
		IS-100
	
	
		INV0087
		6/23/2012
		The Paint Shop
		Stock Sales
		 8,230.00 
		A
		B1
		IS-100
	
	
		INV0088
		6/25/2012
		IT Solutions
		Stock Sales
		 22,800.00 
		E
		B1
		IS-100
	
	
		INV0089
		6/25/2012
		Energy Incorporated
		Stock Sales
		 19,050.00 
		A
		B1
		IS-100
	
	
		INV0090
		6/25/2012
		TRF Solutions
		Stock Sales
		 34,000.00 
		A
		B1
		IS-100
	
	
		INV0091
		6/26/2012
		EC Estate Agents
		Stock Sales
		 12,540.00 
		A
		B1
		IS-100
	
	
		INV0092
		6/26/2012
		GP Accountants
		Stock Sales
		 18,000.00 
		A
		B1
		IS-100
	
	
		INV0093
		6/26/2012
		The Paint Shop
		Stock Sales
		 15,200.00 
		A
		B1
		IS-100
	
	
		INV0094
		6/26/2012
		PTY Consultants
		Stock Sales
		 14,000.00 
		A
		B1
		IS-100
	
	
		INV0095
		6/27/2012
		CC Supplies
		Stock Sales
		 12,970.00 
		A
		B1
		IS-100
	
	
		INV0097
		6/27/2012
		XX Building Supplies
		Stock Sales
		 28,000.00 
		A
		B1
		IS-100
	
	
		INV0098
		6/27/2012
		IT Solutions
		Stock Sales
		 13,200.00 
		E
		B1
		IS-100
	
	
		INV0099
		6/27/2012
		PTY Consultants
		Stock Sales
		 2,230.00 
		A
		B1
		IS-100
	
	
		INV0100
		6/27/2012
		GP Accountants
		Stock Sales
		 28,800.00 
		A
		B1
		IS-100
	
	
		INV0101
		6/28/2012
		WC Financial Advisors
		Stock Sales
		 25,500.00 
		A
		B1
		IS-100
	
	
		INV0102
		6/28/2012
		XX Building Supplies
		Stock Sales
		 3,120.00 
		A
		B1
		IS-100
	
	
		INV0103
		6/28/2012
		DF Manufacturing
		Stock Sales
		 13,200.00 
		A
		B1
		IS-100
	
	
		INV0104
		6/29/2012
		CC Supplies
		Stock Sales
		 15,400.00 
		A
		B1
		IS-100
	
	
		INV0105
		6/29/2012
		EC Estate Agents
		Stock Sales
		 8,300.00 
		A
		B1
		IS-100
	
	
		INV0106
		6/30/2012
		GP Accountants
		Stock Sales
		 14,440.00 
		A
		B1
		IS-100
	
	
		INV0107
		6/30/2012
		The Paint Shop
		Stock Sales
		 3,400.00 
		A
		B1
		IS-100
	
	
		INV0108
		6/30/2012
		PTY Consultants
		Stock Sales
		 20,100.00 
		A
		B1
		IS-100
	
	
		INV0109
		6/30/2012
		IT Solutions
		Stock Sales
		 12,000.00 
		E
		B1
		IS-100

The words contained in this file might help you see if this file matches what you are looking for:

...Sheet instructions excel skills exercises daily sales report wwwexcelskillscom versions amp our practical are much more than just we design in such a way that they provide the user with mapping of features can be used order to complete appropriate task most efficient manner possible also reference each step exercise tutorial needs studied able solutions comprehensive only available customers who have purchased either full or training membership if you not unfortunately cannot any assist steps included start by saving workbook on your system then number and subsequent as listed this before comparing solution provided open invoices view data from which will compile monthly note all has been an table make it easier specify cell ranges need formulas ll defining named range refers amount column is absolute necessity but sometimes work name instead structured referencing associated tables select cells except for heading form part e enter selecting insert based selection following invamount u...

Related files

Share

Help

Excel Skills \| Exercises \| Daily Sales Report		www.excel-skills.com
Instructions
Versions: Excel 2010 & Excel 2007

Our practical Excel exercises are much more than just exercises! We design our exercises in such a way that they provide the user with a mapping of the Excel features that can be used in order to complete the appropriate task in the most efficient manner possible. We also reference each step in each exercise to the appropriate tutorial that needs to be studied in order to be able to complete the step.

The solutions to our comprehensive exercises are only available to customers who have purchased either a full or training membership. If you have not purchased a membership, we unfortunately cannot provide you with any of the solutions or assist you with any of the steps that are included in the appropriate exercise.

Start the exercise by saving the workbook on your system, then complete step number 1 and each subsequent step in the order as listed on this sheet before comparing your workbook to the solution that we have provided.

Step	Task	*Tutorial*
1	Open the Invoices sheet and view the data from which we will compile a monthly sales report. Note that all the data on the Invoices sheet has been included in an Excel table which will make it easier to specify the cell ranges that need to be included in our formulas.
2	We'll start by defining a named range which refers to the Sales Amount column on the Invoices sheet. This is not an absolute necessity but it is sometimes easier to work with a name instead of the structured cell referencing that is associated with Excel tables. Select all the cells (except for the column heading) that form part of the Sales Amount column in the Excel table (the cell range from cell E2 to E60).	Enter Data / Select Cells : Selecting Cells
3	Insert a named range based on this cell selection and enter the following as a name for the named cell range: InvAmount	Using Named Ranges : Define Named Ranges
4	Open the Name Manager and select the InvAmount named range. Note the syntax of the cell reference in the "Refers To" section. We've already defined a named range for the Invoice Date column - select the InvDate named range. Make sure that the same structured cell referencing is used for both named ranges (only the column names should differ). If the syntax differs, select the InvAmount named range again and select or enter the correct cell references. Close the Name Manager when you've checked that the named ranges have been created on the same basis.	Using Named Ranges : Edit Named Range Cells
5	Open the Summary sheet, select cell A5 and enter the following date: 2012/06/01 (Note: use the appropriate date format as per the regional date settings that are specified in your System Control Panel)	Enter Data / Select Cells : Enter Data
6	Select cell A6 and enter the following date: 2012/06/02 (Note: use the appropriate date format as per the regional date settings that are specified in your System Control Panel)	Enter Data / Select Cells : Enter Data
7	Use the Auto Fill feature to extend the date series up to 2012/06/30 (from cell A5 to cell A34)	Copy & Paste Data : Auto Fill
8	Select cell B5 and enter a SUMIFS formula to calculate the total sales amount for the date which has been included in cell A5. Note that we could have used the SUMIF function because the calculation only needs to be based on one criteria but we prefer the syntax of the SUMIFS function.	Statistical Functions : Sum Based on Multiple Criteria
8.1	Enter the InvAmount named range in the sum range function argument.	Statistical Functions : Sum Based on Multiple Criteria
8.2	Enter the InvDate named range in the criteria range 1 function argument.	Statistical Functions : Sum Based on Multiple Criteria
8.3	Link the criteria 1 function argument to the date in cell A5.	Statistical Functions : Sum Based on Multiple Criteria
9	Copy cell B5.	Copy & Paste Data : Copy Cells
10	Paste cell B5 into cells B6 to B34.	Copy & Paste Data : Paste Cells (normal)
11	Select cell B35 and use the Auto Sum feature to add a total for all the daily sales amounts.	Math Functions : Auto Sum
12	Apply a single top border and a double bottom border to cell B17.	Format Cells : Format Borders
13	Note that if we add additional invoices to the Excel table on the Invoices sheet and the invoice dates fall into the date range that is included on the Summary sheet, the invoice amounts will automatically be included in the calculations on the Summary sheet because the daily sales totals are calculated based on the entire columns which form part of the Excel table (via the named ranges that we specified and included in our SUMIFS formula).
14	*Save the workbook and compare your workbook to the solution that we've provided.*

Invoice Number	Invoice Date	Customer	Description	Sales Amount	Tax Code	Bank Code	Account Code
INV0051	6/1/2012	DF Manufacturing	Stock Sales	8,000.00	A	B1	IS-100
INV0052	6/1/2012	CC Supplies	Stock Sales	15,000.00	A	B1	IS-100
INV0053	6/4/2012	IT Solutions	Stock Sales	11,200.00	E	B1	IS-100
INV0054	6/4/2012	IQ Bonds	Stock Sales	10,200.00	A	B1	IS-100
INV0055	6/4/2012	WC Financial Advisors	Stock Sales	17,000.00	A	B1	IS-100
INV0056	6/5/2012	DF Manufacturing	Stock Sales	17,420.00	A	B1	IS-100
INV0057	6/5/2012	GP Accountants	Stock Sales	3,000.00	A	B1	IS-100
INV0058	6/8/2012	Energy Incorporated	Stock Sales	8,230.00	A	B1	IS-100
INV0059	6/8/2012	Energy Incorporated	Stock Sales	22,800.00	A	B1	IS-100
INV0060	6/9/2012	CC Supplies	Stock Sales	19,050.00	A	B1	IS-100
INV0061	6/9/2012	DF Manufacturing	Stock Sales	34,000.00	A	B1	IS-100
INV0062	6/9/2012	WC Financial Advisors	Stock Sales	12,540.00	A	B1	IS-100
INV0063	6/11/2012	PTY Consultants	Stock Sales	18,000.00	A	B1	IS-100
INV0064	6/12/2012	WW Retail	Stock Sales	15,200.00	A	B1	IS-100
INV0065	6/12/2012	EC Estate Agents	Stock Sales	14,000.00	A	B1	IS-100
INV0066	6/12/2012	The Paint Shop	Stock Sales	12,970.00	A	B1	IS-100
CN00018	6/13/2012	IT Solutions	Stock Sales	-3,100.00	E	B1	IS-100
INV0067	6/13/2012	Energy Incorporated	Stock Sales	28,000.00	A	B1	IS-100
INV0068	6/13/2012	TRF Solutions	Stock Sales	13,200.00	A	B1	IS-100
INV0069	6/14/2012	EC Estate Agents	Stock Sales	2,230.00	A	B1	IS-100
INV0070	6/14/2012	GP Accountants	Stock Sales	28,800.00	A	B1	IS-100
INV0071	6/15/2012	The Paint Shop	Stock Sales	25,500.00	A	B1	IS-100
INV0072	6/16/2012	PTY Consultants	Stock Sales	3,120.00	A	B1	IS-100
INV0073	6/16/2012	CC Supplies	Stock Sales	13,200.00	A	B1	IS-100
INV0074	6/17/2012	XX Building Supplies	Stock Sales	15,400.00	A	B1	IS-100
INV0075	6/19/2012	IT Solutions	Stock Sales	8,300.00	E	B1	IS-100
INV0076	6/19/2012	PTY Consultants	Stock Sales	14,440.00	A	B1	IS-100
INV0077	6/19/2012	GP Accountants	Stock Sales	3,400.00	A	B1	IS-100
INV0078	6/19/2012	WC Financial Advisors	Stock Sales	20,100.00	A	B1	IS-100
INV0079	6/19/2012	XX Building Supplies	Stock Sales	12,000.00	A	B1	IS-100
INV0080	6/20/2012	Energy Incorporated	Stock Sales	8,000.00	A	B1	IS-100
INV0081	6/20/2012	CC Supplies	Stock Sales	15,000.00	A	B1	IS-100
INV0082	6/21/2012	DF Manufacturing	Stock Sales	11,200.00	E	B1	IS-100
INV0083	6/22/2012	WC Financial Advisors	Stock Sales	10,200.00	A	B1	IS-100
INV0084	6/22/2012	PTY Consultants	Stock Sales	17,000.00	A	B1	IS-100
INV0085	6/23/2012	WW Retail	Stock Sales	17,420.00	A	B1	IS-100
INV0086	6/23/2012	EC Estate Agents	Stock Sales	3,000.00	A	B1	IS-100
INV0087	6/23/2012	The Paint Shop	Stock Sales	8,230.00	A	B1	IS-100
INV0088	6/25/2012	IT Solutions	Stock Sales	22,800.00	E	B1	IS-100
INV0089	6/25/2012	Energy Incorporated	Stock Sales	19,050.00	A	B1	IS-100
INV0090	6/25/2012	TRF Solutions	Stock Sales	34,000.00	A	B1	IS-100
INV0091	6/26/2012	EC Estate Agents	Stock Sales	12,540.00	A	B1	IS-100
INV0092	6/26/2012	GP Accountants	Stock Sales	18,000.00	A	B1	IS-100
INV0093	6/26/2012	The Paint Shop	Stock Sales	15,200.00	A	B1	IS-100
INV0094	6/26/2012	PTY Consultants	Stock Sales	14,000.00	A	B1	IS-100
INV0095	6/27/2012	CC Supplies	Stock Sales	12,970.00	A	B1	IS-100
INV0097	6/27/2012	XX Building Supplies	Stock Sales	28,000.00	A	B1	IS-100
INV0098	6/27/2012	IT Solutions	Stock Sales	13,200.00	E	B1	IS-100
INV0099	6/27/2012	PTY Consultants	Stock Sales	2,230.00	A	B1	IS-100
INV0100	6/27/2012	GP Accountants	Stock Sales	28,800.00	A	B1	IS-100
INV0101	6/28/2012	WC Financial Advisors	Stock Sales	25,500.00	A	B1	IS-100
INV0102	6/28/2012	XX Building Supplies	Stock Sales	3,120.00	A	B1	IS-100
INV0103	6/28/2012	DF Manufacturing	Stock Sales	13,200.00	A	B1	IS-100
INV0104	6/29/2012	CC Supplies	Stock Sales	15,400.00	A	B1	IS-100
INV0105	6/29/2012	EC Estate Agents	Stock Sales	8,300.00	A	B1	IS-100
INV0106	6/30/2012	GP Accountants	Stock Sales	14,440.00	A	B1	IS-100
INV0107	6/30/2012	The Paint Shop	Stock Sales	3,400.00	A	B1	IS-100
INV0108	6/30/2012	PTY Consultants	Stock Sales	20,100.00	A	B1	IS-100
INV0109	6/30/2012	IT Solutions	Stock Sales	12,000.00	E	B1	IS-100

Related files

Share

Share to social media

Help

Login Area