Accounting Spreadsheets: Examples And Concepts

Naming Cells in Spreadsheets

The naming cell reference is used in MS-Excel for the purpose of understanding the concept of formula. Below is the example to calculate the Net profit using the function “Naming Cell Reference”.

Example

Normal View:

Net Profit Calculation:

Sales ($) Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper	Expenses ($)	Net Profit ($)
2566.02	2469.45	96.57
3256	1258	1998
1458	1066	392
2034	1034	1000

Formula View:

Sales ($)	Expenses ($)	Net Profit ($)
2566.02	2469.45	=Sales-Expenses
3256	1258	=Sales-Expenses
1458	1066	=Sales-Expenses
2034	1034	=Sales-Expenses

Brackets in worksheet is known as Parentheses. Negative figures in financial report denotes credit items or expenses. There are also other forms to present negative figures are: minus sign before the value, apply red color to the value or use brackets. Accountants normally follow the practice of using the brackets in preparation of financial reports because it is considered to be the best and simplest method to denote the negative figures in report. Further it can be said that brackets highlight the negative figures easily to the person.

Example:

Particulars	Amount ($)
Sales	$6,50,000.00
Direct Costs	($4,25,000.00)
Gross margin	$2,25,000.00
Indirect Costs	($1,68,000.00)
Net Profit	$57,000.00

Accounting spreadsheet reports should be designed with a completely separate data entry area and a separate report area because of the following reasons:

Separation of report area with the data area will provide a perfect image about the company’s financials to the readers.
Worksheet used for the preparation of the financials because formulae’s and many function keys can be used easily and one or more data can be modified or deleted without changing of the whole data.

After the preparation of the report, all the financials must be collaborate with the data entry area.

Therefore, Separation of report area with the data area will provide profits to the stakeholders because the company’s financial position can be easily analyzed.

Example:

Normal View:

Kapoor Ltd
Trading Account
For the year ended 30 June, 2017
Particulars	Amount ($)	Particulars	Amount ($)

Opening Inventory:	Closing Stock:
Raw Material	65000	Raw Material	25000
Work in Progress	25000	Work in Progress	10000
Purchases:
Raw Material	42000	COGS	184000
Direct labour	21000
Direct Expenses:
Depreciation	18000
Factory Insurance	8000
Manufacturing overhead:
Manufacturing Expense	14000
Factory Salary	26000
TOTAL	219000	TOTAL	219000

Kapoor Ltd
Income Statement
For the year ended 30 June, 2017
Particulars	Amount ($)
		Total ($)
Revenues:
Sales	285000
Other Income	0
Total Income (A)	285000
Expenses:
COGS	184000
Salary	41000
General expenses	17000
Advertisement	7000
Light and Power	12000
Rate and Taxes	4500
Total operating expenses (B)	265500
Net Income (A-B)	19500

Formula View:

Kapoor Ltd
Trading Account
For the year ended 30 June, 2017
Particulars	Amount ($)	=+B4	Amount ($)
Opening Inventory:	Closing Stock:
Raw Material	65000	Raw Material	25000
Work in Progress	25000	Work in Progress	10000
Purchases:
Raw Material	42000	COGS	=E17-(E6+E7)
Direct labour	21000
Direct Expenses:
Depreciation	18000
Factory Insurance	8000
Manufacturing overhead:
Manufacturing Expense	14000
Factory Salary	26000
TOTAL	=SUM(C5:C16)	TOTAL	=C17
Kapoor Ltd
Income Statement
For the year ended 30 June, 2017
=+B4	Amount ($)	Total ($)
Revenues:
Sales	285000
Other Income	0
Total Income (A)	=SUM(C26:C27)
Expenses:
=D9	=E9
Salary	41000
General expenses	17000
Advertisement	7000
Light and Power	12000
Rate and Taxes	4500
Total operating expenses (B)	=SUM(C30:C35)
Net Income (A-B)	=D28-D36

‘IF’ function is used in worksheet to know something is true then apply the function otherwise apply something else. Logical calculations between actuals and forecasted values then use function ‘IF’ in worksheet.

