Part 4- Multi-Product Price Optimization in a Nutshell

 

Final Chapter of Optimization Analysis

Products:

• Candy: 186% Markup
• Cake: 186% Markup
• Biscuits: 186% Markup
• Ice Cream: 186% Markup
• Sweets: 186% Markup
• Chocolate Bar: 186% Markup
• Coke: 186% Markup
• Fanta: 186% Markup
• Red Bull: 186% Markup

View/Download Excel Analytical Model Here

Overview:

Due to the complexity of variables and constraints in this model, it is challenging to optimize all products simultaneously. Optimization is feasible for 4-5 products at a time. We have achieved results by minimizing variable changes in the markup section and running the solver with upper and lower bounds on sales markup. The optimization process successfully determined the selling prices.

This provides a framework for you to decide on sales prices based on the criteria drawn, aka cash drawings. There are still potential areas for exploration within this analytical model, with results dependent on work-in-progress (WIP) and sales markup.

Optimization Drivers in a Nutshell

• Assets > Liabilities: Consider making a cash withdrawal or lowering the markup to balance the balance sheet.
• Assets < Liabilities: Introduce new working capital or increase the markup to balance the balance sheet.
• Market Conditions: Lowered prices for products in order to meet severe competition will be compensated by adjusting prices for monopoly products, ensuring the balance sheet remains balanced.
• Universal Markup: Reducing prices for products to stay competitive will be offset by raising prices for monopoly products, ensuring the balance sheet stays balanced. 
• Scalability: You can add another 150 products into this simulation, expanding your optimization analysis further.

Salient Features:

• Purchase Prices: Inflated
• Net Sales Revenue & Purchases: Recognized
• First-Year Allowance: Deferred Taxes accounted for
• Current Taxes: Accurate, with a summary list provided
• Production Budget: Detailed
• Optimization Performance: 100% successful, but I recommend Frontline Solver Premium
• Balancing the Assets & Liabilities: Multiple markups ensure Statement of Financial Position (SOFP) is tallied, giving us vast options in price setting
• Versatile: This analytical model can be used to apply discounting and MC=MR strategies from previous chapters
• Price Settings: You’re the master at setting prices here. If your plant has a maximum manufacturing capacity of 10,000 units and the break-even point is 5,000 units, you can set the price for any output. This is because pricing decisions are only considered after the balance sheet is tallied. However, Solver is capable of optimizing quantity & prices for you within upper and lower bounds, but it might not suit your goals
• Superior: Makes all costing methods redundant
• Sensitivity Analysis: This analytical model offers a valuable feature! When I included drawings, the prices were high, and when I excluded drawings, the prices were low. You can configure variables that might affect your prices in a similar way. 
  
  Another instance, if there's an overdraft (negative bank balance) in the first two years, you can use the second video to adjust it. 

Limitations:

• Limited to few variables and constraints
• The balance sheet couldn't be tallied in the first-year due to issues with capital and debt. However, I wrote a linear equation which tallies a balance sheet and will try it that way and publish the results soon. 

Note:

In the previous model, I used the offset between Deferred Tax Assets (DTA) and Deferred Tax Liabilities (DTL). In this model, I used the offset between Net Temporary Differences. There is only a minor difference in amounts.

Anyone with basic Excel skills will find this model user-friendly. Additionally, the bank's negative balance during the first two years can be addressed by employing various markup optimization techniques from the video. 

Markup optimization can be achieved in different ways:

1. By selecting the markups from cells V12 to V29.

2. By choosing each cell in the range B21 to U29 as a variable changing cell.

I've implemented a creative approach in the Current Taxes section. Since the Taxable Income is negative, this results in a negative current tax, meaning no taxes were paid. This assessment has been reflected in the Statement of Profit or Loss and Other Comprehensive Income (SPLOCI-PL) to find its impact on profits. For more precise results, please adhere to your specific tax compliance requirements.

Example, your Adjusted Trading Profit x 30% Corporate Tax Rate = Current Tax Expense can be:

Tally ERP is one of the best tools to understand how a balance sheet is balanced and what happens when there is an imbalance. It demonstrates the process of balancing a sheet, and while this isn't about auditing/reporting, it's widely known that the Statement of Financial Position (SOFP) doesn't always need to balance. However, by optimizing, for example, using the conventional price you have set and then optimizing it, you can achieve a balanced balance sheet. (These pictures depict a tallied balance sheet and a non-tallied one input by me)

For further updates or details, I will contact you as soon as possible. Enjoy exploring the model & hope you like my videos.

Feel free to let me know if you need any further assistance or clarification. If you want to convert this model into a real financial statement, I'm here to help!

 

© Yasaswi Gomes 2024

 

Disclaimer

The techniques employed in this optimization analysis are unprecedented, and the results achieved are considered 100% optimal based on the methodologies used. While the tools and methods applied have been carefully selected and executed to ensure maximum accuracy and efficiency, the author acknowledges that there may be alternative software or methods that could yield different outcomes.