University Accounting Assignment: Inventory, Receivables, and Reports

This document presents a comprehensive solution to an accounting assignment, covering key topics such as naming cells, negative numbers, spreadsheet design for data entry and reporting, and the use of IF statements. It delves into periodic inventory systems, worksheet creation, and the preparation of financial reports, including handwritten and spreadsheet solutions. The assignment further explores inventory flow assumptions (FIFO, LIFO, Average Cost), bank reconciliation (handwritten and spreadsheet methods), journalizing accounts receivable entries, and methods for estimating bad debts (percentage of credit sales and aging of accounts receivable). It concludes with an analysis of how receivables are used to evaluate a firm's financial position, the treatment of dishonored notes receivable, and a work-integrated assessment, providing a well-rounded understanding of fundamental accounting principles and practices. The solution includes examples and spreadsheet designs to illustrate the concepts.

ACCOUNTING

Table of Contents
1. NAMING CELL..........................................................................................................................1
2. NEGATIVE NUMBERS.............................................................................................................1
3. NEED OF DESIGNING SPREADSHEET WITH SEPARATE DATA ENTRY AREA AND
SEPARATE REPORT AREA.........................................................................................................2
4. FUNCTIONS IF STATEMENT WITH SPREADSHEET EXAMPLE......................................3
5. PERIODIC SYSTEMS................................................................................................................4
6. WORKSHEET AND FINANCIAL REPORTS..........................................................................4
A. Handwritten solution..............................................................................................................4
B. Spreadsheet designing............................................................................................................6
C. Advantages of spreadsheet.....................................................................................................8
7. APPLICATION OF INVENTORY FLOW ASSUMPTIONS....................................................9
Requirement 1: Calculation of ending inventory under different inventory method..................9
Requirement 2: Computation of gross profit............................................................................10
8. BANK RECONCILIATION.....................................................................................................11
A. Handwritten solution............................................................................................................11
B. Spreadsheet solution.............................................................................................................11
9. JOURNALISE ACCOUNT RECEIVABLE ENTRIES............................................................13
10. TWO DIFFERENT METHODS OF ESTIMATING BAD DEBTS WITH EXAMPLE.......13
11. USE OF RECEIVABLES TO EVALUATE FINANCIAL POSITION OF FIRM WITH
EXAMPLE.....................................................................................................................................14
12. DISHONOUR OF NOTES RECEIVABLE............................................................................14
13. WORK INTEGRATED ASSESSMENT................................................................................15
REFERENCES..............................................................................................................................20
2

1. NAMING CELL
This is done in spreadsheet by giving name to specific cell through box at left of formula
bar. Procedure: First the individual need to select the range or cell which need to be named, than
the person have to go and click formula tab and then the individual need to click define name
button. At last person need to enter the name for the particular rand or cell and Enter.
Current assets
Inventory 3000
Receivables 2500
Cash 2000
Non-current assets
Plant and building 18000
Furniture 25000
Liabilities
Current liabilities
Payable
Bank overdraft 5000
short-term debt 12000
Non-current liabilities
Long-term loan 20000
Owner's equity
Share capital 10000
Retained earnings 2000
general reserve 1500
Total assets 50500
1

2. NEGATIVE NUMBERS
 First the individual need to go on format cells dialogue box which is given under number
tab.
 Secondly the individual need to select the number option and then click on the bracket
option in the negative number window (Schneider, Becker & Berg 2017).
Further, the accountant always uses negative number in bracket because the red colour
link represents that organisation is losing money. Moreover, sometime if the accountant do not
use colour printers the loses are determined by minus sign.
2
Illustration 1: Naming cell, Formula View

3

Particulars Amount
Building 78000
Accumulated
depreciation (2300.00)
Written down value 75700
3. NEED OF DESIGNING SPREADSHEET WITH SEPARATE DATA
ENTRY AREA AND SEPARATE REPORT AREA
Accountant creates spreadsheet in order to properly record the entire data set in a separate
data entry system and separate report area helps to generate accounting reports analyse business
position. Spreadsheet helps to maintain proper record of all the accounting information and
facilitate accountant to examine the results. Spreadsheet technique enables accountant to have
clear insight and guide over decision making process (Ramachandran Rackliffe & Ragland
2016). Moreover, designing spreadsheet assist the auditor in avoiding time wastage over testing
procedures and designing standard.
Designing Spreadsheet with separate data entry area.
This system is used to provide a specific layout of overall data of entries by avoiding
errors and mistakes. Recording all the entries in spreadsheet gives accountant an analytical
overview and solution of all the business operations. Further, it is used by auditor as the
designing process is reliable, flexible and assists in managing time.
Designing Spreadsheet with separate report area
The system helps the accountant in separating data, presentation and analysis. The
accounting manager analyses the reporting in spreadsheet as it can be easily refreshed by
replacing raw data into updated data. Beside, the manager can use different combinations of
formula to analyse the reported data.
Example:
4