The syntax for ‘IF’ Function is:

SYNTAX

IF(Something is True, then apply, otherwise do something else)

Using the same example calculating net profit/loss using ‘IF’ function:

No Profit or Loss:

Kapoor Ltd
Trading Account
For the year ended 30 June, 2017
Particulars	Amount ($)	Particulars	Amount ($)
Opening Inventory:	Closing Stock:
Raw Material	65000	Raw Material	25000
Work in Progress	25000	Work in Progress	10000
Purchases:
Raw Material	42000	COGS	184000
Direct labour	21000
Direct Expenses:
Depreciation	18000
Factory Insurance	8000
Manufacturing overhead:
Manufacturing Expense	14000
Factory Salary	26000
TOTAL	219000	TOTAL	219000

Kapoor Ltd
Income Statement
For the year ended 30 June, 2017
Particulars	Amount ($)	Total
Revenues:
Sales	265500
Other Income	0
Total Income (A)	265500
Expenses:
COGS	184000
Salary	41000
General expenses	17000
Advertisement	7000
Light and Power	12000
Rate and Taxes	4500
Total operating expenses (B)	265500
Net Income (A-B)	no profit no loss0

Profit:

Kapoor Ltd
Trading Account
For the year ended 30 June, 2017
Particulars	Amount ($)	Particulars	Amount ($)
Opening Inventory:	Closing Stock:
Raw Material	65000	Raw Material	25000
Work in Progress	25000	Work in Progress	10000
Purchases:
Raw Material	42000	COGS	184000
Direct labour	21000
Direct Expenses:
Depreciation	18000
Factory Insurance	8000
Manufacturing overhead:
Manufacturing Expense	14000
Factory Salary	26000
TOTAL	219000	TOTAL	219000

Kapoor Ltd
Income Statement
For the year ended 30 June, 2017

Particulars	Amount ($)	Total ($)
Revenues:
Sales	285000
Other Income	0
Total Income (A)	285000
Expenses:
COGS	184000
Salary	41000
General expenses	17000
Advertisement	7000
Light and Power	12000
Rate and Taxes	4500
Total operating expenses (B)	265500
Net Income (A-B)	Profit/19500

Loss:

Kapoor Ltd
Trading Account
For the year ended 30 June, 2017
Particulars	Amount ($)	Particulars	Amount ($)
Opening Inventory:	Closing Stock:
Raw Material	65000	Raw Material	25000
Work in Progress	25000	Work in Progress	10000
Purchases:
Raw Material	42000	COGS	184000
Direct labour	21000
Direct Expenses:
Depreciation	18000
Factory Insurance	8000
Manufacturing overhead:
Manufacturing Expense	14000
Factory Salary	26000
TOTAL	219000	TOTAL	219000

Kapoor Ltd
Income Statement
For the year ended 30 June, 2017

Particulars	Amount ($)	Total
Revenues:
Sales	265000
Other Income	0
Total Income (A)	265000
Expenses:
COGS	184000
Salary	41000
General expenses	17000
Advertisement	7000
Light and Power	12000
Rate and Taxes	4500
Total operating expenses (B)	265500
Net Income (A-B)	loss/-500

Recording of inventories in the books can be done through two different methods such as Perpetual and periodic inventory systems.

Perpetual Inventory System	Periodic Inventory System
In Perpetual inventory system, every transaction is continuously updated. In other words, transactions of sale and purchase, Cost of Goods Sold entries and stocks are continuously updated.	In Periodic inventory system, every transaction is not continuously updated. In other words, entries are updated at the end of accounting period through passing a closing entry.

Example:

Date	Particulars		Amount ($)
01-01-2017	Opening Stock	(150 units at $ 10 each)	1500
16-01-2017	Purchase	(80 units at $ 10 each)	800
22-01-2017	Issue	(110 units at $ 15 each)	1650
Periodic inventory system:
Particulars	Amount ($)	Particulars	Amount ($)

