MRP Demo
MRP, or Material Requirements Planning, is in essence a simple ledger of future inventory debits and credits. However, due to a variety of dynamic variables, results can be difficult to predict and visualize. This sheet gives the common tabular display of the future plan, and a chart of demand, supply and inventory.
The Demo is an instructional tool using VBA-controlled Scenarios to present sets of inputs and their effect on output. Several illustrative Scenarios are preset and can be scrolled. The user can enter their own values, and can save, change or delete Scenarios with the click of a button.
Variables include demand randomized within a high/low range, or constant; beginning inventory, minimum and incremental order quantities, and cycle time; safety stock and safety time, both static and time-phased.
This project uses complex formulas made readable with line breaks, indents and help text.
Excel features: Advanced formulas with Name references; Conditional Formatting; Table; Chart; clean screen; VBA: Formatting, Scenario Management
Contents
MRP Demo.zip, with
- MRP Demo.xlsm (Excel Macro-Enabled Workbook)
Usage
USAGE NOTES
The demo is most effective with maximum screen space. Headings are hidden (Alt-W,V,H). Additional space can be gained by:
Ctrl+F1 to hide the ribbon
Shift+Ctrl+F1 to hide the ribbon, toolbar and status bar
The five grouped buttons in each section are used to select, add, change and delete scenarios, with prompts to guide the user.
• < and > spinbuttons cycle through existing scenarios
• + button creates a new scenario with the current values in the section
• Δ button changes the values and/or description of the current scenario
• X button deletes the current scenario and renumbers any scenarios above itAll white cells are editable for ad-hoc demo or to set up new scenarios. Scenarios can also be manually reviewed via Scenario Manager, Alt-A,W,S.
Table formulas can be read by expanding the formula bar, Shift+Ctrl+U.
A hidden sheet stores values generated by VBA macros.