Problem Topic #3: Budget Calculator Note: this problem is more aligned with something that could happen in an individual

[answerhappygod]

Problem Topic 3 Budget Calculator Note This Problem Is More Aligned With Something That Could Happen In An Individual 1 (30.27 KiB) Viewed 23 times

Problem Topic #3: Budget Calculator Note: this problem is more aligned with something that could happen in an individual's life rather than a problem that an employer asks you to solve, but it still addresses a useful method of using Excel. Scenario: you are searching for a job and 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 will receive at 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 deduct payroll deductions. Use Chapter 11 in your accounting textbook for more information about usual payroll deductions. Make whatever assumptions you need about the amounts of each deduction and your potential approximate annual salary. 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 How to Create a Monthly Budget Charts and graphs displaying how you spend most of your money (i.e. what percentage is dedicated to rent? What percentage is dedicated to buying clothes?) Your money usage goals, for example, a list of items you wish to purchase (include their approximate cost) vs an amount of money you wish to save by a specified date Ask yourself some of these questions to help you determine what advanced functions and formulas you could include: • 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? • 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? 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 data to prove that it works. A reminder that the populated data must not be your personal financial information.