Stock Accounting Dashboard

• Real-time stock valuation tracking with FIFO, AVCO, Standard Cost

• Analyze Cost of Goods Sold (COGS) by product and period

• Calculate inventory carrying costs and storage costs

• Compare book value vs market value of inventory

• Track stock adjustments and P&L impact

• Analyze gross margin by product category

• Dashboard for inventory turnover costs and holding costs

• Trend charts for stock value and COGS by month

• Multi-dimensional pivot tables by product, location, cost center

• Export Excel reports with detailed valuation and journal entries

• Stock Valuation: Inventory value by accounting method

• FIFO (First In First Out): First in, first out costing

• AVCO (Average Cost): Weighted average cost

• Standard Cost: Predetermined standard cost

• COGS (Cost of Goods Sold): Cost of goods sold

• Inventory Carrying Cost: Storage costs (storage, insurance, depreciation)

• Stock Adjustment: Inventory adjustment and accounting impact

• Inventory Turnover Cost: Costs related to inventory turnover

• Gross Margin: Gross profit = Revenue - COGS

• Working Capital: Working capital tied up in inventory

1. Go to Spreadsheet > Dashboards, click Create

2. Select "Stock Accounting Analysis" template or create from scratch

3. Add Pivot Table: Insert > Pivot, select Stock Valuation data

4. Configure Pivot: Rows = Product, Columns = Month, Values = Valuation

5. Add 2nd Pivot: Account Move Lines, filter COGS accounts

6. Create charts: Insert > Chart, stock value and COGS trends

7. Add KPI cards: Total stock value, COGS, Gross margin, Turnover cost

8. Set up filters: Date range, Product category, Costing method

9. Add formulas: Gross margin %, Inventory days, Carrying cost %

10. Save dashboard and share with finance team

• Line Chart: Stock valuation and COGS trends by month

• Waterfall Chart: Stock value changes analysis (opening + purchases - COGS = closing)

• Bar Chart: Compare COGS by product category

• Stacked Area: Stock value breakdown by location or category

• Combo Chart: Stock value (bars) and Gross margin % (line)

• Pie Chart: Stock value distribution by product category

• Heatmap: COGS by product and month

• Gauge Chart: Inventory turnover ratio vs target

• Total Stock Value: Total inventory value by accounting method

• COGS: Cost of goods sold in period

• Gross Margin: Gross profit = Revenue - COGS

• Gross Margin %: (Gross Margin / Revenue) × 100%

• Inventory Turnover Ratio: COGS / Average Inventory Value

• Days Inventory Outstanding: 365 / Inventory Turnover Ratio

• Carrying Cost: Storage + Insurance + Depreciation + Opportunity Cost

• Carrying Cost %: (Carrying Cost / Average Inventory) × 100%

• Stock Adjustment Impact: Total adjustments affecting P&L

• Working Capital Tied: Capital tied up in inventory

• Manufacturing: Track raw materials, WIP, finished goods valuation

• Retail chain: Analyze COGS and gross margin per store

• Distributor: Optimize inventory levels to reduce carrying costs

• CFO: Monthly closing report with stock valuation and COGS

• Controller: Reconcile stock value between inventory and GL accounts

• Cost Accountant: Analyze variances between standard vs actual costs

• Auditor: Verify stock valuation methods and journal entries

• Operations: Identify slow-moving inventory with high carrying costs

• FIFO: Suitable when prices rising, lower COGS, higher profit

• AVCO: Smooth out price fluctuations, stable COGS

• Standard Cost: Easy to manage, detect variances, suitable for manufacturing

• Real-time valuation: Update value with each transaction

• Periodic valuation: Calculate at period end, simpler

• Landed cost: Include freight, customs, insurance in product cost

• Impact on P&L: FIFO vs AVCO can differ significantly with price volatility

• Tax implications: Choose method suitable for tax regulations

• Multiple data sources: Stock Valuation, Account Moves, Product Costs

• Calculated fields: Gross Margin % = (Revenue - COGS) / Revenue

• Conditional formatting: Red for negative margins, Green for >30%

• Drill-down: Click product to view detailed cost breakdown

• Grouping: Group by product category, cost center, location

• Sorting: Sort by COGS or margin descending

• Filters: Filter by date, costing method, product type

• Subtotals: Display totals by category and period

• =ODOO.PIVOT("Stock Valuation", "Value", "Product", "Month")

• =ODOO.PIVOT("Account Moves", "Debit", "Account", "COGS")

• =(Revenue - COGS) / Revenue - Calculate Gross Margin %

• =COGS / ((Opening_Stock + Closing_Stock) / 2) - Turnover Ratio

• =365 / Turnover_Ratio - Days Inventory Outstanding

• =Stock_Value × Carrying_Cost_Rate - Calculate Carrying Cost

• =IF(Margin_Percent < 0.2, "Low", "OK") - Low margin alert

• =SUMIF(Adjustments, Type, "Loss") - Total inventory losses

• Inventory: Get stock quantities, movements, and valuations

• Accounting: Connect with GL accounts, journal entries, P&L

• Purchase: Track purchase prices and landed costs

• Sales: Get revenue data to calculate gross margin

• Manufacturing: Track production costs and WIP valuation

• Cost Center: Allocate inventory costs by departments

• Analytic Accounting: Analyze profitability by projects

• Reporting: Export data for financial statements

• Stock vs GL reconciliation: Compare stock value with balance sheet

• COGS verification: Reconcile COGS with sales and stock movements

• Adjustment tracking: Review all stock adjustments and reasons

• Variance analysis: Compare standard vs actual costs

• Period-end closing: Verify stock valuation before closing books

• Audit trail: Track changes in costing methods and prices

• Physical count: Reconcile system vs physical inventory

• Write-off analysis: Review obsolete and damaged inventory

• Consistent costing method: Don't change method frequently

• Regular reconciliation: Reconcile stock vs GL monthly

• Document adjustments: Note reasons for all stock adjustments

• Review margins: Analyze gross margin trends to identify issues

• Monitor carrying costs: Optimize inventory levels to reduce costs

• Landed cost allocation: Include all costs in product valuation

• Periodic reviews: Review costing accuracy quarterly

• Segregation of duties: Separate stock entry and adjustment approval

• Backup data: Export valuation reports before major changes

• Training: Ensure team understands impact of costing methods

• Stock value doesn't match GL: Run stock valuation reconciliation report

• Incorrect COGS: Check costing method and product costs

• Negative stock value: Review transactions and fix negative quantities

• Adjustment not posting: Check accounting configuration and accounts

• Slow valuation update: Force recompute stock valuation

• Wrong margin calculation: Verify revenue and COGS data sources

• Export error: Check date ranges and data permissions

• Performance issues: Optimize queries and reduce date range

• Spreadsheet Dashboard module must be installed

• Stock Account module must be activated

• Accounting module must be configured correctly

• Costing methods must be set up for products

• Access rights: User (view), Accountant (edit), Manager (configure)

• Browser: Latest Chrome, Firefox, Safari versions