WEEK 1 EXCEL HANDOUT Exercise 1: Create the spreadsheet below as follows: 1. Enter the spreadsheet title, row titles and column titles and data as shown. 2. To get column title in two lines as shown, click Alt, Enter at the end of the word ‘Hours’ and type the rest.
3. Bold and centre the column headings 4. Adjust the column widths 5. Merge and centre the main heading across A1 to F1 and make it bold and 14pt, red 6. Format all cells between C6 and F13 to be currency with the decimal of 2 (Format Cells) 7. Calculate the Gross column by multiplying the Hours worked with the Hourly rate (=B6*C6) 8. Calculate the Tax column by multiplying the Gross amount with 24% using absolute cell reference (=D6*$B$3) 9. Calculate the Net column by subtracting the Tax amount from the gross amount 10. Type the word TOTAL in A13 11. Calculate the totals in row 13 using the Autosum function 12. Put a single top border and double bottom border for row 13. 13. Save the spreadsheet and name it "HDP" 14. Check the print preview. 15. In Set-up, change to landscape, vertically and horizontally centre the worksheet 16. Create a custom footer and put your file name on the left of the footer, the date in the centre, and your name on the right.
1
Exercise 2: Sales Forecast
Create the above spreadsheet as follows: 1.
Use Autofill to enter the months. Enter the data above as shown.
2.
Adjust the column widths, merge and centre the main heading.
3.
Change the font of the heading to 14pt, bold
4.
Add shading of your choice
5.
Bold and centre row headings (Adjust column width if necessary)
6.
Format all cells between C7 to E14 to be currency with 2 decimal places (Format Cells)
7.
Use formulas to calculate the following: (remember to use cell references and absolute address where necessary)
Cost Price = Quantity x Price
Profit = Cost Price x Profit %
Selling Price = (Cost Price + Profit)/Quantity
8.
Add a row title “Total” in A14 and use Autosum to calculate the totals in row 14. Place a border on the top and bottom of the row, and bold.
9.
Save the spreadsheet and call it "Sales Forecast"
10. Check the print preview. 11. In Set-up, change to landscape, vertically and horizontally centre the worksheet 12.
Create a custom footer and put your file name on the left of the footer, the date in the centre, and your name on the right.
13. Save and print your completed worksheet and a copy of your formulae and show your tutor
2
Exercise 3: Create the spreadsheet shown below as follows: 1. Enter data as shown. 2. Fill the months January to June using Auto fill. 3. Calculate the totals for the months using the AutoSum Function. 4. In the row headed Total after discount, write a formula to calculate the Total after Discount for each month, i.e. =Total – (Total x Discount). You need to use absolute reference 5. Format the figures to currency, and no cents. 6. Right align the months. 7. Merge and centre the Title and Subtitle across the columns 8. Bold the main title and change the font to 12. Bold and italicise the subtitle. 9. Bold all the row headings and column headings. 10.Put a border around the TOTAL and DISCOUNTED TOTAL figures as shown. 11.In File Page Set-up vertically and horizontally centre the worksheet, and put your file name on the left of the header, the date in the centre, and your name on the right. 12.Check the print preview. 13.Print your worksheet and show your tutor.
3
Exercise 4: Payroll Enter the following data in a new work book.
1. Calculate the following values for each Sales Representative a) Sales revenue ie Quantity sold x Price (use absolute addressing) b) Commission ie 60% of Sales Revenue - use absolute addressing c) Tax ie 23% of Commission - use absolute addressing d) Pay ie Commission less tax 2. Calculate the Totals for each Column 3. Format all dollar amounts to currency with 2 decimal places. 4. Format numbers in column A to text and centre align. 5. Centre title across columns, bold and increase font size to 20 pt. 6. Centre and bold column headings, use word wrap. 7. Increase the height of row 3 to 40 pts and height of row 2 to 26 pts 8. Align the text horizontally and vertically centred as shown. 9. Place Borders as shown. To change the thickness of borders, go to Format/cells/Borders 10. Horizontally and vertically centre your spreadsheet 11. Save workbook as PAYROLL, print with filename, your name and date in the footer, and show gridlines.
4
Exercise 5: Señorita Salsa Company Monty Zuma owns the Señorita Salsa Company, a successful business producing Mexican style salsas, whose main customers are restaurants and gourmet food stores. He is working on adding a new product, Thai Five-Alarm Red Hot, to Señorita’s gourmet salsa line. A bank loan is required to finance the additional food-processing equipment to handle the requirements of the increased salsa production. Monty has an appointment with his bank manager tomorrow and asks you to prepare a professional looking worksheet showing projected sales of the new salsa and the expected effect on profits. 1. Open a new blank workbook 2. Enter the text and numbers as shown below:
3. Insert a column between ‘Product’ and ‘Unit Cost’, name it ‘Heat Rating’ and add the star symbols (look in Wingdings) Product Black Bean and Fruit Thai Five-Alarm Red Hot Garden Explosion Spicy Nut with Blue Cheese Watermelon Fire & Ice
Heat Rating
4. Calculate the following columns, ‘Profit per Unit’, ‘Total Sales’ and ‘Total Profit’ 5. Type the word Total: in E11, align right and make Bold 6. Calculate totals in columns F, G and H 7. Complete column I ‘% of Total Sales’. Format to percent style with one decimal place. 8. Change the order of the products as follows Product Black Bean and Fruit Garden Explosion Spicy Nut with Blue Cheese Watermelon Fire & Ice Thai Five-Alarm Red Hot
Heat Rating
9. Merge and centre the main heading across A1 to I1. Make it bold, 18pt and Jokerman font. 10. Merge and centre the heading in row 2 across A1 to I1. Make it bold, 12pt and Tahoma font 11. Delete row 3 12. Bold and centre the column headings. Add a medium width top and bottom border and change background colour of cells to grey. 5
13. Add a medium width top border and a double bottom border to row 10 14. Format columns C, D and E to 2 decimal places. Format columns F, G and H to zero decimal places and with a thousand separator (e.g. 14,000) 15. Format C5 to accounting style. Use Format Painter to apply accounting style to D6 and E6. 16. Apply accounting style (with no decimal places) to cells G6 and H6. 17. Add currency style to row 10 18. Insert a new row 8 and add the following information: Product: Mango Tequila Unit Selling Price: 20.10
Heat Rating: 3 stars Units Sold: 16,500
Unit Cost: 16.35
19. Italicise product names in column A and indent 20. Apply a yellow background to the Total Profit column from rows 5 to 11 21. Add a text box below the report from A13 to C16 and add the following text. Notice the high profit margin of the Thai Five-Alarm Red Hot. It has the second highest profit margin per unit. Make sure you italicise the name of the product. Add a shadow to the text box. Draw an arrow from the text box to the ‘Profit per Unit’ for the Thai Five-Alarm Red Hot product. 22. Rename the sheet tab to Sales Forecast 23. Click in the Sheet 2 tab and rename to Documentation. Move the Documentation sheet so that it is before the Sales Forecast worksheet.
6
24. Change the colour of the worksheet to a colour of your choice. Using WordArt and the image file ‘salsa.jpg’ create a logo for the company anywhere between A1:H14 (see example below). Your logo does not have to look exactly like this one – be creative.
25. Read pp xxxix to xli (refer to notes.pdf file) on Spreadsheet Documentation. Open the file scrip.xls for another example of a file that contains a Documentation sheet. You should note that there is no one single way to document a workbook. The required amount of documentation varies depending on the use and complexity of the workbook. Documentation can be as basic as who created the worksheet and the date it was created, or it could be more detailed including instructions on how to use the worksheets, where to enter data, how to save the workbook and how to print reports. Every well-designed workbook should contain a Documentation sheet. 26. Type ‘Created:’ in cell A17, ‘Created by:’ in A19, ‘Modified:’ in A21, ‘Modified by:’ in A23 and ‘Purpose:’ in A25. Put today’s date in C17 and your name in C19. 27. Type the following text in rows 25, 26, 27 and 28 starting in column C. Display nicely formatted and well-designed worksheet showing projected sales of new product, Thai Five-Alarm Red Hot and the expected effect on profits; to be presented to bank manager in applying for a new loan. 28. Create a custom footer and put the file name on the left of the footer, the date in the centre and your name on the right. Change the Sales Forecast worksheet to Landscape orientation. Vertically and horizontally centre both worksheets. 29. Save your file as Salsa 30. Close the file
Exercise 6: Fill Series 1. Open the notes.pdf file and read page 17 on Series 2. Open the file createSeries.xls and complete the worksheet 3. Save the file
Exercise 7: various functions
7
Relative and Absolute Cell References Maximum, Minimum and Average Functions 1. Open week_1a 2. Rename the Sheet1 tab to Relative Reference and change the colour of the tab to green. 3. Calculate the Product totals in Column H using the AutoFill feature. 4. Calculate the monthly Gross Income totals in Row 12. 5. Calculate the Monthly Average sales in row 14. 6. In rows 17 and 18 calculate the Minimum and Maximum sales for each Product under their respective heading. 7. Display formulas and refer to them as you read the next few paragraphs. To make it easy to copy formulas (especially when using the Autofill feature), Excel uses a concept called Relative Addressing. Relative addressing or a Relative Cell Reference refers to the concept that cells in a formula change relative to where the formula is copied. For example, the formula in cell B12 =SUM(B8:B11) changed to =SUM(C8:C11) when it was copied across to the next column. Likewise, the formula in cell H8 =SUM(B8:G8) changed to =SUM(B9:G9) when the formula was copied down to the next row. However, there will be times when you’re copying formulas and you don’t want all the cell addresses to change relative to the row or column to which you’re copying. That is, the formula may be referring to a specific cell and you want that specific cell to be always referred to regardless of where the formula is copied or moved to. In situations such as this, you need to create an Absolute Cell Reference. To specify an absolute cell reference place a $ (dollar sign) before both the column letter and row number for a cell’s address using the [F4] function key (on the keyboard) e.g. $B$12 8. Click in the Absolute Reference sheet tab and complete tasks 1 to 3
8
Mixed Cell References Mixed cell addresses (references) contain a combination of relative and absolute addressing. Because a cell address has two components – a column and a row – it is possible to fix (make absolute) either portion while leaving the other unfixed (relative). You do this by keying the $ sign before the particular component you wish to set as absolute. For example: $A1 A$1
As this formula is copied, it will always reference the contents of column A, but the row may vary if the formula is copied down to a different row. As this formula is copied, it will always reference the contents of row 1, but the column may vary if the formula is copied across the spreadsheet.
9. Click in the Mixed Cell Reference sheet tab and complete tasks 1 and 2.
COUNTIF AND SUMIF functions 10. Click in the COUNTIF() & SUMIF() sheet tab. 11. Using either the COUNTIF or SUMIF functions, calculate answers to the four questions.
Names and VLOOKUP function 12. Click on the VLOOKUP() sheet tab. 13. Assign the name ‘priceTable’ to the Product/Price table 14. Use VLOOKUP() to place the prices in Column E. Refer to IMPORTANT NOTE below. 15. Calculate the ‘Revenue’ column 16. Click on the More VLOOKUP() sheet tab and complete the task IMPORTANT NOTE: The values that you are searching within with LOOKUP functions (i.e. VLOOKUP(), HLOOKUP() or LOOKUP() ) must be in ascending order (1, 2, 3...) or in alphabetical order (a, b...z) or you get many error messages.
LOOKUP function 17. Click on the LOOKUP() sheet tab 18. Complete tasks 1, 2 and 3
Exercise 8: This question consists of three sections. 9
The manager of Amy’s Real Estate wants to set up a spreadsheet, which will allow her to determine budget forecasts on the anticipated revenue, expenses, gross profit, tax and net profit for the following six months. This will help her decide whether an increase in membership fees will be necessary in the future.
Remember to use Absolute cell addresses where applicable. SECTION ONE Create the worksheet on the following page exactly as it appears, starting in Cell A1. When completed follow the instructions below: 1. Alter the width of Column A to size 40. 2. Alter the width of columns B – G to size 15. 3. Change the text for the first two headings to font colour violet 4. Change the first two headings to Times New Roman, size 16. 5. Change ‘PREPARED BY (YOUR NAME) to italics. 6. Change the heading in cell A19 and 27 to Times New Roman font, size 14. 7. Change headings in rows 25, 37, 39 and 43 to bold. 8. Left align the cells B18 – G18. 9. Change the cells B18 – G18 to blue coloured font and bold. 10.Shade the cells B18 – G18. 11.Add a border with a top single line and double bottom line to the Net Profit cells B43 – G43.
10
AMY'S REAL ESTATE Projected Income for the months July to December PREPARED BY (YOUR NAME) Commission Rates: Julian Kathy Greg Wendy
10% 15% 12% 12%
Monthly Sales Growth:
3%
Other Monthly Expenses
5,500
Company Tax:
33% Jul
SALES Julian Kathy Greg Wendy
Aug
Sept
850,000 1,600,000 789,000 1,460,000
TOTAL SALES EXPENSES COMMISSION Julian Kathy Greg Wendy TOTAL COMMISSION Other Monthly Expenses
5,500
TOTAL EXPENSES NET PROFIT COMPANY TAX NET PROFIT AFTER TAX
11
Oct
Nov
Dec
12. The Sales are expected to grow by the percentage figure shown in cell B12. Create a formula to calculate the Total Sales. 13.Each salesperson is paid a certain rate of commission on sales, as shown in the worksheet under the heading ‘Commission Rates’. Calculate each salespersons expected commission for each month 14.Create a formula to calculate the total commission. 15.The Other Monthly Expenses are to remain constant as shown in cell B14. 16.Create a formula to calculate the total expenses. 17.Create a formula to calculate the net profit for the projected months. 18.Create a formula to calculate the company tax for the projected months using the company tax rate shown in the worksheet. 19.Create a formula to calculate the net profit after tax for the projected months. 20.Alter the page setup to the following: Horizontally centre the worksheet. 21.Create a centred header that displays the text – End of Section 1. 22.Save your worksheet. SECTION TWO 1. Copy all of the data on sheet 1 over to sheet 2. 2. Rename sheet 2 to display the text – Section 2. 3. Edit the worksheet on sheet 2, by carrying out the following instructions: 4. Add a row after Total Commission giving it the bolded heading AVERAGE COMMISSION. 5. Change the font colour of the Average Commission figures to Violet. 6. Amy’s initial recruitment period was for 6 months. She is thinking of extending it to 12 months. Add the months January to June as well as a total column for all months and do the appropriate calculations. 7. Alter the first two headings so they are centred over the worksheet. 8. Change the months in the second heading to reflect the change in months. 9. Decrease the tax rate to 30%. 10.Increase the percentage value of all commission by 2%. 11.Change appropriate figures to currency, with 2 decimals. 12.Alter the header to display the text End of Section 2. 13.Re-save the edited workbook.
12
If all your formulae are correct, your Total Net Profit after tax should = $39,792,217.80. If you changed: Monthly sales growth to Other Monthly expenses to Company tax to Greg’s commission to
6% $10,000 34% 15%
Total net profit after tax =
$44,482,496.62
SECTION 3 1. Insert a column chart on a new sheet, showing the Net Profit after Tax for the months July to June. Give your chart a suitable title, the axes must be labelled but no legend is required. 2. Change the Title font to Times New Roman, 16 points and violet. 3. Change the x & y axis labels to bold and violet. 4. Bold the months and values on the x & y axis. 5. Change the chart column colour to green. 6. Rename the sheet Chart1 to display the text – Net Profits chart. 7. Re-save the edited workbook to your test disk under the same name.
13