Illustration 2: Formula view
Illustration 3: Normal view
4. FUNCTIONS IF STATEMENT WITH SPREADSHEET EXAMPLE
This is the most popular function of Excel, accountant uses this to analyse the cell value
which are either true or false (Excel Functions, 2017). The ultimate statement of IF term is that “
if the value is true than use that, otherwise use some other value”
5

Here, IF function is used to match the trial balance showing that if both the debit and
credit balance perfectly match than indicate “No difference”, otherwise open suspense account.
As reflected in the trial balance, there is a difference of 700 therefore, suspense account open by
the same amount using IF function.
6
Illustration 4: Normal view
Illustration 5: Formula view

5. PERIODIC SYSTEMS
Periodic inventory is a system which keeps update of inventory on regular basis. In this
system accountant do not need to put much effort as it is a systematic process and keeps records
up to date of cost of goods sold (Ingram 2014).
For example,
Periodic inventory
system
Particulars Amount
Beginning inventory 12000
Add: Goods purchase 135000
Less: 16000
Cost of goods sold 131000
7

6. WORKSHEET AND FINANCIAL REPORTS
A. Handwritten solution
Adjustments
1. Selling Expense
Supplies Expense: 2400*2/3= 1600
General Expense
Supplies Expense: 2400*1/3= 800
2. Building depreciation
Selling expense: 4000*1/2 = 2000
General expense: 4000*1/2 = 2000
3. Building depreciation
Selling expense: 3,700*1/2 = 1,850
General expense: 3,700*1/2 = 1,850
Account
Trial
Balance
Adjustment
s
Adjusted
balance
Income
statement
Balan
ce
sheet
Debit
Credi
t Debit
Credi
t Debit
Credi
t Debit
Credi
t Debit
Credi
t
8

Cash 12450 12450 12450
Accounts Receivable 17650 17650 17650
Inventory 56980 56980 56980 78000 78000
Supplies 7560 2400 5160 5160
Buildings
14500
0 145000
14500
0
Accumulated Depn.,
Building 17600 4000 21600 21600
Furniture 23780 23780 23780
Accumulated Depn.,
Furniture 5760 3700 9460 9460
Accounts Payable 17400 17400 17400
Salary payable 2300 2300 2300
Interests payable 1400 1400 1400
Unearned Sales
Revenue 7650 2900 4750 4750
Note, payable, Long
term 34000 34000 34000
Capital
14267
5
14267
5
14267
5
Drawings 4590 4590 4590
Sales Revenue
16600
0 2900
16890
0
16890
0
Sales Discount 3450 3450 3450
Sales return and
Allowances 3430 3430 3430
Purchases 89700 89700 89700
Purchases Discount 4015 4015 4015
Purchases Returns and
Allowances 7690 7690 7690
Selling Expense 23700 23700 23700
9

Supplies Expense 1600 1600 1600
Depreciation on
building 2000 2000 2000
Depreciation on
Furniture 1850 1850 1850
Salaries Expense 2300 2300 2300
General Expense 14500 14500 14500
Supplies Expense 800 800 800
Depreciation on
building 2000 2000 2000
Depreciation on
Furniture 1850 1850 1850
Interests Expenses 1400 1400 1400
Total
40279
0
40279
0 16700 16700 414190
41419
0
20556
0
25860
5
28663
0
23358
5
Net income 53045 53045
Total
25860
5
25860
5
28663
0
28663
0
Fancy Footwear Income
Statement Amount
sales revenue 168900
Less: Sales return and allowance
Cost of good sold 3430 165470
beg,. Stock 56980
Purchase 89700
Less: Purchase return and
allowance 7690 82010
Cost of goods sold 138990
Less: Ending stock 78000 60990
Gross margin 104480
10