# daily p&l sheet: hand this file to your agent Paste this whole file into your agent (claude, chatgpt, whatever you run) and tell it to build the four tabs from your own data. it is written to be read by an agent, not by you. what it gives your agent: four tabs (settings, orders, days, recurring charges), the exact formulas, the invoice variance loop, a refuse-to-guess list so it leaves blanks instead of inventing costs, and a ready prompt to build the whole thing from your own data. before you run it: - fill in tab 0 first. the plan and the fee rate change every number below it. - pick ONE cost allocation rule and never switch mid period. - keep store revenue and your ad platform's purchase value in separate columns. net profit uses store revenue only. --- === TAB 0: SETTINGS (fill these first, they change every number below) === cost_allocation_rule fifo_by_purchase_order OR moving_average pick one. never switch mid-period. state it on the sheet reporting_currency timezone your store's, not utc variance_flag_threshold_pct e.g. 5 shopify_plan basic / grow / advanced / plus third_party_txn_fee_pct the rate for YOUR plan. see the table below. do not default it using_shopify_payments yes / no. if yes, there is no third-party fee at all --- third-party transaction fee, by plan (shopify.com/pricing, read 4 nov 2026) --- basic 2.0% grow 1.0% advanced 0.6% plus 0.2% this fee only applies when you use a payment provider OUTSIDE shopify payments. on shopify payments it is zero. most stores are on basic, so 2% is the common case, not 1%. set the number from your own plan and leave a note on the sheet saying which plan it came from. --- what each rule means, so the sheet is self-documenting --- fifo_by_purchase_order each order draws from the oldest purchase order with stock remaining, at that purchase order's landed cost per unit. use when freight swings between shipments and you want the swing to show up in the order cost. moving_average unit cost = total landed value on hand / units on hand, recalculated on every receipt. use when units are interchangeable and you are not attributing a shipment. neither is exact. both are defensible. mixing them is not. === TAB 1: ORDERS (one row per order) === order_id order_date day_key destination_country gateway financial_status fulfilment_status gross_merchandise discounts shipping_charged refunds net_revenue processing_fee_rate processing_fee_fixed payment_processing_fee shopify_txn_rate shopify_transaction_fee total_fees purchase_order_ref landed_cost_per_unit units landed_cost_allocated landed_cost_expected allocated_variance true_order_cost order_profit order_margin_pct --- formulas --- net_revenue = gross_merchandise - discounts + shipping_charged - refunds payment_processing_fee = net_revenue * processing_fee_rate + processing_fee_fixed shopify_transaction_fee = net_revenue * shopify_txn_rate total_fees = payment_processing_fee + shopify_transaction_fee landed_cost_per_unit = per cost_allocation_rule, from purchase_order_ref landed_cost_allocated = landed_cost_per_unit * units allocated_variance = landed_cost_allocated - landed_cost_expected true_order_cost = landed_cost_allocated + total_fees order_profit = net_revenue - true_order_cost order_margin_pct = order_profit / net_revenue --- notes --- order_date is in your store's timezone, not utc day_key is yyyy-mm-dd and is the join to tab 2 purchase_order_ref is required. an order with no purchase order is a gap, not a row landed_cost_allocated is an allocation, never an exact figure. say so on the sheet shopify_txn_rate comes from tab 0, set from your own plan. zero if you use shopify payments if gateway is not in the fee table, leave the fee blank and list the order === TAB 2: DAYS (one row per trading day) === day_key orders revenue landed_cost fees recurring ad_spend impressions link_clicks add_to_carts initiate_checkouts sessions purchases purchase_value cpa roas cost_per_add_to_cart net_profit profit_per_order profit_per_visitor --- formulas --- orders = count of paid orders that day revenue = sum of orders.net_revenue (STORE ONLY) landed_cost = sum of orders.landed_cost_allocated fees = sum of orders.total_fees recurring = sum of recurring.daily_cost_exact over charges active that day cpa = ad_spend / purchases (blank if purchases = 0) roas = purchase_value / ad_spend (blank if ad_spend = 0) cost_per_add_to_cart = ad_spend / add_to_carts (blank if add_to_carts = 0) net_profit = revenue - landed_cost - fees - recurring - ad_spend profit_per_order = net_profit / orders (blank if orders = 0) profit_per_visitor = net_profit / sessions (blank if sessions = 0) --- notes --- TWO SOURCES, NEVER MIXED. revenue, landed_cost and fees come from the store. this is money. ad_spend is real money out, from the ad platform. purchase_value is the ad platform's CLAIM about money in. it is not your revenue. both are real, and they are never the same number. net_profit uses STORE revenue only. purchase_value never enters it. roas exists to judge an ad against the platform's own number, nothing else. if a total blends store revenue and platform purchase value, that total is wrong. blank, not zero, when the divisor is zero. a zero says you checked and it was nothing sessions come from your store analytics === TAB 3: RECURRING CHARGES === charge_name vendor amount currency fx_rate amount_reporting billing_cycle cycle_days daily_cost_exact start_date end_date active --- formulas --- amount_reporting = amount * fx_rate cycle_days = monthly 30.4167, every 30 days 30, yearly 365, one-off blank daily_cost_exact = amount_reporting / cycle_days (4 decimal places) active = start_date <= day <= end_date --- notes --- use the fx_rate from the day you were billed, not today's rate keep 4 decimal places. rounding a daily cost to cents hides the total one-off charges go on the day they were charged, not spread === THE VARIANCE LOOP (run when a purchase order invoice arrives) === 1 invoice arrives, in the currency it was billed in 2 enter product, freight and agent fees as three separate lines 3 convert at that day's rate 4 invoiced_total = (product + freight + agent) * fx 5 variance = invoiced_total - expected_total 6 write the invoiced cost back to EVERY order that drew from that purchase order, not just the ones placed after it arrived 7 restate the day rows for the affected days 8 flag the sku if variance is over your threshold two invoices in a row step 6 is the one people get wrong. an order placed before the invoice arrived still drew from that purchase order, so it still restates. === THE REFUSE-TO-GUESS LIST (give this to the agent with the sheet) === leave blank and list, never estimate: - a cost for a sku with no invoice and no purchase order - a fee for a gateway that is not in the fee table - an fx rate for a day that is not available - a refund that has not settled - whether a line is a bundle or a single (read it from the line items) - which purchase order an order drew from, when stock is mixed and the rule cannot decide - anything about tax (out of scope, do not model) every blank goes on a gaps list at the bottom of the sheet. a blank gets fixed. a guess gets believed. === THE AGENT PROMPT (optional, if you want it built for you) === build the four tabs above from my own data. rules: - read-only. do not change anything in my store, my ad account or my email platform - use the formulas exactly as written, and do not add your own - use the cost_allocation_rule in tab 0 and never mix rules. say which one you used at the top - use third_party_txn_fee_pct from tab 0. never assume 1%. if using_shopify_payments is yes, set it to zero. state the plan and the rate you used - keep store revenue and the ad platform's purchase value in separate columns. net profit uses store revenue only. never blend them in one total - where a value is missing, leave the cell blank and add a row to the gaps list with the reason - never estimate a cost, a fee, a rate or a tax - state the timezone and the reporting currency you used at the top of the sheet - when you finish, print: the number of orders priced, the number of gaps, the rule you used for cost allocation, and the three skus with the largest allocated variance do not recommend a price change. do not recommend a budget change. this is a report.