Prepare a model that allocates the support departments using the three methods (direct, sequential(step), and reciprocal)

Use the following data to prepare a model that allocates the support departments using the three methods (direct, sequential(step), and reciprocal).  Pay attention to the following instructions:

1. You should design your model to include an input section, an output section with a separate display for each allocation method, and user instructions.

2. The output section should have nothing but references and formulas in it. Each allocation method’s formulas and cell references should refer to the input section and not to other allocation sections.

3. For the sequential(step) method, allocate HR first.

4. Calculate the reciprocal amounts using a formula or using Excel functions for linear equations or simultaneous equations (search the Internet for instructions) in the output area. Display the original equations for the total support department costs in a text box next to the final allocation of support department costs.

5. Before submitting your spreadsheet, review it from the perspective of someone looking at for the first time. Make sure it is easy to understand, navigate and use.  For example, a user should be able to see where to change the allocation base values, change them easily and see the correct recalculated allocations.