Plugging New Variables into an Existing Variable

Help Home  Previous  Next

Changing Exl-Plan > Making Simple Changes > Plugging New Variables into an Existing Variable

This involves creating schedules within the unused Excel rows below an assumption report and then plugging their totals into an existing row within the assumptions report by entering simple addition or summing formulae in lieu of assumption values.

This is one of the easiest and most powerful changes that can be made to an Exl-Plan model as illustrated in the two examples below.

Note that the *Tools/Setup > Clear All Assumption Reports or *Tools/Setup > Clear Current Assumption Report menu commands only remove assumption values in unlocked cells within assumption reports. To use this facility after adding new assumption rows or new blocks of assumption values, the newly added cells must be unlocked (using the *Protection > Unlock Range option or the "unlock" toolbar button) to enable their assumption values to be cleared by the foregoing commands.

Plugging In  - Example 1

It is desired to expand assumptions relating to annual sales values for a particular sales group to accommodate annual forecasts for, say, twenty individual products. To do this, follow the procedure below.

1.Go to Assumptions Report No. 1 - Sales and turn off protection for its worksheet (*Protection > Unprotect This Worksheet).
2.In empty rows below the Report for Supplementary Data & Calculations, add appropriate product description labels for each of the twenty products in column A and then enter sales value forecasts in columns G to K. Unlock these new assumption cells using *Protection > Unlock Range or "unlock" toolbar button.
3.Insert SUM formulae in the appropriate row for the sales group within the Assumptions Report in order to total all the annual values entered into the newly inserted schedule.
4.Restore the worksheet's protection (*Protection > Protect This Worksheet).

No other changes are required aside from possibly inserting weighted average selling prices for the expanded main sales group for each year in the appropriate row for Net selling prices. If different prices are available for each product, proceed as follows:

1.Add a further twenty rows to accommodate the annual prices for each of the twenty products. Unlock these new assumption cells using *Protection > Unlock Range or "unlock" toolbar button.
2.Use twenty more rows to calculate annual sales values (volumes *prices) for each product and add a further row to arrive at their total annual sales. Unlock these new assumption cells using *Protection  >Unlock Range or "unlock" toolbar button.
3.Calculate weighted average prices for each year by dividing total sales by total volumes for each year.
4.Use simple formulae to plug these weighted prices into the row for Net selling prices which relates to the expanded sales group inside the Assumptions Report.

Plugging In - Example 2

It is desired to use separate percentage values for each sales group for Cost of materials/pack or goods for resale in Assumptions Report No. 2 - Materials/Goods, Other Direct Costs & Purchases. Proceed as follows:

1.Go to Assumptions Report No. 2 and turn off protection for its worksheet (*Protection > Unprotect This Worksheet).
2.In empty rows below the Report for Supplementary Data & Calculations, add description labels for the six sales groups in column A and enter cost percentage forecasts in columns G to K. Unlock these new assumption cells using *Protection > Unlock Range or "unlock" toolbar button.
3.In a further six rows, use simple formulae to calculate the costs for each group based on the cost percentages in point 1 above and the sales projections for each group in Assumptions Report No. 1. Unlock these new assumption cells using *Protection > Unlock Range or "unlock" toolbar button.
4.Insert SUM formulae to determine total annual costs for all groups and then enter formulae to divide these cost totals by the values for total sales in Assumptions Report No. 1 (and multiply by 100) to give weighted average cost percentages. Link these percentages into the row for Cost of materials/pack or goods for resale.
5.Restore the worksheet's protection (*Protection > Protect This Worksheet).

You may wish to progress to a more powerful version for the purpose of generating more detailed monthly and quarterly multi-year projections and to gain access to a range of powerful planning tools for "what-ifs" sensitivity analyses, profitability improvement etc. For detailed descriptions, visit <http://www.planware.org/exldesc.htm> and for trial copy downloads go to <http://www.planware.org/exldown.htm>.

See Also:

Changing Exl-Plan

Making Simple Changes

Changing the Name of an Existing Variable

Extending the Power of Plugging In

Using Formulae Instead of Values

Changing Report Layouts

Changing Column Widths

Combining Simple Changes

Making Complex Changes

Extending Exl-Plan



Online:  Support   Free Registration   Purchase Upgrade