ACC 566 Financial Analysis Assignment Solution - Complete Analysis

This document presents a comprehensive financial analysis assignment solution for the ACC 566 course. The assignment covers various aspects of financial analysis, including the use of Excel for calculations and reporting, inventory management systems (perpetual and periodic), different inventory costing methods (FIFO, LIFO, and Average), bank reconciliation processes, journal entries related to accounts receivable and bad debts (direct write-off and allowance methods), the impact of online retailing, and the application of a balance scorecard. The solution includes detailed explanations, examples, and calculations to provide a thorough understanding of the concepts. The assignment also includes the analysis of a company's financial data to assess the performance and guide the company to achieve its goals.

Running head: FINANCIAL ANALYSIS
Subject Code: ACC 566
Subject: Financial Analysis
Name of the Student:
Assignment Task Number 2
Number of Questions Attempted: 13
Number of Question Not Attempted: 0

FINANCIAL ANALYSIS
Table of Contents
Question 1..................................................................................................................................2
Question 2..................................................................................................................................2
Question 3..................................................................................................................................3
Question 4..................................................................................................................................3
Question 5..................................................................................................................................4
Question 6..................................................................................................................................5
Question 7..................................................................................................................................6
Question 8..................................................................................................................................9
Question 9................................................................................................................................13
Question 10..............................................................................................................................14
Question 11..............................................................................................................................14
Question 12..............................................................................................................................15
Question 13..............................................................................................................................15
Reference..................................................................................................................................19
NAME
STUDENT NO Page 1

FINANCIAL ANALYSIS
Question 1
One of the main reasons for replacing cell references with names is to describe what that
particular cell wants to display. Naming a particular cell allows the users to identify the cell
with what calculations it is associated with and the technique is used for the basic
convenience of the user. One instance of replacing cell references with names is briefly
illustrated as follows:
Particulars Amount (in $)
EBIT 100,000
Interest 10000
Tax 30000
EAT/PAT 60000
Figure 1: (Chart showing Calculation of EAT/PAT)
Source: (Author Made)
Question 2
In most cases any negative figures which arises in any calculations are shown in
brackets or is highlighted. The main reason for this is to ensure that the user of the excel sheet
is aware of the fact that the figure is negative whenever the user see the figures in brackets.
The main purpose of putting brackets or highlighting the figure is basic identification and
differentiation of the negative figures from the positive ones.
NAME
STUDENT NO Page 2

FINANCIAL ANALYSIS
Figure: (Excel Sheet showing negative figure)
Source: (Ahmed, 2015)
Question 3
The accountant main responsibility is effective calculation and presentation of financial data.
In an excel sheet, accountant normally inputs data in another area and for reporting purposes
another sheet or area is used. This is mainly done in order to keep the calculations and the
reporting part from getting mixed up and moreover for the sake of simplicity of the whole
reporting framework. If any of the calculations and reporting area are mixed up than mistakes
or miscalculations are bound to happen. A spreadsheet generated instance has been depicted
as follows:
Figure 3: (Separate areas of data entry and report in spreadsheet)
Source: (Bogale, 2013)
Question 4
IF function is one of the most popular functions which is used in excel. IF function
provide the users with an opportunity to make comparison among values in an excel
worksheet (Liengme, 2015). IF function a technique which helps to check whether a
condition is accomplished or not. The function returns a single value if it is true and another
value if it is false. An instance of spreadsheet generated IF function is depicted as follows:
NAME
STUDENT NO Page 3

FINANCIAL ANALYSIS
Figure 4: (Excel Sheet showing IF function)
Source: (Okeniyi & Okeniyi, 2012)
Question 5
Perpetual Inventory System Periodic Inventory System
This system traces every single movement of
inventory as and when they arise. The system
analyses the book records for keeping track of
inventories (Berlemann & Wesselhöft, 2014).
As per this system of inventory the recoding
method of inventory involves updating the
inventory records at periodic intervals. The
system analyses by using physical verification
of inventories (Qin et al. 2014).
In this system the inventory and cost of good
sold are continuously updated
Under the periodic inventory system, there is no
need for continuous updating however both
inventory and cost of good sold are updated at
the end of the year.
In the perpetual inventory system there is no
need for closing entries to be recorded.
In the periodic inventory system as the system
updates inventories and cost of good sold
therefore the system requires closing entries to
be recorded.
NAME
STUDENT NO Page 4

FINANCIAL ANALYSIS
Perpetual Inventory System
Date Particulars Debit Credit
Amount (in $) Amount (in $)
01-01-2017 Purchase Account……Dr 10,000
To Bank Account 10,000
05-01-2017 Bank Account……….Dr 15,000
To Sales Account 15,000
Periodic Inventory System
Date Particulars Debit Credit
Amount (in $) Amount (in $)
01-01-2017 Inventory Account…….Dr 4,000
To Bank Account 4,000
05-01-2017 Bank Account……Dr 3,900
To Sales Account 3,900
07-02-2017 Cost of Goods Sold Account……Dr 3,000
To Inventory Account 3,000
Question 6
Any individual who is starting off with accounting background will be definitely
using excel worksheets for the various calculations. Spreadsheet helps in keeping tracks of
various data such as accounts of small as well as large businesses. The owners of small
business are new to accounting and may not be able to maintain proper records, spreadsheet
helps offering easy solution to such a problem. Spreadsheet helps in commonly using the
analysis of money which is spent and income that has been received. Spreadsheet enables an
individual to split the amount of cash that business has received and received (Zhu, 2014).
Another advantage of using the spreadsheet is that business can link the worksheets together
and it is very helpful in generating cash flow of a company. However a disadvantage which
arises while using spreadsheets is that such spreadsheets can be manipulated easily and thus
there is an increased chance of occurrence of frauds.
NAME
STUDENT NO Page 5

