People’s Choice Bank Case Study

Subject: Case Studies
Pages: 4
Words: 710
Reading time:
4 min
Study level: College

Introduction

The present report was commissioned by the president of People’s Choice Bank. The People’s Choice Bank is a small community bank with three local branches in Blue Ash, Delhi, and Anderson Hills. The purpose of the present report is to analyze the cash flow of People’s Choice Bank using the database of transactions in August. The data was analyzed using pivot tables in Microsoft Excel 2016.

Summary of Cashflow by Branch

This section summarizes cash flow data from three divisions of People’s Choice Bank in August. The analyses revealed that the Anderson Hills branch had the highest cash flow among the three branches, while the Blue Ash branch had the lowest cash flow. In particular, Anderson Hills had a net cash flow of 1,877,970, which accounted for 47.57 % of the bank’s cash flow, while the Delhi branch had accounted for 28.13% of cash flow, and the Blue Ash branch accounted for 24.3% of cashflow. This information is summarized in Figure 1 below.

Cashflow percentage by branch.
Figure 1. Cashflow percentage by branch.

The maximum and minimum cash flow from a single transaction were similar among all the branches. The detailed results of the analysis are demonstrated in Table 1 below.

Table 1. Cashflow analysis by branch.

Branch Sum of Cash Flow Percentage of Cash Flow2 Min of Cash Flow3 Max of Cash Flow4
Anderson Hills 1877970 47.57% -3825 59400
Blue Ash 959325 24.30% -3810 59600
Delhi 1110517 28.13% -3800 59600
Grand Total 3947812 100.00% -3825 59600

Efficiency Analysis

This section of the report aims at analyzing the efficiency of all the branches. Efficiency is understood as an average of cash flow for a single transaction. To start the analysis of efficiency, the number of transactions by branch was analyzed. The results revealed that Anderson Hill had the highest number of transactions in August (51.45%), while Delhi had the lowest number of transactions (21.77%). The percentage of transactions by the branch is provided in Figure 2 below.

Transaction percentage by branch.
Figure 2. Transaction percentage by branch.

The distribution of the number of transactions was inconsistent with the distribution of cash flow. In particular, while 51.45% of transactions were registered in Anderson Hill, only 47.57% of cash flow was attributed to the branch. Similarly, the cash flow of Blue Ash (24.3%) was disproportionate with the number of transactions (26.77%). Finally, the percentage of transactions attributed to Delhi was 21.77%, while the branch had 28.13% of cash flow. Further analysis demonstrated that while Anderson Hills and Blue Ash had similar cashflows per transaction, Delhi was outperforming the other two branches by 40%. This implies that Delhi’s efficiency was the highest among the three branches. The details about the analysis are provided in Table 2 below.

Table 2. Efficiency analysis by branch.

Branch % of Transactions # of Transactions Cash Flow Percentage Cash Flow Sum Cashflow per transaction
Anderson Hills 51.45% 319 47.57% 1877970 5887
Blue Ash 26.77% 166 24.30% 959325 5779
Delhi 21.77% 135 28.13% 1110517 8226

Historical analysis was used to understand the tendency in the cash flow of the company. Thus, a graph was created to observe the daily fluctuations in net cash flow in August. A trendline was added to the graph to make predictions for future changes in the cash flow. The standard method for constructing a trendline in Excel is linear regression analysis, which is one of the most frequently used approaches to forecasting cash flow (Shields, 2018). The historical data is visualized in Figure 3 below.

Historical analysis of cashflow.
Figure 3. Historical analysis of cashflow.

Observations of Figure 3 led to two conclusions. First, cash flow was always the highest on Mondays, while Saturdays were not associated with significant changes in cash balance. Second, the overall trend in the daily cashflows was negative. In other words, if the current tendency remains unchanged, average daily cashflows will continue to decrease. Cashflow by date is presented in Table 3 below.

Table 3. Cashflow by date.

Sum of Cash Flow
1-Aug 272022
2-Aug 34835
4-Aug 242470
5-Aug 116670
6-Aug 130080
7-Aug 401950
8-Aug 83910
9-Aug 55630
11-Aug 139390
12-Aug 65060
13-Aug 234350
14-Aug 95370
15-Aug 122090
16-Aug 9440
18-Aug 167280
19-Aug 419870
20-Aug 208140
21-Aug 126500
22-Aug 222770
23-Aug 55150
25-Aug 215590
26-Aug 130340
27-Aug 147370
28-Aug 53920
29-Aug 28600
30-Aug 169015
Grand Total 3947812

References

Shields, G. (2018). Business valuation: The ultimate guide to business valuation for beginners, including how to value a business through financial valuation methods. CreateSpace Independent Publishing Platform.