Accounts Receivable Management ✅

1. Objective: To have the optimal amount of A/R outstanding & the optimal amount of bad debts.
(a) This balance requires trade-off between BENEFITS of Credit Sales (e.g more sales) and the COSTS of AR (e.g collection, interest, bad debt). Thus, firm should extend credit UNTIL marginal benefit is ZERO

2. Objective of Receivables Management is to maximize A/R Turnover to shorten average time receivables are held.
(a) AR Turnover Ratio = Net Credit Sales / Avg AR
(b) Number of Days of Receivables = 360 / AR Turnover Ratio [Avg # of days to collect AR, may compute as Avg AR / Avg daily sales]
(c) Avg Gross Receivables = Avg daily sales * Avg collection period.

Cash Management ✅

1. Cash Budget.. is a cash details projected the receipts & disbursement
○ Based on projected sales & credit terms, collection percentage, and estimated purchases & payment terms.
○ Cash outflows is budgeted based on sales level,
○ Budgets must be for a specified period of time.. the units of time must be short enough to assure that all cash payments can be met.

2. Reasons of holding cash
    a- Medium of exchange
    b- Precautionary measure
    c- for Speculation
    d- Compensating Balance

3. Cash inflows should be expedited

4. Slowing Cash Disbursements to increase available cash.
a- Payments should be made within discount periods (if cost of not taking a discount > firm’s cost of capital)
• cost of not taking discount is: [360 ÷ (T. pay period – disc period)] x [ disc% ÷ (100% - disc%)]
Example: 2/10 net 30 (payment period 30 days & discount period 10 days & discount rate 2%)
Calc1: 2/10net30 result in (360 ÷ 20) x (2 ÷ 98) = 36.70% annualized interest (cost of not taking disc.)
Calc2: More accurate calculation (considering compounding effects):
            number of annual payments = 360 ÷ (30-10) = 18
            annual effective rate = [1.0 + (2 ÷ 98)]18  – 1.0 = 43.80%
b- Compensating Balances..
Effective interest rate requiring compensation balance = Total Interest Cost ÷ Total. Principal
d- Zero-balancing Checking Accounts..

5. Cash Amount to Keep on Hand, determined by Cost-Benefit Analysis.
a- reducing [avg cash * interest rate] is the benefit.
b- cost of insufficient cash: incremental personnel cost, lost discounts, lost goodwill
c- Economic Order Quantity (EOQ): is a model for cash management with the following assumptions:
• known demand of cash, • given carrying (interest) cost, • cost of liquidation of other assets.

6. Excess Cash should be invested with high return & little risk. Such as:
a- Treasury Bills: Short-term govt. debt securities guaranteed by US govt & exempted from state & local taxes. It is sold on discount.
b- Certificate of Deposit ‘CD’: savings deposit that cannot be withdrawn before maturity without high penalty.
c- Money-Market Accounts: as checking accounts but pay higher interest.
d- High-Grade Commercial Paper: unsecured & short-term notes issued by large companies with very good credit risk. Commercial paper is higher return than CDs cuz of higher risk.

Financial Ratios to Evaluate Working Capital Management

● SOLVENCY RATIOS:

1. Working Capital: provides a quantification of readily available resources. Ratio not to be less than 2. Aggressive policy: Low ratio, Conservative policy: High ratio.

= current assets - current liabilities           

2. Current Ratio: provides a measure of short-term debt-paying ability.

= current assets / current liabilities            

3. Quick Ratio 'Acid-Test': provides a stricter measure of short-term debt-paying ability, measures immediate short-term liquidity

= (cash, temp invest, marketable securities, net receivables) / current liabilities             

4. Current Cash Debt Coverage Ratio: measures a company's ability to pay off its current liabilities in a given year from its operations.

= net cash by operating activities / avg current liabilities

● ACTIVITY RATIOS:

1. Accounts Receivable Turnover: provides a measure of the relative size of the AR balance and the effectiveness of credit policies.

= net credit sales / average accounts receivable 'net'.           

2. Inventory Turnover: measures the relative size of your inventory. High ratio means: firm not hold excessive stock of unproductive inventories & inventory is marketable.

= cost of goods sold / average inventory 

3. Asset Turnover: measures how efficiently all assets are used to generate sales

= net sales / average total assets

4. Average Collection Period: measures the average time taken to collect your A/R

= 360 / accounts receivable turnover       

5. Number of Days' Sales in Inventory: provides an idea of how long it takes to sell the entire inventory. Lower number is better.

= 360 / inventory turnover

6. Operating Cycle: the time between inventory acquisition and cash of sales receipt

      = number of days of inventory + number of days of receivables

Working Capital Financing Policies

Working Capital Financing

Two policies:

Conservative working capital Policy: minimize liquidity risk by increasing working capital, result in company foregoes higher returns from using additional working capital to acquire long-term assets.

Aggressive working capital policy: reduce current ratio, accept higher risk of short-term cash flow.

EXCEL Shortcuts - 10: Extended Selection

 To include / exclude more or less cells in a selected range...

Extended selection


EXCEL Shortcuts - 9: Navigation inside selected Range

When you have multiple cells selected you can perform tasks like copying down/right (Ctrl+D or Ctrl+R) from the active cell.

Active cell




EXCEL Shortcuts - 8: Group and Hide

 

Group & Hide


EXCEL Shortcuts - 7: Rows and Columns

 

Rows & Columns


EXCEL Shortcuts - 6: Cell and Font Formatting

..

Cell & Font formatting


EXCEL Shortcuts - 5: Number Formatting

To quickly apply number formats to a cell or range. Use the number keys above the letters on the keyboard to press the shortcuts.

Number format


EXCEL Shortcuts - 4: Editing Text

These shortcuts can be used in all areas of Excel where you are editing text.

Edit text




EXCEL Shortcuts - 3: Entering Data

 

Entering data

EXCEL Shortcuts - 2: Writing Formulas


Writing formula


EXCEL Shortcuts - 1: Navigating the Worksheet

 If you ever find yourself spending more time scrolling the worksheet with the mouse, then these shortcuts will save you a lot of time.

Navigating the worksheet



CEILING Function to round-up numbers

 Ceiling(): returns number rounded up, away from zero, to the nearest multiple of significance.

Syntax: CEILING(number, significance)

Ceiling.Math(): rounds a number up to the nearest integer or to the nearest multiple of significance.

Syntax: CEILING.MATH(number, [significance], [mode])


Ceiling function


How to represent your data graphically inside the cell??

Here, we can quickly represent our data graphically inside the cell, without charts.

Select your range → Conditional formatting → Data Bars





How to convert score into grade??

This is an important trick, using LOOKUP and TEXTJOIN; we can produce this awesome sentence down including the student name/ his score/ and his grade



How to make a list or sentence from different cells??

 TEXTJOIN function doing great job on this regard, check this example; we joined 3 cells in one sentence. with the same logic you can make a list from a group of cells.

TEXTJOIN()


How to Insert SPACE before the nth last character in a string??

 Here, we are using LEN function to locate the place to REPLACE with a space " "

Check the below example and formula

Insert space inside a sentence


20 Important Excel-Shortcuts You've to Memorize


excel short cuts






How to Highlight the Blank Cells Inside a Range??

 Mark area with missing values in ur table

Home Menu >> Find >> Goto Special >> Check "Blanks"

Now, all blank cells are selected, highlight the current cell, then

Ctrl+Enter… Done 👍



Inspirational..

 Every achievement starts with the decision to try 👍👍







How to Extract the Integer Portion & Decimal Portion from a Number??

 To extract the integer portion, use INT() function

To extract the decimal portion, use MOD() function

as explained below..




How to Find the Most Frequently Occurring Word??

Here we used a mix of three functions, INDEX - MODE - MATCH

Note1: You can use MODE function to find the most frequently occurring number.

Note2: the MODE function only works with numbers.






How to Use Emoji Inside Your Formula??

 It is very simple, just follow the next few steps:

1- We've a column of scores, we need to insert our impression on the next column.

2- Let's use IF function ... =IF(A1>0,"👍","👎")

3- While you type this formula, just press Win button + ; button to bring the emoji list

4- If you've any query? just drop your notice down





How to Put the Date Between Quotes??

Date between double quotes:

1. Right click
2. Format cells
3. Custom
4. Type this custom Format: \"dd-mmm-yyyy\"
5. Ok



How to Check for The Expiry Date, and Highlight It

 Date Transactions & Conditional Format using IF

As explained above.. please not the following points:

1) Expiry date calculated as Issuance date (-) 12 months.
(we adjust it by the function EOMONTH() end-of-month)
2) Days left to expiry = the expiry date (-) today
3) Status of the license, showing if it is expired or still running, using simple IF() function
(we used conditional formatting here to change its color to yellow when expired)
4) Action needed, using VLookup() function




How to Calculate Age?? The most simple method..

 A simple example of date calculation (age). we calculate 3 separate cells (for years, months, and days) using DATEDIF() function, then we grouped them it into one sentence as shown using "&"



Accounts Receivable Management ✅

1. Objective: To have the optimal amount of A/R outstanding & the optimal amount of bad debts. (a) This balance requires trade-off betwe...

POPULAR POSTS