Administrative Assistants are often presented with problems that need to be solved using spreadsheet software

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Budget Project Instructions

Description & Purpose

Administrative Assistants are often presented with problems that need to be solved using spreadsheet software. The individual(s) and/or teams they support frequently require assistance with creating, editing, fixing, and maintaining Excel workbooks. Administrative Assistants need to show proficiency and creativity in the creation and management of Excel workbooks as it will be used to either assist with data entry, data management, financial analysis, charting and graphing, task management, time management, or any other information that needs to be organized and tracked. Your task for the major project to solve the specific problems of the scenario below, using your knowledge gained from Excel Associate and Excel Expert. You will need to use research, critical thinking, analytical, and problem solving skills to create a workbook that best solves the scenario. Make sure your major project is thorough, professional, well organized, and incredibly helpful and useful. You are permitted to use additional commands or formulas that were not covered in the course that you have found in online forums and Excel support communities.

Your project needs to include at least three advanced features from Microsoft Excel Expert. You can choose which advanced features to use, but they must come from the following list:

 Advanced formulas

 Advanced functions

 Advanced formatting

 Advanced charts

 Pivot tables

 Pivot charts

 What-if analysis

 Macros The scenarios do not tell you which advanced features are the most appropriate to use to solve the problem and analyze the data. You have to make decisions on which features to use.

Scenario:

You are budgeting for your monthly expenses. You wish you could type data into a spreadsheet and have your monthly budget calculated for you based on whatever annual salary you currently receive (or will receive your future place of employment). Having a budget calculator will help you organize your salary and expenses and determine how much money you can save or spend at any given time throughout the month or annually. Note: you DO NOT have to use your personal financial information in the worksheet you submit as your project. Use approximations to protect your confidentiality.

Task: build an Excel workbook template that automates the calculation of your salary and helps you budget your monthly expenses. Make sure you use advanced Excel functions to analyze and organize the data. Your worksheet must include:

 Calculation of your approximate net annual salary. You can type in any gross annual amount into your spreadsheet, but set up formulas that automatically perform deductions (if applicable)

 A list of all of your fixed and variable living expenses. This can include rent/mortgage, utilities, telephone/internet, transit, car expenses, child care, credit card payments, insurance, groceries, take out food, pet care, computer equipment, health care, clothing, travel, personal care, education, recreation, etc. For more information on what can be included in a budget, visit these websites: o Monthly Budget Canada o Making a Budget o How to Create a Monthly Budget

 Charts and graphs displaying how you spend most of your money (what percentage is dedicated to rent? What percentage is dedicated to buying clothes?)

 Advanced features that help you answer the following questions: o If I saved a certain amount of money per month, at what point will I have enough money to buy what I want? Or at what point will I have saved all of the money I want to have saved? o Is there a way I can make a progress chart or bar in Excel to show me how much more I need to save to reach my goal? o Is there a way to use colour coordination to show me if I have overspent during the month?

Submission: submit two Excel workbooks: one template for the instructor to test, and one with populated financial data to prove that the spreadsheet and formulas work.

Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme