jagomart
digital resources
picture1_Microsoft Excel Tutorial Pdf 46454 | Excel Exercise4


 231x       Filetype XLSX       File size 0.22 MB       Source: www.excel-skills.com


File: Microsoft Excel Tutorial Pdf 46454 | Excel Exercise4
sheet 1 instructions excel skills exercises entering formulas amp linking cells wwwexcelskillscom instructions versions excel 2010 amp excel 2007 our practical excel exercises are much more than just exercises we ...

icon picture XLSX Filetype Excel XLSX | Posted on 18 Aug 2022 | 3 years ago
Partial file snippet.
Sheet 1: Instructions
Excel Skills | Exercises | Entering Formulas & Linking Cells
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 GP sheet and enter a formula in cell D4 which deducts the cost of sales amount in cell C4 from the sales amount in cell B4. Enter Data / Select Cells : Enter Formulas
2 Copy cell D4. Copy & Paste Data : Copy Cells
3 Paste only the cell formula into cells D5 to D12. Copy & Paste Data : Paste Formulas
4 Select cell E4 and enter a formula in this cell which divides the gross profit amount in cell D4 by the sales amount in cell B4 in order to calculate a monthly gross profit percentage. Enter Data / Select Cells : Enter Formulas
5 Copy cell E4. Copy & Paste Data : Copy Cells
6 Paste only the cell formula into cells E5 to E12. Copy & Paste Data : Paste Formulas
7 Select the cell range from cell A3 to cell B11 and copy this cell range. Copy & Paste Data : Copy Cells
8 Open the Comm sheet, select cell A3 and paste the selected cell range so that the orientation of the copied cells changes from row based to column based. The month names will therefore now form the column headings. When selecting the appropriate paste options, make sure that you only paste the cell values. Copy & Paste Data : Paste Text Orientation
9 Select cell B9 and enter a formula which will enable you to calculate a monthly commission amount based on the two commission percentages that have been entered in cells B6 and B7. Note that we intend to copy the formula to the formatted cells in both row 9 and 10 (Tip: An absolute row reference therefore needs to be used for the sales cell and an absolute column reference needs to be used for the commission percentage cell). Formulas & Links : Relative & Absolute References
10 Copy cell B9. Copy & Paste Data : Copy Cells
11 Paste all the contents of cell B9 into the cell range from cell B9 to cell I10. Copy & Paste Data : Paste Cells (normal)
12 Open the Sales sheet and link cell B4 on the Sales sheet to cell B4 on the GP sheet. Formulas & Links : Create a Link
13 Copy cell B4 on the Sales sheet. Copy & Paste Data : Copy Cells
14 Paste only the cell formula into cell B5 to B11. Copy & Paste Data : Paste Formulas
15 Select cell C4 and enter a formula which can be used to calculate a cumulative sales total for all the cells in column C (Tip: You will have to use the SUM function, use the same cell reference as both the from and to referral points for the sum cell range and change the first cell reference to an absolute cell reference). Formulas & Links : Relative & Absolute References
16 Copy cell C4. Copy & Paste Data : Copy Cells
17 Paste only the cell formula into cell C5 to C11. Copy & Paste Data : Paste Formulas
18 Open the Tax sheet and select the cell range from cell A5 to cell D14. Enter Data / Select Cells : Select Cells
19 Insert the selected cell range into an Excel table. Excel Tables : Insert Table
20 Select cell B6 and link the cell to cell B4 on the GP sheet. Note how the formulas in the other cells in column B are automatically copied when the cell range is included in an Excel table. Formulas & Links : Create a Link
21 Select cell C6 and enter a formula which multiplies the appropriate monthly sales amount by the sales tax percentage that has been specified in cell B3. Note that you will have to use an absolute cell reference to refer to the sales tax percentage otherwise the other sales tax amounts in column C will not be calculated correctly. Enter Data / Select Cells : Enter Formulas
22 Enter a formula in cell D6 which sums the sales and tax amounts for each row that is included in the table. Enter Data / Select Cells : Enter Formulas
23 Note the formula in cell D6 - this is an example of structured cell referencing which is a unique cell referencing system to Excel tables. Change the default Table1 name to: TaxTable Excel Tables : About Excel Tables
24 Note how the formulas in the cells that are included in the Excel table are automatically updated with the new table name. Now add a total row to the table. Excel Tables : Insert Total Row
25 Select cell B15 and add a SUM calculation to this column total. Do the same in cell C15. Excel Tables : Insert Total Row
26 Open the GP sheet and note that the formula that has been included in column E displays an error if the sales amount in column B is nil. Edit the formula in cell E4 by adding an IF function to it in order to display a nil value if the sales amount in column B is nil. Logical Functions : IF Function
27 Copy cell E4. Copy & Paste Data : Copy Cells
28 Paste only the cell formula into cells E5 to E12. Cell E12 should now contain a nil value. Copy & Paste Data : Paste Formulas
29 Save the workbook and compare your workbook to the solution that we've provided.

Sheet 2: GP
Example Limited








Month Sales Cost of Sales Gross Profit GP%
Aug-2011 100,000 65,000

Sep-2011 85,000 52,000

Oct-2011 90,000 53,000

Nov-2011 115,000 59,000

Dec-2011 107,000 61,000

Jan-2012 101,000 62,000

Feb-2012 105,000 58,000

Mar-2012 112,000 63,000

Apr-2012 - -


Sheet 3: Comm
Example Limited











































Commission 1 % 10.0%






Commission 2 % 7.5%















Commission 1 Amount







Commission 2 Amount








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

...Sheet instructions excel skills exercises entering formulas amp linking cells wwwexcelskillscom versions 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 gp enter formula cell d which deducts cost sales amount c from b data select copy paste into e divides gross profit calculate monthly percentage range comm selected so orientation copied changes row based column month names will therefore now form headings when selecting options make sure values text enable commission two percentages been entered note inte...

no reviews yet
Please Login to review.