Homework answers / question archive / Integrated VBA Project   Scenario Each year, during the budgeting season, the Anytown Rec Center uses Excel to forcast program fee increases or decreases for the upcomming year

Integrated VBA Project   Scenario Each year, during the budgeting season, the Anytown Rec Center uses Excel to forcast program fee increases or decreases for the upcomming year

Computer Science

Share With

---

Integrated VBA Project

 

Scenario
Each year, during the budgeting season, the Anytown Rec Center uses Excel to forcast program fee increases or decreases for the upcomming year. For instance, they can enter a percent increase or decrease per program or for all programs combined.

Before working on the price changes, the worksheet must be updated with the past year program rates in a specified area of the workbook then copied from there to the work area. Keeping the original data from the past year in a specified area allows recopying the data if needed without having to re-enter it manually.

Note: Programs can be added or deleted at any time so data ranges require the ability to grow or shrink without effecting the rest of the worksheet.

At the Annual Budget Meeting with the Board of Directors, A Budget Report is presented with the final forcasted data which was copied from Excel.

Your job is to create automated processes for the tasks listed below for this workbook using the given past year data (test data.)

• A process to copy the past year numbers into a work area.
• A process to allow entering a different percentage rate for each program. The user should be prompted for each program. If the change is 0%, no change should occur in the price of the program in the work area.
• A process to allow entering the same percentage rate for each program (the user will enter the percent into one cell. The process should use that cell for the calculations. If the cell is empty, the user should be prompted for the percent of change.
• A process to copy the final data to the Budget Report Presentation for the Board of Directors.

 

See the next page for a rubric of required elements for this project.

 

 

 

Integrated VBA: Final Project Rubric
Item	Possible Points
Named Range created to hold forcasted percent for the "change all programs" option.	5
Process created to copy past year data into the working area	10
Working area old data deleted. Headings should remain in tact.	10
Named ranges created for past year data and working area	10
Named Ranges used in process coding	20
Process tested and works correctly	5
Process created to allow user to enter a different percent into each program	10
Named Ranges used in process coding	10
Loops used to implement price changes	20
Input box used to determine price change percentage for each program.	10
Appropriate variable types initialized	10
Process tested and works correctly	5
Process created to change all program prices by the same percentage	10
Named Range used in process coding	10
Loops used to implement price changes	20
Appropriate variable types initialized	10
Process tested and works correctly	5
Process created to copy Final summary grid to appropiate area of Board Report Presentation	10
Process tested and works correctly	5
Miscelaneous
Comments in code to describe each main part of processes	10
Naming conventions used for variables	5
Total Points Possible:	210