ReAllocator Tool (Download)
 |
This Resource Allocation model (ReAllocator) helps you size and allocate marketing effort, such as sales force headcount. This spreadsheet is a special version of the model, designed to illustrate sales force allocation across products at Syntex Labs. This model helped Syntex determine:
- How large their sales force should be, and
- How the sales force effort should be allocated across products to maximize profit.
This is a limited version of the more general ReAllocator Model, made specific to the Syntex data. You can experiment with the optimization options and the constraints. You can also change the names of the products and the profit margins, but you cannot change anything not in blue.
How the Model Works
A firm's current allocation of sales people across market segments is the basis for estimating the potential impact of changes. The ReAllocator model requires you to estimate how much sales would increase or decrease by product if the different products were assigned one of the following levels of sales effort:
A firm's current allocation of sales people across market segments is the basis for estimating the potential impact of changes. The ReAllocator model requires you to estimate how much sales would increase or decrease by product if the different products were assigned one of the following levels of sales effort:
- No salespersons — represented in the "0" column under Assumptions
- One half of the current number of salespersons — represented in the -50% column
- 50 percent more salespersons — represented in the +50% column
- Unlimited salespersons — represented in the "Max" column
Using these estimates, the model develops response curves for each product or market segment. The response curves are plots that describe the incremental sales resulting from the allocation of an additional sales person to a market segment. Each additional sales representative will be assigned by the model to the product that will generate the highest increase inprofit. If the company wants to add more than one rep, the model follows the same procedure again and assigns each rep sequentially to the product that offers the highest increase in profit at each stage.
System Requirements:
Excel 2000 or higher
Solver add-in installed
Security settings set to MEDIUM or LOW .
Change the security settings of Excel
Excel 2000 or higher
Solver add-in installed
Security settings set to MEDIUM or LOW .
Change the security settings of Excel
For Excel 2000
Go to Tools – Macros – Security settings
Set the security level to Medium
Go to the Trusted Sources tab
Make sure the Trust all installed add-ins and templates box is checked.
Go to Tools – Macros – Security settings
Set the security level to Medium
Go to the Trusted Sources tab
Make sure the Trust all installed add-ins and templates box is checked.
For Excel XP (2002 / 2003)
Set the security level to Medium
Go to the Trusted Sources tab
Make sure the two boxes are checked.
Set the security level to Medium
Go to the Trusted Sources tab
Make sure the two boxes are checked.
ATTENTION: THIS SOFTWARE IS SUBJECT TO THE FOLLOWING DOWNLOAD AGREEMENT; DO NOT INSTALL IF YOU DISAGREE WITH ANY OF FOLLOWING.
1. MARKETINGNPV LLC HEREBY GRANTS TO YOU A NON-EXCLUSIVE LICENSE TO INSTALL AND USE THIS SOFTWARE ON A SINGLE COMPUTER ONLY AND ONLY IN ACCORDANCE WITH THE SAME TERMS, CONDITIONS, RESTRICTIONS, AND LIMITATIONS AS APPLY TO YOUR SINGLE-USER INSTALLATION AND SINGLE-USER USE OF MICROSOFT EXCEL UNDER THE MICROSOFT END-USER LICENSE AGREEMENT, ALL OF WHICH ARE INCORPORATED HEREIN WITH MARKETINGNPV LLC AS LICENSOR AND YOU AS LICENSEE.
2. THE SOFTWARE IS PROVIDED WITHOUT EXPRESS OR IMPLIED WARRANTY. WE MAKE EVERY REASONABLE EFFORT TO ENSURE THE DOWNLOADABLE TOOLS ARE FREE OF VIRUSES, WORMS, OR OTHER HARMFUL SOFTWARE ELEMENTS, HOWEVER WE DO NOT GUARANTEE THEM TO BE FREE OF SUCH AGENTS. THESE TOOLS HAVE NO FUNCTION OR PURPOSE OTHER THAN THAT EXPLICITLY DESCRIBED AND DO NOT COMMUNICATE WITH ANY OTHER COMPUTER OR NETWORK. SINCE THESE TOOLS ARE OFFERED FREE OF CHARGE, YOUR SOLE AND EXCLUSIVE REMEDY IS RETURN THE SOFTWARE TO MARKETINGNPV, WITHIN 90 DAYS OF INSTALLATION, WITH A DETAILED WRITTEN DESCRIPTION OF ALL ALLEGED DEFECTS. NO DAMAGES MAY BE CLAIMED. THIS IS THE SOLE AND EXCLUSIVE REMEDY FOR ANY ALLEGED DEFECT.
3. THE FOREGOING EXCLUSIVE REMEDY AND LIMITATION OF LIABILITY IS AN INTEGRAL PART OF YOUR AGREEMENT TO DOWNLOAD THIS PROGRAM.
4. ALL DISPUTES AND DISAGREEMENTS SHALL BE RESOLVED BY FINAL, BINDING ARBITRATION IN PRINCETON, NEW JERSEY BEFORE A RETIRED JUDGE APPOINTED BY THE AMERICAN ARBITRATION ASSOCIATION.
You can find a more interactive uploading version of this tool at DecisionPro's site.
