Hello ,
I have attached a case problem that I am trying to figure out the best way to approach it.
Basically I am being asked to create a What If scenario (a monthly financial tool) that allows to test or change profit margins or sales volume. This scenario should allow also time parameter as it asks that expected margin is reached within 24 or 36 months depending on the product. I understand the problem however I am not sure what is the best way to present it. Does it need to be a what if scenario that allows changes to profit margin % and time in months? Or should I show a monthly data table that portrays monthly values by column with the respective cost, sales and margin? It is obviously more complex as there a couple more variables. Including the fact that in the problem the company expect to make the sales of the chairs within the 1st year.
Any input on which is the best way to present this via what if analysis is appreciated.
Chair Input Cells
Total Cost $200,000.00
Gross Profit $100,000.00
Profit Margin 33.33%
Term(months) 12
Sales % 1st year 80%
Sales % 2nd year 20%
Stock Qty 20000
Table Tops Input Cells
Total Cost $100,000.00
Gross Profit $25,000.00
Profit Margin 20.00%
Term(months) 36
Sales % 1st year 60%
Sales % 2nd year 30%
Sales % 3rd year 10%
Stock Qty 5000
here is the problem:
1. A local furniture chain that sells chairs and tables. The company has only one type of chair (Relaxer) and one type of table (Boca Top). Currently, the company has 20,000 Relaxers and 5,000 Boca Tops in stock; the book values are $200,000 and $100,000 respectively. Warren’s furniture expects to sell the existing stock of chairs over the next 24 months. The tables are slower moving; however, they should be fully depleted within 36 months. The company expects to receive an overall gross profit of $100,000 for the Relaxers and $25,000 for the Boca Tops. The company expects that 80% of the Relaxer sales will be in year 1 and 20% in years 2. The Boca Top sales are expected to be 60% in year 1, 30% in year 2, and 10% in year 3.
Please create a monthly financial tool that will show the performance of Warren’s chair and table sales over the next three years. The company should also be able to test different profit margins and different sales run-offs by year (i.e., What if monthly Relaxer sales are constant for two years or 50% per year?)
I have attached a case problem that I am trying to figure out the best way to approach it.
Basically I am being asked to create a What If scenario (a monthly financial tool) that allows to test or change profit margins or sales volume. This scenario should allow also time parameter as it asks that expected margin is reached within 24 or 36 months depending on the product. I understand the problem however I am not sure what is the best way to present it. Does it need to be a what if scenario that allows changes to profit margin % and time in months? Or should I show a monthly data table that portrays monthly values by column with the respective cost, sales and margin? It is obviously more complex as there a couple more variables. Including the fact that in the problem the company expect to make the sales of the chairs within the 1st year.
Any input on which is the best way to present this via what if analysis is appreciated.
Chair Input Cells
Total Cost $200,000.00
Gross Profit $100,000.00
Profit Margin 33.33%
Term(months) 12
Sales % 1st year 80%
Sales % 2nd year 20%
Stock Qty 20000
Table Tops Input Cells
Total Cost $100,000.00
Gross Profit $25,000.00
Profit Margin 20.00%
Term(months) 36
Sales % 1st year 60%
Sales % 2nd year 30%
Sales % 3rd year 10%
Stock Qty 5000
here is the problem:
1. A local furniture chain that sells chairs and tables. The company has only one type of chair (Relaxer) and one type of table (Boca Top). Currently, the company has 20,000 Relaxers and 5,000 Boca Tops in stock; the book values are $200,000 and $100,000 respectively. Warren’s furniture expects to sell the existing stock of chairs over the next 24 months. The tables are slower moving; however, they should be fully depleted within 36 months. The company expects to receive an overall gross profit of $100,000 for the Relaxers and $25,000 for the Boca Tops. The company expects that 80% of the Relaxer sales will be in year 1 and 20% in years 2. The Boca Top sales are expected to be 60% in year 1, 30% in year 2, and 10% in year 3.
Please create a monthly financial tool that will show the performance of Warren’s chair and table sales over the next three years. The company should also be able to test different profit margins and different sales run-offs by year (i.e., What if monthly Relaxer sales are constant for two years or 50% per year?)
