HEXCO · Polytechnic · NC Accountancy

Business Computing and Digital Literacy: Spreadsheets

Learning Outcome 4 of 6

Free HEXCO NC Accountancy notes on Spreadsheets, learning outcome 4 of 6 in Business Computing and Digital Literacy. Data communication, file management, word processing, spreadsheets, databases and multimedia. Semester 1, module 310/22/M04.

Here we learn how to create and edit a spreadsheet, use formulae and functions, add and delete sheets, generate graphs, print, and produce invoices, receipts and simple financial statements, using Microsoft Excel as the example.

A spreadsheet is the accountant's everyday calculator, because once figures and formulae are entered, it recalculates totals instantly whenever a number changes.

4.1 Creating and Editing a Spreadsheet

A spreadsheet is a grid of rows and columns used to enter, calculate and analyse data, and a file of spreadsheets is called a workbook.

Columns are labelled with letters and rows with numbers, so each cell has an address such as B4.

Loading Excel and Entering Data

To load Excel, click Start, type 'Excel' and open it, then choose Blank workbook. Click a cell and type text (labels such as 'Sales'), numbers (such as 2500) or formulae, then press Enter to move down or Tab to move right. Text lines up on the left of a cell and numbers on the right. Widen a column by double-clicking the line between the column letters.

Editing Techniques

To edit a cell, double-click it or press F2, or retype its contents. Highlight a range by dragging across the cells, such as A1:D10. Delete clears the contents of selected cells. Cut (Ctrl + X), copy (Ctrl + C) and paste (Ctrl + V) move or copy data, and dragging the border of a selection moves it, while holding Ctrl copies it. The fill handle, the small square at the bottom-right of a cell, is dragged to copy a formula down a column or to continue a series such as Jan, Feb, Mar.

Borders, Merging Cells, and Inserting or Deleting Rows and Columns

Borders are added with Home, Borders, for example All Borders for a table or Bottom Double Border under a final total. Merge & Center joins selected cells into one and centres the text, which is used for titles across a table. To insert a row or column, right-click a row number or column letter and choose Insert; the new one appears above or to the left. To delete, right-click and choose Delete. Formulae adjust automatically when rows and columns are inserted or deleted.

4.2 Formulae and Functions

The real power of a spreadsheet comes from formulae, which calculate results from the values in other cells.

Functions are ready-made formulae for common calculations.

Creating and Using Formulae

A formula always starts with an equals sign (=) and uses cell references and operators: + (add), - (subtract), * (multiply), / (divide) and ^ (power). For example, =B2*C2 multiplies quantity by price. Excel follows BODMAS, so =(B2+B3)*10% adds first. Using cell references instead of typed numbers means the result updates automatically when the data changes.

When a formula is copied down, its references change in step (relative references). To keep a reference fixed, such as a VAT rate in cell F1, use an absolute reference with dollar signs, =D2*$F$1.

Using Functions

A function is a built-in formula with a name and arguments in brackets. The table shows the most useful functions for business.

Function	What it does
=SUM(B2:B10) Adds a range.	Totals the values in B2 to B10, such as total sales.
=AVERAGE(B2:B10) Finds the mean.	Adds the values and divides by how many there are.
=MAX(B2:B10) and =MIN(B2:B10) Find the largest and smallest.	Shows the highest and lowest sales, for example.
=COUNT(B2:B10) Counts numbers.	Counts how many cells contain numbers.
=ROUND(B2,2) Rounds a value.	Rounds B2 to 2 decimal places, as needed for money.
=IF(B2>=1000,"Bonus","No bonus") Makes a decision.	Tests a condition and shows one result if true and another if false.

Displaying Formulae

Normally a cell shows the result of its formula, while the formula itself appears in the formula bar. To show all formulae on the sheet at once, choose Formulas, Show Formulas, or press Ctrl + ` (the key above Tab). This is useful for checking work and for printing the formulae as evidence in assessments; press the same keys again to return to results.

Adding and Deleting Sheets

A workbook can hold many worksheets, shown as tabs at the bottom. Click the plus (+) icon to add a sheet, double-click a tab to rename it, such as 'January', and drag a tab to move it. To delete a sheet, right-click its tab and choose Delete; this cannot be undone, so check first. Formulae can refer to other sheets, such as =January!B10.

4.3 Generating Graphs

Graphs, called charts in Excel, turn rows of numbers into pictures that show patterns at a glance.

Choosing the right type of chart makes the message clear.

Creating a Chart

Select the data including the headings, such as months in column A and sales in column B, then choose Insert and a chart type, or Recommended Charts. The chart appears on the sheet; the Chart Design tab changes its style, colours and data, and the plus (+) button beside it adds a chart title, axis titles, data labels and a legend. Every chart should have a clear title and labelled axes.

Types of Graphs for Data Analysis

A column or bar chart compares amounts between categories, such as sales by branch. A line chart shows a trend over time, such as monthly sales for a year. A pie chart shows the parts of a whole as percentages, such as the share of each expense in total expenses, and works best with a few categories. A scatter chart shows the relationship between two variables, such as advertising and sales.

Saving and Printing

Save with Ctrl + S, choosing Excel Workbook (.xlsx) in Save As. To print, choose File, Print, and check the preview. To print only part of a sheet, select the range and choose Print Selection, or set a Print Area under Page Layout. Use landscape for wide sheets, Fit Sheet on One Page for small tables, and Print Titles to repeat headings on every page. To print a chart alone, click the chart before choosing Print.

4.4 Generating Business Documents

Spreadsheets are ideal for documents that combine text and calculations.

Invoices, receipts and simple financial statements can be set up once and reused.

Invoices

An invoice in Excel has the business's details and logo at the top, the invoice number, date and customer details, then a table with description, quantity, unit price and amount columns. The amount column uses a formula such as =B8*C8, copied down, followed by a subtotal (=SUM(D8:D10)), VAT (=D11*15%) and total (=D11+D12). The example below shows the layout; VAT is at 15% here only to illustrate the formula, and the current rate should always be checked.

Receipts

A receipt in Excel shows the receipt number, date, received from, amount in figures and words, for (what the payment is for), payment method and received by. The amount can be linked to the invoice total with a formula, and a template with pre-set borders and formulae can be reused by saving it as an Excel Template (.xltx).

Simple Financial Statements

A simple statement of profit or loss is laid out with labels in column A and figures in columns B and C. Formulae calculate gross profit (=Sales - Cost of sales), total expenses (=SUM of the expense cells) and profit for the year (=Gross profit - Total expenses). Because the figures are linked, changing any one number, such as rent, updates the profit at once, which makes the spreadsheet ideal for 'what if' questions.