Opening Stock	1500	Sales	1650
Purchase	800	Closing Stock	1200
Gross profit	550
Total	2850	Total	2850

Perpetual inventory system:
Date	Particulars	Purchase	Issue	Closing Balance
No of units	Unit cost	Total cost	No of units	Unit cost	Total cost	No of units	Unit cost	Total cost
2011
01-01-2017	Opening Stock							150	10	1500
16-01-2017	Purchase	80	10	800				150	10	2300
								80	10
22-01-2017	Issue				110	15	1650	120	10	1200

Overview

The Spreadsheet is a computer application program in which records are organized in columns and rows. It is used generally for placing the numerical data and can also portray figures graphically. In today’s world there are many applications such as lotus 1_2_3 but Microsoft excel is most popular application.

The report contains the advantages and disadvantages of the worksheets in the accounting. Further the worksheet can also be used by the accounting expert to allocate the costs to different process of the corporation.

Body Framework

Advantages of worksheets in accounting:

The most basic benefit of using a spreadsheet is the ease of use.
Worksheet will help in maintaining the accounts accurate with the help of calculator function.
Using of Formulae’s and function keys in the worksheet will make the calculations accurate and faster as well as easier.
Facility of making notes and inserting comments in worksheet is also very helpful by elaborating data.
One workbook has the capacity to import many external data such as sales data, purchase data, banking data etc. to upkeep the accounting actions. This is one of the most important reason to use spreadsheet for the purpose of accounting.
Linking of data and or worksheets is also a benefit of using a spreadsheet. This helps in making budgets or cash flows etc.
Dispersed expenses such as salary or advertisement and track easily how much spent on these expenses on monthly basis by making table.
Automation of data in spreadsheet is also an advantage.

Disadvantages of worksheets:

There are chances that a spreadsheet can be corrupted or crashed and thus the whole of the data will get lost.
Hoarding of data is very easy and making changes or duplication of the data without any control is also a risk in using of spreadsheet.
Storage requirements is also get increased.
There is no requirement of having some special skills to operate spreadsheet, any unskilled labor can also operate spreadsheet very easily.

Conclusion

Thus it can be concluded that Using of Worksheet for accounting purposes as a tool is very useful because it brings the accurate results which is valuable to the company and their stakeholders. Further it brings productivity with the using of functions and formulae’s.

Statement of units:
Date	Purchase	Issue	Closing Balance
No of units	No of units	No of units
Oct-01			60
Oct-03	10		70
Oct-12	30		100
Oct-18	70		170
Oct-31			115
October		$ 25000

Negative Numbers

Closing Inventory at the end of the October were 115.

Date	Particulars	Purchase	Issue	Closing Balance
No of units	Unit cost	Total costs	No of units	Unit cost	Total costs	No of units	Unit cost	Total costs
Oct-01	Opening Balance							60	57	$ 3,420.00
Oct-03	Purchase	10	65	$ 650.00				60	57	$ 4,070.00
								10	65
Oct-12	Purchase	30	70	$ 2,100.00				60	57	$ 6,170.00
								10	65
								30	70
Oct-18	Purchase	70	72	$ 5,040.00				60	57	$ 11,210.00
								10	65
								30	70
								70	72
October	Issue				379	65.94	$ 25,000.00
Oct-31	Closing Balance							115	65.94	$ 7,583.24

Average unit Cost

$ 65.94

Cost of Closing Inventory as per Average Cost method were $ 7583.24.

Cost of Goods Sold = Opening Inventory + Purchases during the month – Closing Inventory

COGS = $3420 + $7790 – $7583.24

COGS = $ 3626.76.

First in First Out (FIFO):

Date	Particulars	Purchase	Issue	Closing Balance
No of units	Unit cost	Total costs	No of units	Unit cost	Total costs	No of units	Unit cost	Total costs
Oct-01	Opening Balance							60	57	3420
Oct-03	Purchase	10	65	650				60	57	4070
								10	65
Oct-12	Purchase	30	70	2100				60	57	6170
								10	65
								30	70
Oct-18	Purchase	70	72	5040				60	57	11210
								10	65
								30	70
								70	72
