1 Plan Training Session
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View 1 Plan Training Session as PDF for free.
More details
- Words: 1,467
- Pages: 5
Excel Skills | Exercises | Monthly 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 Enter Data / Select Cells : Selecting Cells 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 E31).
3
Insert a named range based on this cell selection and enter the following as a name for the named cell range: Using Named Ranges : Define Named Ranges InvAmount
4
Open the Name Manager and select the InvAmount named range. Note the syntax of the cell reference in the Using Named Ranges : Edit Named Range Cells "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.
5
Open the Summary sheet, select cell A5 and enter the following date: 2011/03/31 (Note: use the appropriate Enter Data / Select Cells : Enter Data date format as per the regional date settings that are specified in your System Control Panel)
6
Select cell A6 and enter the following date: 2011/04/30 (Note: use the appropriate date format as per the Enter Data / Select Cells : Enter Data regional date settings that are specified in your System Control Panel)
7
Use the Auto Fill feature to extend the date series up to 2012/02/29 (from cell A5 to cell A16)
Page 1 of 5
Copy & Paste Data : Auto Fill
Step Task
Tutorial
8
Select column A and change the number formatting to display the dates with a shortened month name followed Format Cells : Format Numbers & Dates by a hyphen and the full four digit year (Tip: Use the Custom number formatting option and specify a formatting type of mmm-yyyy).
9
Select cell B5 and enter a SUMIFS formula to calculate the total sales amount for the month which has been Statistical Functions : Sum Based on Multiple Criteria included in cell A5.
9.1 9.2 9.3
Enter the InvAmount named range in the sum range function argument. Statistical Functions : Sum Based on Multiple Criteria Enter the InvDate named range in the criteria range 1 function argument. Statistical Functions : Sum Based on Multiple Criteria Enter a DATE function in the criteria 1 function argument which converts the date in cell A5 to the first day of Statistical Functions : Sum Based on Multiple Criteria the calendar month. Specify criteria to include all invoices with invoices dates that are greater than or equal to the result of the DATE function in the calculation results (Tip: The mathematical operators should be included within quotation marks and there should be an ampersand between the operators and the DATE function).
9.3.1 Tip: Note that the DATE function should include a YEAR function in the year function argument, a MONTH Date Functions : Date Components & Convert Dates to Month function in the month function argument and both of these functions should refer to the date in cell A5. The day End Dates function argument in the DATE function should contain a value of 1. 9.4 9.5
Enter the InvDate named range in the criteria range 2 function argument. Statistical Functions : Sum Based on Multiple Criteria Link the criteria 2 function argument to the month end date in cell A5 and specify criteria to include all invoices Statistical Functions : Sum Based on Multiple Criteria with invoices dates that are less than or equal to the date in cell A5 in the calculation results. Note that we've entered month end dates to represent all the months in column A.
10 11 12
Copy cell B5. Paste cell B5 into cells B6 to B16. Select cell B17 and use the Auto Sum feature to add a total for all the monthly sales amounts in this cell.
13 14
Apply a single top border and a double bottom border to cell B17. Format Cells : Format Borders Note that if we add additional invoices to the Excel table on the Invoices sheet and the invoice dates fall into the months that are included on the Summary sheet, the invoice amounts will automatically be included in the calculations on the Summary sheet because the 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).
15
Save the workbook and compare your workbook to the solution that we've provided.
Page 2 of 5
Copy & Paste Data : Copy Cells Copy & Paste Data : Paste Cells (normal) Math Functions : Auto Sum
Invoice Number
Invoice Date
Customer
Description
INV0051
1/23/2011
DF Manufacturing
Consulting Services
8,000.00
A
B1
IS-100
3/1/2011
INV0052
2/18/2011
CC Supplies
Consulting Services
15,000.00
A
B1
IS-100
3/10/2011
INV0053
3/14/2011
IT Solutions
Consulting Services
11,200.00
E
B1
IS-100
4/13/2011
INV0054
3/19/2011
IQ Bonds
Consulting Services
10,200.00
A
B1
IS-100
4/10/2011
INV0055
4/9/2011
WC Financial Advisors
Consulting Services
17,000.00
A
B1
IS-100
5/9/2011
INV0056
4/22/2011
DF Manufacturing
Consulting Services
17,420.00
A
B1
IS-100
5/9/2011
INV0057
5/5/2011
GP Accountants
Accounting Services
3,000.00
A
B1
IS-100
5/10/2011
INV0058
5/7/2011
Energy Incorporated
Taxation Services
8,230.00
A
B1
IS-100
6/6/2011
INV0059
5/10/2011
Energy Incorporated
Consulting Services
22,800.00
A
B1
IS-100
6/9/2011
INV0060
5/31/2011
CC Supplies
Consulting Services
19,050.00
A
B1
IS-100
6/30/2011
INV0061
6/26/2011
DF Manufacturing
Consulting Services
34,000.00
A
B1
IS-100
7/26/2011
INV0062
6/29/2011
WC Financial Advisors
Consulting Services
12,540.00
A
B1
IS-100
7/16/2011
INV0063
7/11/2011
PTY Consultants
Consulting Services
18,000.00
A
B1
IS-100
7/16/2011
INV0064
7/22/2011
WW Retail
Consulting Services
15,200.00
A
B1
IS-100
8/21/2011
INV0065
8/13/2011
EC Estate Agents
Consulting Services
14,000.00
A
B1
IS-100
9/12/2011
INV0066
8/17/2011
The Paint Shop
Consulting Services
12,970.00
A
B1
IS-100
9/16/2011
CN00018
9/5/2011
IT Solutions
Discount
-3,100.00
E
B1
IS-100
9/27/2011
INV0067
9/12/2011
Energy Incorporated
Consulting Services
28,000.00
A
B1
IS-100
9/30/2011
INV0068
9/17/2011
TRF Solutions
Consulting Services
13,200.00
A
B1
IS-100
10/4/2011
INV0069
10/8/2011
EC Estate Agents
Taxation Services
2,230.00
A
B1
IS-100
10/13/2011
INV0070
10/8/2011
GP Accountants
Consulting Services
28,800.00
A
B1
IS-100
10/13/2011
INV0071
10/31/2011
The Paint Shop
Consulting Services
25,500.00
A
B1
IS-100
11/30/2011
INV0072
11/3/2011
PTY Consultants
Consulting Services
3,120.00
A
B1
IS-100
12/3/2011
INV0073
11/29/2011
CC Supplies
Consulting Services
13,200.00
A
B1
IS-100
12/21/2011
INV0074
11/29/2011
XX Building Supplies
Consulting Services
15,400.00
A
B1
IS-100
12/29/2011
INV0075
12/25/2011
IT Solutions
Consulting Services
8,300.00
E
B1
IS-100
1/11/2012
INV0076
1/19/2012
PTY Consultants
Accounting Services
14,440.00
A
B1
IS-100
1/31/2012
INV0077
1/20/2012
GP Accountants
Taxation Services
3,400.00
A
B1
IS-100
2/28/2012
Sales Amount
Tax Code Bank Code
Account Code Payment Date
Invoice Number
Invoice Date
Customer
Description
INV0078
2/15/2012
WC Financial Advisors
Consulting Services
20,100.00
A
B1
IS-100
INV0079
2/29/2012
XX Building Supplies
Consulting Services
12,000.00
A
B1
IS-100
Sales Amount
Tax Code Bank Code
Account Code Payment Date 2/28/2012
Example Limited Monthly Sales Report Month
Total Sales
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 Enter Data / Select Cells : Selecting Cells 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 E31).
3
Insert a named range based on this cell selection and enter the following as a name for the named cell range: Using Named Ranges : Define Named Ranges InvAmount
4
Open the Name Manager and select the InvAmount named range. Note the syntax of the cell reference in the Using Named Ranges : Edit Named Range Cells "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.
5
Open the Summary sheet, select cell A5 and enter the following date: 2011/03/31 (Note: use the appropriate Enter Data / Select Cells : Enter Data date format as per the regional date settings that are specified in your System Control Panel)
6
Select cell A6 and enter the following date: 2011/04/30 (Note: use the appropriate date format as per the Enter Data / Select Cells : Enter Data regional date settings that are specified in your System Control Panel)
7
Use the Auto Fill feature to extend the date series up to 2012/02/29 (from cell A5 to cell A16)
Page 1 of 5
Copy & Paste Data : Auto Fill
Step Task
Tutorial
8
Select column A and change the number formatting to display the dates with a shortened month name followed Format Cells : Format Numbers & Dates by a hyphen and the full four digit year (Tip: Use the Custom number formatting option and specify a formatting type of mmm-yyyy).
9
Select cell B5 and enter a SUMIFS formula to calculate the total sales amount for the month which has been Statistical Functions : Sum Based on Multiple Criteria included in cell A5.
9.1 9.2 9.3
Enter the InvAmount named range in the sum range function argument. Statistical Functions : Sum Based on Multiple Criteria Enter the InvDate named range in the criteria range 1 function argument. Statistical Functions : Sum Based on Multiple Criteria Enter a DATE function in the criteria 1 function argument which converts the date in cell A5 to the first day of Statistical Functions : Sum Based on Multiple Criteria the calendar month. Specify criteria to include all invoices with invoices dates that are greater than or equal to the result of the DATE function in the calculation results (Tip: The mathematical operators should be included within quotation marks and there should be an ampersand between the operators and the DATE function).
9.3.1 Tip: Note that the DATE function should include a YEAR function in the year function argument, a MONTH Date Functions : Date Components & Convert Dates to Month function in the month function argument and both of these functions should refer to the date in cell A5. The day End Dates function argument in the DATE function should contain a value of 1. 9.4 9.5
Enter the InvDate named range in the criteria range 2 function argument. Statistical Functions : Sum Based on Multiple Criteria Link the criteria 2 function argument to the month end date in cell A5 and specify criteria to include all invoices Statistical Functions : Sum Based on Multiple Criteria with invoices dates that are less than or equal to the date in cell A5 in the calculation results. Note that we've entered month end dates to represent all the months in column A.
10 11 12
Copy cell B5. Paste cell B5 into cells B6 to B16. Select cell B17 and use the Auto Sum feature to add a total for all the monthly sales amounts in this cell.
13 14
Apply a single top border and a double bottom border to cell B17. Format Cells : Format Borders Note that if we add additional invoices to the Excel table on the Invoices sheet and the invoice dates fall into the months that are included on the Summary sheet, the invoice amounts will automatically be included in the calculations on the Summary sheet because the 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).
15
Save the workbook and compare your workbook to the solution that we've provided.
Page 2 of 5
Copy & Paste Data : Copy Cells Copy & Paste Data : Paste Cells (normal) Math Functions : Auto Sum
Invoice Number
Invoice Date
Customer
Description
INV0051
1/23/2011
DF Manufacturing
Consulting Services
8,000.00
A
B1
IS-100
3/1/2011
INV0052
2/18/2011
CC Supplies
Consulting Services
15,000.00
A
B1
IS-100
3/10/2011
INV0053
3/14/2011
IT Solutions
Consulting Services
11,200.00
E
B1
IS-100
4/13/2011
INV0054
3/19/2011
IQ Bonds
Consulting Services
10,200.00
A
B1
IS-100
4/10/2011
INV0055
4/9/2011
WC Financial Advisors
Consulting Services
17,000.00
A
B1
IS-100
5/9/2011
INV0056
4/22/2011
DF Manufacturing
Consulting Services
17,420.00
A
B1
IS-100
5/9/2011
INV0057
5/5/2011
GP Accountants
Accounting Services
3,000.00
A
B1
IS-100
5/10/2011
INV0058
5/7/2011
Energy Incorporated
Taxation Services
8,230.00
A
B1
IS-100
6/6/2011
INV0059
5/10/2011
Energy Incorporated
Consulting Services
22,800.00
A
B1
IS-100
6/9/2011
INV0060
5/31/2011
CC Supplies
Consulting Services
19,050.00
A
B1
IS-100
6/30/2011
INV0061
6/26/2011
DF Manufacturing
Consulting Services
34,000.00
A
B1
IS-100
7/26/2011
INV0062
6/29/2011
WC Financial Advisors
Consulting Services
12,540.00
A
B1
IS-100
7/16/2011
INV0063
7/11/2011
PTY Consultants
Consulting Services
18,000.00
A
B1
IS-100
7/16/2011
INV0064
7/22/2011
WW Retail
Consulting Services
15,200.00
A
B1
IS-100
8/21/2011
INV0065
8/13/2011
EC Estate Agents
Consulting Services
14,000.00
A
B1
IS-100
9/12/2011
INV0066
8/17/2011
The Paint Shop
Consulting Services
12,970.00
A
B1
IS-100
9/16/2011
CN00018
9/5/2011
IT Solutions
Discount
-3,100.00
E
B1
IS-100
9/27/2011
INV0067
9/12/2011
Energy Incorporated
Consulting Services
28,000.00
A
B1
IS-100
9/30/2011
INV0068
9/17/2011
TRF Solutions
Consulting Services
13,200.00
A
B1
IS-100
10/4/2011
INV0069
10/8/2011
EC Estate Agents
Taxation Services
2,230.00
A
B1
IS-100
10/13/2011
INV0070
10/8/2011
GP Accountants
Consulting Services
28,800.00
A
B1
IS-100
10/13/2011
INV0071
10/31/2011
The Paint Shop
Consulting Services
25,500.00
A
B1
IS-100
11/30/2011
INV0072
11/3/2011
PTY Consultants
Consulting Services
3,120.00
A
B1
IS-100
12/3/2011
INV0073
11/29/2011
CC Supplies
Consulting Services
13,200.00
A
B1
IS-100
12/21/2011
INV0074
11/29/2011
XX Building Supplies
Consulting Services
15,400.00
A
B1
IS-100
12/29/2011
INV0075
12/25/2011
IT Solutions
Consulting Services
8,300.00
E
B1
IS-100
1/11/2012
INV0076
1/19/2012
PTY Consultants
Accounting Services
14,440.00
A
B1
IS-100
1/31/2012
INV0077
1/20/2012
GP Accountants
Taxation Services
3,400.00
A
B1
IS-100
2/28/2012
Sales Amount
Tax Code Bank Code
Account Code Payment Date
Invoice Number
Invoice Date
Customer
Description
INV0078
2/15/2012
WC Financial Advisors
Consulting Services
20,100.00
A
B1
IS-100
INV0079
2/29/2012
XX Building Supplies
Consulting Services
12,000.00
A
B1
IS-100
Sales Amount
Tax Code Bank Code
Account Code Payment Date 2/28/2012
Example Limited Monthly Sales Report Month
Total Sales
Related Documents
1 Plan Training Session
August 2019 14
Training Plan
November 2019 18
Blank Training Session
April 2020 4
Session Plan Final
May 2020 6
Session-plan-masonry-sample.pdf
November 2019 8
Session Plan Focp
November 2019 8More Documents from ""
1_plan_training_session.docx
August 2019 13
1 Plan Training Session
August 2019 14
Doc2
August 2019 33
Sharepoint 2007 - Records Management Guide
December 2019 35
Material De Apoyo Bombeo Electrosumergible V2.pdf
April 2020 24