Tally a Balance Sheet/Optimize Prices Using Linear Equation In A Nutshell

 

Hi all!

Today, I aim to unlock the secrets of crafting a linear equation in a simple yet powerful manner. You might think it's easy – but think again! Starting from scratch, I used my optimization Excel model from the previous chapter and I finally succeeded in creating what can only be described as UNIVERSAL.

Step 1:

I began with a concise simulation featuring summarized financial statements on an Excel sheet, as shown in the screenshot. I uploaded this into Bing Autopilot, requesting it to generate a linear equation. After solving for ‘x’ and replacing the result in the markup cell, the next automatically generated markup due to cell linking was off, indicating an increased discrepancy between Assets and Equity + Total Liabilities. Remember, this process is iterative.

Step 2:

Next, I employed Goal Seek to adjust the markup and balance the difference. Once the balance sheet tallied, I re-uploaded it to the AI for another equation. With my previous expertise in tallying balance sheets manually and using solver, I got an idea to feed it with detailed information on how cash flows from purchases and Work In Progress (WIP) until it becomes a finished good and is recognized in Revenue. I also showed how this value is marked up with taxes to derive the Maximum Retail Price (MRP) and where it is accounted for in the statements. Additionally, I demonstrated bank cash inflow and outflow movements. This comprehensive data allowed the AI to generate the code easily. The resulting equation balanced the balance sheet perfectly, so I moved ahead with confidence.

Step 3:

Finally, I utilized comprehensive, life-like financial statements, formatted for AI recognition of cash flow patterns. The AI provided another equation, which worked seamlessly. Upon replacing the ‘x’ value in the markup cell, the difference narrowed. For instance, the balance sheet balanced at a 100% Sales Markup; changing it to 101% made the ‘x’ value revert to 100%. That's when I knew I had it!

Workings:

Dive into this video to see how to calculate the equation and solve for ‘x’. If you observe closely, after a markup of 1019.08%, the balance sheet difference decreases further if we include additional precision, such as .0805674892, and so on. That's the essence of how it operates. Recognize that every model produces different results, and for this specific model, this equation fits perfectly. If you wish to use it, ensure you create formats consistent with the attached Excel.

Alternative Method: I've used the 10% and 20% markups and the results, as shown in the attached Excel sheet, to determine the optimal 60% markup for a given working capital. Here's how it works: I don't think a video is necessary because all you have to do is copy the SOFP differences for 10% & 20% and plug them into the equation.

Let's take another look at the equations and make sure we get the correct and positive markup percentage.

 

Data Summary:

1. At 20% Markup:

    SOFP Difference: -₹809,900

 

2. At 10% Markup:

   - SOFP Difference: -₹1,012,375

 

Formulating Equations:

We have two points:

- (Markup, SOFP Difference)

- (20, -809900)

- (10, -1012375)

 

Using these points, we can formulate the linear equation:

SOFP Difference=𝐴⋅Markup+𝐵

 

Creating Equations:

1. From 20% Markup: [ -809900 = 20A + B ]

 

2. From 10% Markup: [ -1012375 = 10A + B ]

 

Solving the System of Equations:

Subtract the second equation from the first to eliminate ( B ):

(20A + B) - (10A + B) = -809900 - (-1012375)

10A = 202475

A = 202475.10

A = 20247.5

 

Now, substitute ( A ) back into one of the equations to solve for ( B ):

 -809900 = 20(20247.5) + B

-809900 = 404950 + B

B = -809900 - 404950

B = -1214850

 

Linear Equation:

The linear equation is:

SOFP Difference = 20247.5 .Markup - 1214850

 

Rearranging for Positive Markup:

To find the markup that makes the SOFP Difference equal to 0:

0 = 20247.5 .Markup - 1214850

1214850 = 20247.5 .Markup

Markup = 1214850/20247.5

Markup =Approx 60

 

Therefore, the correct and positive markup percentage that balances the SOFP based on the given data is approximately 60%. (View/Download Excel)

 

Pros:

Eliminates the need for Solver.

Cons:

Provides only one optimal price where assets equal equity and liabilities, unlike Solver, which allows multiple markups for balanced balance sheets.

Note: 

Stay tuned for future updates and improvements!

© Yasaswi Gomes 2024

Disclaimer

Please note, while the results and methods shared here have demonstrated unprecedented accuracy within the specific model discussed. The information and equations provided in this document are based on specific financial models and assumptions tailored for the examples presented. By using these equations and methods, you agree to take full responsibility for any outcomes.