October	Issue				55	454.55	$ 25000
Oct-31	Closing Balance							5	57	$ 8,075.00
						10	65
						30	70
						70	72

Cost of Closing Inventory as per FIFO method were $ 8075.

Cost of Goods Sold = Opening Inventory + Purchases during the month – Closing Inventory

COGS = $3420 + $7790 – $8075

COGS = $3135.

Last in First Out (LIFO):

Date	Particulars	Purchase	Issue	Closing Balance
No of units	Unit cost	Total costs	No of units	Unit cost	Total costs	No of units	Unit cost	Total costs
Oct-01	Opening Balance							60	57	3420
Oct-03	Purchase	10	65	650				60	57	4070
								10	65
Oct-12	Purchase	30	70	2100				60	57	6170
								10	65
								30	70
Oct-18	Purchase	70	72	5040				60	57	11210
								10	65
								30	70
								70	72
October	Issue				55	454.55	25000
								60	57	$ 7,250.00 Oct-31
	Closing Balance							10	65
						30	70
						15	72

Cost of Closing Inventory as per FIFO method were $ 7250.

Cost of Goods Sold = Opening Inventory + Purchases during the month – Closing Inventory

COGS = $3420 + $7790 – $7250

COGS = $3920.

Normal View:

Mickey
Data
Particulars	$
Bank balance on 31 july	21453
Adjustments:
EFT Rent receipt	-600
EFT Insurance payment	300
NSF Cheque from customer	-2400
Note receivable	-2650
Bank error Cheque 1419	456
Bank service charges	65
Deposit in transit	-2345
outstanding cheque
Cheque no. 1420	1678
Cheque no. 1421	760
Cheque no. 1422	340
Cash account balance as of 31 july	17057

Formula View:

Mickey
Data
Particulars	$
Bank balance on 31 july	21453
Adjustments:
EFT Rent receipt	-600
EFT Insurance payment	300
NSF Cheque from customer	-2400
Note receivable	-2650
Bank error Cheque 1419	456
Bank service charges	65
Deposit in transit	-2345
outstanding cheque
Cheque no. 1420	1678
Cheque no. 1421	760
Cheque no. 1422	340
Cash account balance as of 31 july	=SUM(C6:C18)

Changes

Normal View:

Mickey
Data
Particulars	$
Bank balance on 31 july	21453
Adjustments:
EFT Rent receipt	-400
EFT Insurance payment	1300
NSF Cheque from customer	-2600
Note receivable	-2150
Bank error Cheque 1419	-44
Bank service charges	65
Deposit in transit	-3345
outstanding cheque
Cheque no. 1420	2178
Cheque no. 1421	560
Cheque no. 1422	40
Cash account balance as of 31 july	17057

Formula View:

=B3
Data
Particulars	$
Bank balance on 31 july	21453
Adjustments:
EFT Rent receipt	=-600+200
EFT Insurance payment	=300+1000
NSF Cheque from customer	=-2400-200
Note receivable	=-2650+500
Bank error Cheque 1419	=456-500
Bank service charges	65
Deposit in transit	=-2345-1000
outstanding cheque
Cheque no. 1420	=1678+500
Cheque no. 1421	=760-200
Cheque no. 1422	=340-300
Cash account balance as of 31 july	=SUM(C26:C38)

Credit sales

Particulars

Debit ($)

Credit ($)

Accounts Receivable Dr.

To Credit Sales

100000

The collection of the part of the amount owing

Particulars

Debit ($)

Credit ($)

Cash Account Dr.

To Accounts Receivable

80200

8200

The write off of accounts receivable

Particulars

Debit ($)

Credit ($)

Allowance for doubtful amount Dr.

To Accounts Receivable

15000

The reinstatement of an amount written off (Amount in $)

Particulars

Debit

Credit

Accounts Receivable Dr.

To Allowance for doubtful amount

15000

The collection in full of the amount owing (Amount in $)

Particulars

Debit

Credit

Cash Account Dr.

To Accounts Receivable

