System Modeling Theory- Please Help- ASAP

Design a spreadsheet that will enable you to request the required funds from the financial division or the organization, order supplies from the home office, and ensure the proper distribution of all needed medical supplies to each of the villages. Your final order to the home office should the number of packages each village will need.

Population shows there are:

 a.

3000 children

 b.

2500 teenagers

 c.

500 seniors

 d. 9000 adults

The villages have no current stock on hand.

 2.
You checked on stock of the medical supplies. Inventory is as follows.

 a. Size A-bandages 124 packages

 b. Size B-Bandages 16 packages

 c. Size C-Bandages 82 packages

 d. 72 rolls of tape

 e.
4 hearing aides

*******Additional Information********

 Senior citizens (those over 65), Children (population 12 and under), Teens (those aged 13 –19), The population aged 20–65 The medical supplies required by each village include bandages (types A, B, and C), medical tape, and hearing aids. Children need type A bandages; teens need type B bandages; and adults (i.e., everyone else), need type C bandages. All members of the population use the same kind of medical tape. Only senior citizens require hearing aids. The former team member explained to you that a good rule of thumb is to ensure that at all times a village should keep in stock two bandages per person and hearing aids for 5 percent of the senior citizen population. Cost and packaging information for the products is as follows: Type A bandages come in packages of 30. Each package costs $3.00. Type B bandages come in packages of 30. Each package costs $5.00. Type C bandages come in packages of 30. Each package costs $6.00.  Medical tape comes in rolls of 2 feet each. You usually use one roll per package of bandages. One roll costs $2.50. Hearing aids are sold in single units (1 per package) and

are $5.00 each.

 Make a table showing the needs compared to stock. Please type a memo to the procurement department letting them know your needs and specifically your shortfalls so they can make sure they are in stock before the shipping date next week. Make sure the table is fully formatted.

 3.
You have been notified in via email that there is a potential increase to bandages costs coming through. Using the population figures above, construct a graph showing how the amount for each order would vary if the cost for the packages of bandages rose by $0.25, $0.50 and so on up to $3.00 per package ***( Bar Graph for Bandages A,B and C ). Make sure the graph is labeled and made presentation ready.

Show the 2 tables (Monies needed &Supplies needed) and Graph (increase in bandage costs) including the table needed to create graph on one spreadsheet. You may use more than one workbook (tabs at bottom) but only one Excel document.