Foundations of IT Girija Krishnaswamy
Practical 4
1. Download the file Data2.xlsx to the Downloads Folder on the Computer.
2. Rename Data.2xlsx using your Student ID for the file name. Example: If your
Student ID is 123456, you will rename your test file to 123456.xls
• Create a spreadsheet that contains the following details, using Figure 1 as a guide.
• Create formulas to calculate the following:
Sell $ = Cost + Cost * Markup
Total Sales = Units Sold * Sell Price
Total Costs = Units Sold * Cost
Profit = Total Sales – Total Costs
• Enter functions for the Total and the Average.
• Format the spreadsheet similar to the sample as shown in Figure 1.
o Increase the font size of the title to 14 point and format in bold.
o Centre the title across the top of the spreadsheet.
o Add bold and italics as shown in Figure 1 to the column headings. Centre the
column headings in B3:H3.
o Add borders as shown in figure 1.
| o | Format the numbers as shown in Figure 1: Units Sold – Comma format with zero decimal places |
Cost $, Sell $, Total Sales $, Total Costs $, & Profit $ – Accounting number
format with $ currency symbol with zero decimal places
Markup % – Percentage format with zero decimal places
• Rename the sheet tab from Sheet1 to Widget Sales and change the sheet tab colour
to blue.
• Add your full name to the file properties.
• Add conditional formatting to the Units Sold figures (B4:B10) to apply a green fill with
dark green text to Units Sold figures that are greater than1,200.
• Save the file, close and submit.
• Log off from the Computer.
Figure 1