FINANCIAL ANALYSIS
Question 7
Average Method
Date
Purchase Cost of Sales Balance Inventory
Unit
Cos
t
per
Uni
t (in
$)
Total
Amount
(in $) Unit
Cost per
Unit (in
$)
Total
Amoun
t (in $)
Uni
t
Cos
t
per
Uni
t (in
$)
Total
Amoun
t (in $)
01-
01-
2017 80 57 4,560
03-
01-
2017 10 65 650 80 57 4,560
10 65 650
90 58 5,210
05-
01-
2017 30 70 2,100 90 58 5,210
30 70 2,100
120 61 7,310
08-
01-
2017 70 72 5,040 120 61 7,310
70 72 5,040
190 65 12,350
31-
01-
2017 75 65
4,8
75 115 65 7,475
FIFO Method
Date
Purchase Cost of Goods Sold Balance Inventory
Unit
Cos
t
per
Uni
t (in
$)
Total
Amount
(in $) Unit
Cost per
Unit (in
$)
Total
Amoun
t (in $)
Uni
t
Cos
t
per
Uni
t (in
$)
Total
Amoun
t (in $)
01-
NAME
STUDENT NO Page 6

FINANCIAL ANALYSIS
01-
2017 80 57 4,560
03-
01-
2017 10 65 650 80 57 4,560
10 65 650
05-
01-
2017 30 70 2,100 80 57 4,560
10 65 650
30 70 2,100
08-
01-
2017 70 72 5,040 80 57 4,560
10 65 650
30 70 2,100
70 72 5,040
31-
10-
2016 75 57
4,2
75 5 57 285
10 65 650
30 70 2,100
70 72 5,040
31-
10-
2016 75
4,2
75 115 8,075
LIFO Method
Date
Purchase Cost of Goods Sold Balance Inventory
Unit
Cos
t
per
Uni
t (in
$)
Total
Amount
(in $) Unit
Cost per
Unit (in
$)
Total
Amoun
t (in $)
Uni
t
Cos
t
per
Uni
t (in
$)
Total
Amoun
t (in $)
01-
01- 80 57 4,560
NAME
STUDENT NO Page 7

FINANCIAL ANALYSIS
2017
03-
01-
2017 10 65 650 80 57 4,560
10 65 650
05-
01-
2017 30 70 2,100 80 57 4,560
10 65 650
30 70 2,100
08-
01-
2017 70 72 5,040 80 57 4,560
10 65 650
30 70 2,100
70 72 5,040
31-
10-
2016 120 70
8,4
00 80 57 4,560
70 72
5,0
40 10 65 650
(90) 70 (6,300)
- 72 -
Question 8
Normal View
Bank Reconciliation
31-07-2017
Account Description General Cash Prepared
By
GL Account Number Reviewed
By
NAME
STUDENT NO Page 8

FINANCIAL ANALYSIS
Bank Account Number
Balance Per Bank Statement 24,009.00
Add: EFT Rent Received 700
EFT Insurnace payment 400
Deposits in Transit 4567.00
NFS Cheque from customers 2300.00
Bank Service charges 78.00
Note Receivables 2345.00
9,690.00
Subtract:
Checks Issued Not Cleared 1789.00
Checks Issued Not Cleared 650.00
Checks Issued Not Cleared 320.00
(2,759.00)
Other:
A
Book error cheque 78.00
78.00
Balance Per Bank Ledger 31,018.00
Bank Reconciliation
31-07-2017
Account Description General Cash Prepare
d By
GL Account
Number
Reviewe
d By
Bank Account
Number
Balance Per Bank
Statement 25,000.00
Add: EFT Rent Received 750
EFT Insurnace 500
NAME
STUDENT NO Page 9

FINANCIAL ANALYSIS
payment
Deposits in Transit 6587.00
NFS Cheque from
customers
2500.00
Bank Service
charges
90.00
Note Receivables 3545.00
13,222.00
Subtract:
Checks Issued Not
Cleared
2100.00
Checks Issued Not
Cleared
700.00
Checks Issued Not
Cleared
300.00
(3,100.00)
Other:
B
Book error cheque 95.00
95.00
Balance Per Bank
Ledger 35,217.00
NAME
STUDENT NO Page 10

FINANCIAL ANALYSIS
Bank Reconciliation Statement Formula View:
Bank Reconciliation
42947
Account
Description
General Cash Prepa
red
By
GL Account
Number
Revie
wed
By
Bank Account
Number
Balance Per
Bank
Statement
24009
Add: EFT Rent
Received
700
EFT Insurnace
payment
400
Deposits in
Transit
4567
NFS Cheque
from
customers
2300
Bank Service
charges
78
Note
Receivables
2345
=SUM(OFFSET(J19,-1,-
1,MATCH("Add:",E$1:E$104,0)-ROW()+1))
Subtract:
Checks Issued
Not Cleared
1789
Checks Issued
Not Cleared
650
Checks Issued
Not Cleared
320
=-SUM(OFFSET(J24,-1,-
1,MATCH("Subtract:",E$1:E$104,0)-ROW()
+1))
Other:
A
Book error
cheque
78
=SUM(OFFSET(J28,-1,-
1,MATCH("Other:",E$1:E$104,0)-ROW()+1))
NAME
STUDENT NO Page 11