Projected Accounts Receivable
The projected values for Accounts Receivable are calculated using the projected Days ratio. This ratio can be quickly adjusted for all periods using the Projection Dials tab in the control panel of the Projection. To change the rate on a period-by-period basis, you can type in the ratios in the Balance Sheet Assumptions section of the Projection tab.
In general, the formula for Accounts Receivable is:
= Accounts Receivable Days / 365 * Sales
To find the figure for the first month of annual Sales used in the Accounts Receivable calculation, this is the formula:
= first month of projected Sales + 11 months of prior sales
To find the 11 months of prior sales, take the previous year's sales times 11/12.
Example
Let's say a company has two historical annual periods of 12/31/2017 and 12/31/2018. It has $1,000,000 in sales for Year 1 and $1,200,000 in sales for Year 2. It also has 30 Accounts Receivable Days projected. The first projected month, 1/31/2019, has a sales amount of $108,603 (as shown below).
To find how the projected Accounts Receivable is calculated, do the following:
Multiply the last historical period by the number of months of prior sales remaining over an annual period, in this case 11 months out of 12:
$1,200,000(11/12) = $1,100,000
Then add the first projected month's sales to that number: $1,100,000 + $108,603 = $1,208,603
Then, use the following formula:
= Accounts Receivable Days / 365 * Sales
= 30 / 365 * $1,208,603 = $99,337
Projected Inventory
The projected values for Inventory are calculated using the projected Days ratio, and it is very similar to the Accounts Receivable calculation. This ratio can be adjusted for all periods by using the Projection Dials tab of the control panel. To change the rate on a period-by-period basis, you can also type in the ratios in the Balance Sheet Assumptions section of the Projection tab.
The formula for Inventory is:
= Inventory Days / 365 * Cost of Sales
*Note: If Cost of Sales is set to 0, the formula for projected Inventory becomes Inventory Days / 365 * Sales instead.
Example
Let's say a company has 20 Inventory Days and an annual Cost of Sales (COGS) of $300,000.
To find the figure for the first month of annual COGS used in the Inventory calculation, this is the formula:
= first month of projected COGS + 11 months of prior COGS
To find the 11 months of prior COGS, take the previous year's COGS times 11/12.
$300,000(11/12) = $275,000
Then add the first projected month's COGS to that number: $275,000 + $27,151 = $302,151
Now use the formula
= Inventory Days / 365 * Cost of Sales
20 / 365 * $302,151 = $16,556
Note: The calculation for Inventory is very similar to Accounts Payable as well. You can swap out Inventory Days for Accounts Payable Days to get its formula.
= Accounts Payable Days / 365 * Cost of Sales