BIS202 Exercises Exercise 1 Objectives: > Introduction to MS Excel files, Workbooks, Worksheets, Columns and Rows. > Formatting Worksheets. > AutoFill, Numeric formats, previewing worksheets. PW ARe II 3 t as 4 [E0001 [Ford 7.5] 35)? 2 Pp 5 [E0002 _|Mino 8 30/7 2 2 62 [Bell 65| 25/2 2 2 72 Davis 9 40) 2 P a? Ture 20 392 2 2 Open a new workbook and save the file with the name “Payroll” Enter the labels and values in the exact cells locations as desired. Use AutoFill to put the Employee Numbers into cells A6:A8. Set the columns width and rows height appropriately. Set labels alignment appropriately. Use warp text and merge cells as desired. Apply borders, gridlines and shading to the table as desired Format cell B2 to Short Date format. Format cells E4:G8 to include dollar sign with two decimal places. 10, Calculate the Gross Pay for employee; enter a formula in cell E4 to multiply Hourly Rate by Hours Worked. 11. Calculate the Social Security Tax (S.S Tax), which is 6% of the Gross Pay; enter a formula in cell F4 to multiply Gross Pay by 6%. 12, Calculate the Net Pay; enter a formula in cell G4 to subtract Social Security Tax from Gross Pay. 13, Set the work sheet vertically and horizontally on the page. 14, Save your work, eS RNanewne 2|Page Downloaded by Bab Ospy (babospy19@gmail.com)BIS202 Exercises Exercise 2 Objectives: > Using Formulas. > Header and Footers. AE : don Taam Cal Sitstes 2 45 Name not Murs caleper 4 Adam ee a sal jiee [al 7 Alex 15| 6 { 8 Emma a7 9 io TOTAL i Fa 12 ome 25 1, Open a new workbook and save the file with the name “Call Statistics”. 2. Delete Sheet 2 & 3, and rename Sheet 1 to (Call Statistics). 3. Enter the labels and values in the exact cells locations as desired 4. Set the row height of rows 1 & 3 to size 30; and rows 4 until 10 to size 20. 5. Set labels alignment appropriately. 6. Use Warp Text, Orientation and merge cells as desired. 7. Apply border, gridlines and shading to the table as desired. 8. Format column E to include euro (€) sign with two decimal places. 9. Format cell B12 to include % sign with 0 Decimal places. 10. Calculate the Calls per Hour, enter a formula in cell D4 to divide numbers of calls by Hours worked. Using AutoFill, copy the formula to the remaining cells. 11, Calculate the Bonus, Enter a formula in cell £4 to multiply ‘Calls per Hours’ by the fixed Bonus Rate in cell B12. Using AutoFill, copy the formula to the remaining cells. 12. Calculate the ‘TOTAL’. 13, Set the worksheet vertically and horizontally on the page. 14, Create a header that includes your name in the left section, and your ID number in the right section. Create the footer that includes the current Date in the center. 3[Page necxensosicerectcome EY studocu Downloaded by Bab Ospy (babospy!8@gmal.com)BIS202 Exercises Exercise 3 Objectives: > Number, Commas and Decimal numeric formats. > Working with Formulas (Maximum, Minimum, Average, Count and Sum). > Percentage Numeric Formats. 9. 1 2 3 "4 Emp. No.[Name 5 6 7 8 Monthly Sales Report - July [Sales Amount 2500/7 3000] 2200) 4500] 3500] 2500) [Comission [Total Salary [Salary ‘S102 S105, S112 ‘S107 su10 S103, ‘Ahmed Hassan Ali Waleed [Mohammed Samir Total ‘Average Highest Lowest ‘Count Create the worksheet shown above. Set the column widths as follows: Column A: 8, Column Columns E & F: 14. Enter the formula to find COMMISSION for the first employee ‘The commission rate is 2% of sales, COMMISSION = SALES * 2% Copy the formula to the remaining employees. Enter the formula to find TOTAL SALARY for the first employee where: ‘TOTAL SALARY = SALARY + COMMISSION Copy the formula to the remaining employees. Enter formula to find TOTALS, AVERAGE, HIGHEST, LOWEST, and COUNT values. Copy the formula to each column Format numeric data to include commas and two decimal places. Align all column title labels horizontally and vertically at the center. Create a Header that includes your name in the left section, page number in the center section, and your ID number in the right section. Create footer with DATE in the left section and TIME in the right section 4, Columns € & 10. Save the file with name Exercise 3. 4|Page Downloaded by Bab Ospy (babospy19@gmail.com)BIS202 Exercises Exercise 4 Objectives: > Working with the IF Statement, a i a a Oa Ea Torat | ToTAL rice | PRICE un |BEFORE| AFTER ar tax | Tax 100_| 115 [30 101_| 256 | 12 a9] 56 23_| 150 ao | 5. 200 | 56 204 | 300 4_| 90 \ Count items |? 2 Average of tax _ |? 2 Min ITEM PRICE |? «Max Tem Price |? For the above table find the following: ‘TOTAL PRICE BEFORE TAX =NO, OF ITEMS * ITEM PRICE. eens REASONABLE. 6, Save file as Exercise 4, ‘TAX (If ITEM PRICE is less than 100, TAX is 50, otherwise it should be 100). ‘TOTAL PRICE AFTER TAX = TOTAL PRICE BEFORE TAX + TAX. RATE (If TOTAL PRICE AFTER TAX > 3500 then the rate is “HIGH”, otherwise it is Find Count of Items, Average of Taxes, Min Item PRICE and Max Item PRICE. 5|Page ‘isdoameriswanbiorersterineon Ey studocu Downloaded by Bab Ospy (babospy19@gmail.com)BIS202 Exercises Objectives: Exercise 5 ‘+ Working with Sum IF and Count IF statements. ‘© Inserting Charts. w 3 14 PErnanawne x z t D E F é Sales and Profit Report - First Quarter 2012 No city Jan Feb Mar | Average Maximum ‘e001 [New York $22,000 $29,000.00 $19,000.00]? z 16002 tos Angees $42,000.00 $39,000.00 $4300000| 2 2 [London $18,000.00 $20,000.00 §22,00000| ? 2 [paris $35,000.00 $26,000.00 $31,000.00]? z 2 {Munich __| 512,000.00 $18,000.00 §13,00000| > 2 ‘Total Sales > 2 2 Cost $53,000.00 $94,000.00 $43,000.00 Profit, > 2 2 10%Boms > 2 2 Total Sales greater than 30,000 [No Sales greater than 30,000 Create the worksheet shown above. Set the Text alignment, Columns width and high appropriately. Use AutoFill to put the Series Numbers into cells A5:A7. Format cells C3:G7, C8:E11, C13:E13 to include dollar sign with two decimal places. Find the Average Sales and Maximum Sales for each City. Find the Total Sales for each Month, Calculate the Profit for each month , where profit = Total Sales - Cost Calculate the 10% Bonus, which is 10% of the Profit. Find the Total Sales for each Month; only for sales greater than 30,000. 10, Find the No of Sales for each Month; only for sales greater than 30,000, 11. Create the following Charts: Downloaded by Bab Ospy (babospy19@gmail.com) Sales and Profi Maximum Sale: each city 6|PageBIS202 Exercises Objectives: Exercise 6 ‘+ Working with Sum IF and Count IF statements. + Inserting Charts, AW |B 21 c [new York new york stile [chica new York [chicaeo. seatile seats seate new York 17 Chicago {,.¢i| Db | € USA Annual Purchases Report 2011 luniversity eh schoo! [Universi [univers [university [oniversity ih School [None [university [None annual Salary $7,500 $3999 $5,750 32,000 ‘$47,500 $13,150 $3,739 2.150) $22,450 $2,500 8 Seaie 491 26 [Male 27 Female 1. Open a new workbook and create the above worksheet. 2, Make sure that your worksheet looks like the picture (Alignment, Shedding, Borders, Wrap text, Orientation ...) 3, Find the entire customer IDs, 4, Format Colum E & D to Currency with dollar sign and two decimal places. 5. Find the Total Annual Purchases for each City. ‘isdoameriswanbiorersterineon Ey studocu Downloaded by Bab Ospy (babospy19@gmail.com) 7\PageBIS202 Exercises Seed ay [sTemsie) syonco | ssaesco | 522000 6. Find the Average Annual Purchases for each Education. 7. Find the total number of customers from each gender. 8, Find the total annual salary for each gender in each city. 9, Create the following Chart: Annual Phurchases Female} ena s.00 Total Annual Purchases $s0ono1 siosenoe $15 .0000 s2a,00.00 330000 $25,000 20000 315,000 $10.00 $s,000 » Anual Purchases Vs. Annual Salary ‘Annual Purchases a Anil Slory Downloaded by Bab Ospy (babospy19@gmail.com) BlPage