Profit Margin Tool/ Calculator

[kirstinbala]

Hi,

I have been tasked with creating a profit margin tool within Excel that is interactive and able to load a customer account's sales history data. It must allow the user to perform "what-if" scenarios in Excel, in order to test the revenue effects of increasing/decreasing the profit margins for certain product lines.

Within each specific customer account, there are some contracted margins for certain product lines that should not be changed, i.e. locking the cell if that is possible.

I'm not Excel wizard, and I'm just looking or somewhere to start.

I was provided an Excel doc with the current "profit margin tool" being used, but it is a simple Excel sheet that does not perform any data transformation or interaction - it simply states sales data.

 

[RCBricker]

try this for starters

Excel What-If Analysis - Easy Excel Tutorial

 

[RCBricker]

OK I have more time so I can see what I can do to get you started.

You can use the simple tool they gave you as a data set. Hopefully it has all your parts and their cost to produce etc. All you really need to know for a simple tool is Part number or sales item, The cost to produce, and the revenue from sales. Profit=revenue-cost and profit margin is profit divide by revenue.

aside from that you need to know what customers may change to build in the analysis.

The analysis section should be separate from your data so that you maintain integrity. Basically make it look exactly like the actual or true value area but with the cells of the data you want to test. So if you want to see what the profit margin would be if you sold the item for less then your analysis section should have blank cells in it for revenue.

I would create the true value section based on a drop down list of parts/sales items. Once selected the true values would populate that section.

The Analysis section would have a drop down that would select the various types of scenarios to run. After the selection is made the would then select the cells with the variable that would be changed to reflect the analysis.

HTH

Rich