19800

T-Account of Accounts receivable:

Particulars	Amount in $	Particulars	Amount in $
To credit sales	100000	By balance b/d	–
By Allowance for doubtful amount	15000	By cash	80200
To balance c/d	–	By Allowance for doubtful amount	15000
		By cash	19800

2 methods for estimating bad debts:

Percentage of Accounts receivable method:

Bad debt is considered as a % of accounts receivable balance.

Example:

Closing balance of Accounts receivable = $1,90,000

It is given that 1% of total Accounts receivable is uncollectible.

Bad Debt amount = $1,90,000 * 1% = $ 1,900

Journal:

Particulars

Debit ($)

Credit ($)

Bad debt Expense A/c Dr.

To Allowance for bad debt A/c

(Estimate of bad debts)

1900

Percentage of Credit sales method:

Bad debt is calculated as a % of credit sales.

Example:

Credit Sales = $ 4,00,000

It is given that 1% of credit sales is uncollectible.

Bad Debt Amount = $ 4,00,000 * 1% = $4,000

Journal:

Particulars

Debit ($)

Credit ($)

Bad debt Expense A/c Dr.

To Allowance for bad debt A/c

(Estimate of bad debts)

4,000

E-commerce industry is increasing its popularity day by day. Every enterprise whether small, medium or large trying to increase its revenue through retailing online. Also with the help of e-business transaction costs for material acquisition and inventory storage costs reduces to the minimum. This happens only when enterprises have personal computers, proper internet connection etc. The market of e-business has really reshaped and consumers can easily purchase products without physically going to the market for the same. At present there are almost online market for many industries for example medicines, textile, grocery etc. All this happened only because the usage of technology is very high and it is very easy to use and time saving. In addition to above, we can also say that e-business increases the sales with increasing in gross margins.

The example used for the online retailing is EBAY.

The EBAY is a multinational ninth largest e-commerce publicly listed company by revenue and traded in NASDAQ: EBAY. The company is California based, US. The company sales its products to consumers through online source either from website (ebay.com) or from the application. The EBAY operated worldwide in nearly 30 nations and the buyers use its website freely.

Financials of Ebay (2017) are as follows:

Revenue	USD 9.567 billion
Stock price	USD 37.80 (7^th May, 2018)

Thus, having said that online retailing with more and more e-commerce businesses will flourish in coming years.

Scenario:

AMP Ltd. Sells medicines to PAM Ltd. for $ 15,000 (credit sales) payment due in 30 days. After 30 days of non-payment, AMP ltd accepted the note receivable from the PAM ltd for $ 15,000. It has been established that, PAM ltd had postponed the payment of $9, 000 and balance amount $6,000 got dishonor.

Journal entries:

(1). Recording of credit sales:

Particulars

Debit ($)

Credit ($)

Notes Receivable A/c Dr.

Credit Sales Revenue A/c

15,000

(2). Account receivable converted to Notes Receivable:

Particulars

Debit ($)

Credit ($)

Notes Receivable A/c Dr.

Account Receivable A/c

15,000

(3). PAM ltd paid $9,000 after the due date:

Particulars

Debit ($)

Credit ($)

Account Receivable A/c Dr.

Notes Receivable A/c

9,000

(4). $6, 000 got dishonor. Journal for the dishonor of the note:

Particulars

Debit ($)

Credit ($)

Allowance for doubtful amount Dr.

Notes Receivable A/c

9,000

Note receivable Account:

Particulars	Amount in $	Particulars	Amount in $
To balance b/d	–	By Account receivable	9,000
To Account receivable	15,000	By Allowance for doubtful amount	9,000
To credit sales revenue	15,000	To balance c/d	12,000
Total	30,000	Total	30,000

Turn in your highest-quality paper
Get a qualified writer to help you with

“ Accounting Spreadsheets: Examples And Concepts ”

Get high-quality paper

NEW! AI matching with writer

Order an Essay Now & Get These Features For Free:

Turnitin Report

Formatting

Title Page

Citation

Outline

Place an Order