Accounting Practical Set
Project description
The Accounting Practical Set requires you to complete the accounting cycle for one month for a sole trading service business. A report of 500 words is also required.
Instructions in Word are attached ‘Practice Set Instructions Sem2 2014′ (please read these instructions very carefully as they are quite detailed) and the Excel
template for students to complete is attached ‘ Practice Set Student Template _Excel_ ‘
The Powerpoint slides for the report activity we will cover in lecture 7 – note that our topic is not the same so this is not designed to be copied, just inspiration!
Microsoft Excel Basics
1. Getting started with Excel
Microsoft Excel is mostly used for performing calculations and creating graphs or charts. You can enter data in the form of text or numbers.
An Excel file is called aspreadsheet or workbookand it contains multiple worksheets. The spreadsheet is saved as a .xlsx file.
Worksheets can be used to group information together. A worksheet can contain data, graphs, or both.
Each worksheet is identified by tabs, which can be edited to have a name related to the data or it can be coloured for emphasis.
When you open Excel, it creates a blank spreadsheet with three worksheets. You can insert, delete, move or copy worksheets by right-clicking on the tabs. A data
worksheet contains rows and columns of cells, similar to a table in Word.
Row: The space between two horizontal lines -generally numbered down the left side of theworksheet.
Column: The space between two vertical lines -depicted by letters across the top of the worksheet.
Cell: Each cell represents the intersection of a row and acolumn and can hold any kind of data.
To specify that you want a cell or a group of cells, yourefer to them by their cell reference. This is the intersection of the column and row, with the column label
followed by the row label. For example, the first cell in the top left has the address A1.
You can also see gridlines – these lines guide your eyes across the page. The cursor is located at the activecell, which has dark gridlines around it.
Entering data
A cell can contain text, numbers or both. Select the cell where you want to enter data and start typing. To accept the information, press Enter or the Tab key or an
Arrow key on your keyboard. Excel automatically uses rightalignment for numbers and leftalignment for text.
You can edit data by selecting the cell that you want to edit and double-clicking on the cell. Pressing the Delete key will delete all data from the cell.
2. Formattingspreadsheets
Formatting a spreadsheet means changing the way it looks to make it more attractive. Formatting can include modifying number styles, text size and colours. Formatting
in Excel is very similar to formatting with Word.
For practice: Open the file named Fish Exercise.xlsx on Blackboard. We will use this spreadsheet to practice formatting.
Many of the most commonly used formatting options can be selected from the Home tab or by using keyboard shortcuts.
Below is a list of the icons available for formatting on the Home tab.
Change the font style for the selected cells
Change the text size in the selected cells
Turns Bold formatting on and off in the selected cells (Ctrl + B)
Turns Italic formatting on and off in the selected cells (Ctrl + I)
Turns Underline formatting on and off in the selected cells (Ctrl + U)
Change the border style around the selected cells
Change the background colour of the selected cells
Change the colour of text in the selected cells
Moves the contents of the cell to the left
Moves the contents of the cell to the centre
Moves the contents of the cell to the right
Decreases indent for the selected cells
Increases indent for the selected cells
Merges the selected cells and moves the contents to the centre
Many additional formatting options are not available on the Home tab. You can access these additional options by right-clicking on the cell or cells for which you wish
to change the formatting and then clicking on the Format Cells option.
The Format Cells dialogue box lists the different formatting options on tabs.
Column Widths and Row Heights
You can adjust the width of columns and height of rows by right-clicking on the columns and choosing Column Width or Row Height respectively. However, Excel can
automatically format the rows for you by doing the following:
Adjusting Column Width and Row Height
1. Move the mouse cursor so that it is between the Column A and B headings. Your mouse pointer will change to a double arrow shape.
2. Click your mouse button and drag to the right. A caption will appear telling you how wide the column will be.
Automatically Adjusting Sizes
You can also resize a column so that it will automatically fit the contents of the column.
1. Move your mouse pointer so that it is between the Column A and B headings again.
2. Double-click to automatically fit the column’s contents.
Text alignment
You can format text in Excel similar to Word. The most common text formatting is to merge the cells into one large cell. To do this, select several cells and then
choose the Merge and Center command.
Change the alignment of the writing in this cell – go to the Alignment group on the Home tab. Choose the Middle Align and also change the orientation to Rotate Text
Up. Alternatively, you can do this through the Format Cells dialogue box.
If you can’t see all of your writing in the cell, you can use the Wrap Text command. This will increase the row height to fit all of the text within the cell.
Borders
Borders (or lines) should be used to make the worksheet easier to read.Select where you would like to add borders. Then use the borders icon on the Home tab.
From here, you can choose which border, line style and line colour that you want to use.Note that you can use the Erase Border icon to draw over any borders you want
to remove.
You can also choose More Borders at the bottom of the list. This opens the dialogue box below, giving you even more choice of lines to use. Select a line style and
colour and click on the diagram to apply these borders.
3. Number Formatting
When using Excel, it is often important to change the way numbers are presented in a spreadsheet. For example:
We want this 3845
To appear as $3,845
In Excel,you don’t type dollar signs yourself – you format the numbers.
Dollars, percentages, dates and times are all treated by Excel as numbers. The only difference is the way they’re formatted. Numbers can be formatted in several ways.
The formatting options are found in the Numbergroup on the Hometab, as indicated in the picture below:
If you click on the arrow next to the word “General”, you can select the most common number format categories:
The Number category gives you a choice of displaying decimal points. You can increase or decrease the number of decimal points by using the shortcut underneath:
You can also include the 1000 separator – this places a comma in the number to make it easier to read.
The Currency category displays a monetary unit beside the number – for example, a dollar sign $.
The Percentage category converts the number into a percentage and displays the % sign beside the number. Note that if you use this format, you do not have to divide
the number by 100.
The Short Date category converts a number into a date.
Number formats will remain even if the numbers in the cells change or are deleted.
If you choose More Number Formats, you can see the Format Cells dialogue box. Here there are extra options for each category.
For example in the Number category, you can choose different ways to present negative numbers. Sometimes this formatting is used to ensure that negative numbers are
noticeable.
If you wanted to change the format for a date, right-click on the cell, chooseFormat Cell, select the Number tab and then chooseCustom.
In the Type, you would need to enter dd to represent days, mm to represent months and yy to represent years, as shown in the diagram below. When you are happy with
the sample, click on OK.
For practice: In the FishExercise worksheet, look for the Sale Date in Column B. Change the format of Column B into the date format dd-mmm-yy. Then format the Cost
in Column C and the Price in Column F into currency with no decimal places.
4. Formulas and Functions
In Excel, the arithmetic operators are + – * /
Multiplication is represented by *
3 x 4 = 12 is the same as 3 * 4 = 12
Division is represented by /
12 ÷ 4 = 3 is the same as 12 / 4 = 3
The comparison operators are:
Equal to =
Greater than >
Less than <
Greater than or equal to >=
Less than or equal to <=
Not equal to <>
Formulas
Formulas are used to calculate values. A formula must always begin with an equals sign (=). A cell containing text cannot be used in formulas. One you have typed
your formula, you press the Enter or Tab keys to calculate the value.
The normal mathematical order of operations holds for Excel. Formulas are calculated in this order:
B Brackets ( )
I Indices ^
M Multiplication *
D Division /
A Addition +
S Subtraction –
Usually, a formula will refer to other cells in the spreadsheet using cell references (like A1, or B3:B6). That way, the formula automatically updates if the values
in those cells change.
Be careful! The cells in a formula can’t include the cell where the answer will be.
For practice: In your FishExercise worksheet, calculate the Cost per kg for each fish – this can be calculated by Cost divided by Weight.
Remember to start with the equals sign! Try to do this using cell references. For example, the Cost per kg for Bream will be:
=C5/D5
Press Enter on the keyboard to calculate the answer. Change the number in Cell D5 and notice that the result of the formula is updated. Do this calculation for each
fish.
Now calculate the Profit for each fish. This is equal to the Price minus the Cost. Use cell references for this formula. Creation of a formula is even easier if you
use the mouse to create cell references. Copy this formula down the column for each fish.
Now calculate the Total Profit. This is equal to the Profit multiplied by the Qty. Sold. Calculate this for each fish.
Double-click in Cell G5 to edit the formula. Notice that the cell references in the formula have become coloured – and the cells have a border around them that is the
same colour as the reference. These coloured borders can be moved as an easy way to edit a formula.
Move your mouse to the edge of the blue border around Cell F5 and drag the border to move it to Cell E5. This will change the cell reference in the formula. Press
Esc on the keyboard to cancel the changes to the formula.
You can display your formulas by clicking on Ctrl and ~. The ~ key is located at the top left of your keyboard. This switches between values and formulas and is a
useful way to check your cell references.
Functions
Excel has functions which you can use instead of common formulas. There are many functions available. However, there are five functions that are used frequently and
they are:
Sum Average Max Min Counta
A function has two components:
For example, if we wanted to add the cells from G7 to G12 together then we could type in the formula: =G7+G8+G9+G10+G11+G12
However, we can use a function instead: =sum(G7:G12)
The G7:G12 is called a cell range and means that the function is calculated based on the cells from G7 to G12 inclusive. Functions must include brackets at the end
and a cell reference or a cell range.
In Excel, it doesn’t matter if you use uppercase (capital) letters or lowercase letters in the formulas and functions. Typing SUM(G7:G12) is the same as typing sum
(g7:g12).
For practice: In your FishExerciseworksheet, find the values at the bottom of the sheet using functions. For example, use the following function to calculate the sum
of costs:
=sum(C5:C12)
If you wanted to build a proof into this worksheet, you could check that the total of “Cost” and “Profit “equals the “Price”.
For practice: In cell J4 enter the word Proof. In cell J5 build a proof. Price – sum (cost + profit) should be equal to 0. Copy this formula to all cells.
Insert Function
You don’t have to remember all the functions! The Insert Functiondialogue box helps us to create functions.
Click on the fx button in the Formula Barand look at the dialogue box in front of you. Select the Math & Trig category to answer the following question:
What does the Product function do?
Freeze panes
If you have a worksheet with a large amount of data, you need to scroll around the sheet to see all of the data. This means that your data headings will disappear.
We can freeze part of the worksheet so that your data headings stay on the screen. We do this through Freeze Panes on the View tab.
For practice: In your FishExercise worksheet, click on Cell B5 and then select theFreeze Panesbutton from the View tab and then click on theFreeze Panesoption. Now
scroll down your sheet – the headings stay at the top of the page. Scroll to the right of your sheet – the fish types stay at the right side of the page.
The solid line on your sheet shows you where the Freeze Panes has been applied. To remove Freeze Panes, go back to Freeze Panes on the View tab and now select
Unfreeze Panes. Note that you can also freeze rows instead of columns by selecting the row below your headings and vice versa.
5. Printingspreadsheets
There are some options when printing a worksheet, such as orientation, page width and height as well as printing gridlines.
Page Layout
In the PageLayout tab, you can change what the worksheet will look like when printed.
Worksheets can be printed in Portrait orientation or in Landscape orientation.
Typically, we would shrink the page to fit to one page in width and one page in height, located here on the Page Layout tab.
You also have the choice to print the gridlines and to print the column and row headings.
Remember if you want to print your formulas, click on Ctrl and ~ to show the formulas on the worksheet.
Header and Footer
When printing a worksheet, you should insert a header or footer with at least your name and student number. A Header appears at the top of every printed page. A
Footer appears at the bottom of every printed page. These are located in the Insert tab.
The Header and Footer have automatic fields that can be inserted, such as page number, sheet name and file name.
Once you insert a header or footer, the worksheet will be in a different view – the PageLayout view. Click on the Normal view in the bottom right corner of the
spreadsheet to return to the view we used previously.
Always do a print preview to ensure that the worksheet will look good when printed!
Saving your spreadsheet
To save your file, choose the Office Button and then choose Save or Save As. The keyboard shortcut is Ctrl and S.
TIP: You should save your files regularly! Save your files…
? When it would be too difficult to do it all again
? When you have just finished something that has gone well
? When you are just starting something new and you are not sure how it will look
? Every 10 minutes (especially if you don’t type!)
In Word, you can set the AutoRecover in case you forget to save.
1. Click on the Office Button and then select Excel Options.
2. Click on the Save option and then select checkbox next to Save AutoRecover information. You can change the number of minutes.
If you are working on an assignment, make backup copies of your files on H: drive. You don’t want to lose your work!
ScholarMatic: Explanation & Answer
Your ready answer from a verified tutor is just a click away for as little as $14.99
Click Order Now to get 100% Original Answer Customized to your instructions!
