last part which is not yellow is example. can you show all hiw you answer. thank you!

HA M N P R v - Spreadsheet Accounting 2 E1 - Personal Budget 3 4 7 s Purpose: This model will allow the student to practice inputting text and data, simple sum formulas, simple referencing formulas, using the fill-handle, and using various format features Accounting/Business Context: Financial Budget 50 11 14 15 12 Background: 19 Businesses that don't prepare and manage to a budget run a higher risk of failing to meet their financial obligations as they come due. By managing to a budget, a business can seek to obtain necessary financing in advance or forestall discretionary expenditures until funds are available. Individuals that budget are much more likely to achieve their financial goals and avoid credit problems. How can one follow the wise adage, "Live within your means" when you are unaware of what your "means" and "necessary" expenses are? 16 17 18 19 20 2. Situation: 22 You are a 21 year old college student living in an apartment local to the college. You are single, fun loving, and serious Check off Instructions Budget -A D 19 are? 20 23 24 21 Situation: You are a 21 year old college student living in an apartment local to the college. You are single, fun loving, and serious about your education. You take a full load at college but need to work part-time to help support yourself. Your parents are surprised you made it into college given your performance in high school and have offered you a generous monthly stipend for as long as you are in school. Priorities: You want to get a college degree, you enjoy living in that particular apartment alone, your job requires that you maintain a car, you are a social person and enjoy dining out with friends, you want to be financially responsible. 25 27 20 29 3. Requirements: Place your name in Cell A1 of all Printouts 31 32 3 35 1 Develop Model On the EXAMPLE sheet in this workbook is a completed image of the schedule you are to create. On the BUDGET1 sheet the model has been started for you. Complete the schedule in the BUDGET1 sheet by mirroring the image on the EXAMPLE Sheet. Use the same columns and rows. Format your schedule so that it looks as close to the Example as possible. Below are formula and technique requirements that you should use in building your model. Save frequently as you build Complete Structure, Titles and Headings - o Put Your Name in Cell A1 of the BUDGET1 Sheet Bu Chuck Shrutto A H N 0 LJKLM Below are formula and technique requirements that you should use in building your model. Save frequently as you build P Q 7 1 Complete Structure, Titles and Headings o Put Your Name in Cell A1 of the BUDGET1 Sheet o Finish inputting the missing income and expense account titles. eo Widen Column A so that the longest Expense title fits in the column without running over into Column B. Fast & Use the Fill Handle to complete the monthly column headings Fast & Use the Spelling icon on the Review Tab in the Proofing Group to detect misspelled words and typos. Save-A Resist the temptation to print the EXAMPLE sheet. Instead copy the object from the Example sheet onto the BUDGET1 sheet. Move it out of the way as you fill in the information. When you are done with Step 1, but before you print, delete the object as you don't need it anymore. Enter Income and Expense Amounts o input the income and Expense amounts and the January Beginning Cash, DO NOT INPUT to the TOTALS columns and rows Fast & Notice that many of the Income and Expense amounts are the same from month to month. For most accounts, Input January data and then copy the amounts to Feb through Dec. Note: The following accounts have varying monthly amounts INCOME: Salary, Other EXPENSE: Auto Repair, School, Vacation 58 Use Sum Function 5 o Use the AutoSum icon to put =SUMO) formulas for Total Income, Total Expense, and the Annual Total of each Income and Expense category. 61 Fast & If you highlight the area B6:N10 and click the AutoSum icon all your totals for Income will be created. Manual Formula Reference O NET Input the formula for NET as Total Income - Total Expense for January Copy the formula to the remaining months and annual total. GO BEGINNING CASH You should have an amount in the January beginning cash already For February reference the Ending Cash cell from January, =B33 Copy the formula to the remaining months. 71 12 O ENDING CASH Input an =SUM formula that sums Net and Beginning Cash Copy the formula to the remaining months. 66 68 69 70 73 TE 75 75 Verify To Check your various totals and make sure they agree to the EXAMPLE sheet P 19 R S T U y Format 30 o Try to format the schedule to be exactly like the Example o Globally set the format for the BUDGET1 Sheet to be comma","and zero decimal places wo Set the font size and bolding of the different titles o Indent the accounts and subtotals using the Indent Icon on the formatting tool bar o Underline, bold, and center the column headings 15 o Use the Borders icon to put the proper underscores and double underscores 18 o Add $ dollar signs to the rows that start a totaling or are directly under an underscore by highlighting the area and using the $ icon on the format toolbar and then reducing the decimal places 37 09 50 Print o Print Preview Your Schedule and make sure that it fits on one page landscape. 20 Print Completed Schedule (with Row and Column Headings) o Print Formula view of your schedule using the Ctrl-. The schedule must be of a size that can be easily read. Reduce the column widths where appropriate to increase the font size. It is OK if titles in Column A are truncated in this view. % Fast & Keep track of how many column widths you reduced. After printing the formula sheet, click the Undo icon an equal number of times to return your schedule to its previous look. 94 95 95 99 FYI: Because you are printing with the Column and Row Headers turned ON, your indented text will appear as if it was NOT indented on the printout. The Instructor is aware of this issue and it is OK. 100 Instructions Engle Budget 104 mos 03 2 What IF It looks like there are going to be some months where you are going to fall short on cash. Increase the January - Beginning Cash to the exact amount necessary so that Ending Cash never goes below zero in any month. Make Sure You Understand the Requirement: NO month should have an Ending Cash Balance that is a negative amount AND at least one month should have an Ending Cash Balance that is ZERO. Print Completed Schedule mo 100 10 11 12 - 3 Critical Thinking 114 Return the January Beginning Cash Balance in your schedule to the original amount as you have determined that 5 increasing your beginning cash for January is not possible. You called Dad to see if he could increase your 11 monthly stipend, but every time you mentioned needing more money (mysteriously) the cell phone connection would drop. Your study schedule would suffer significantly if you tried to work more hours. It looks like you are going to have to cut expenses In business we view some expenses as necessary and some expenses as discretionary. Discretionary expenses are those that could be discontinued or postponed without detrimentally impacting the normal operating goals of the 117 118 120 121 Check on 113 cut expenses. 113 120 121 122 125 In business we view some expenses as necessary and some expenses as discretionary. Discretionary expenses are those that could be discontinued or postponed without detrimentally impacting the normal operating goals of the business. As individuals we have to prioritize our spending. Some spending is critical to us achieving lifestyle and career 12 goals; this spending has priority in our budget. Other spending may fulfill an immediate desire but has little long-term 124 impact Consider the expense categories and identify the three discretionary expenses that you believe could be reduced or eliminated given your priorities described in the Situation section above. Briefly justify why you feel the expense is 127 discretionary. (Do not use other as one of your discretionary expenses.) Mark your answers in the space provided on the Check-off sheet. Adjust the spending in the discretionary categories you just identified and produce a revised budget that prevents a 12 negative cash balance in any month, but allows as balanced an amount of discretionary spending as possible. Print Revised Budget Schedule 129 130 131 133 134 1 END PROBLEM 179 10 Chickor P e S Below is another example to help you with your formulas. Jul Aug Sep Oct Nov Dec Total $ 1,300 $ 960 $ 650 $ 650 $ 650 $ 650 $ 9,680 5 5 5 5 5 60 700 700 700 700 700 700 8,400 200 300 $ 2,005 $ 1,665 $ 1,355 $ 1,355 $ 1,355 $ 1,555 S 18.440 FOLLOW *** $ 600 $ 600 $ 600 $ 600 $ 600 $ 600 $ 7,200 75 75 75 75 75 75 900 150 150 150 150 150 150 1.800 400 450 15 15 15 15 15 15 180 55 55 55 55 55 55 660 40 40 40 40 40 40 480 220 220 220 220 220 220 2.640 100 100 100 100 100 100 1.200 60 60 60 60 60 720 80 80 80 80 80 80 960 3 100 100 100 100 100 100 1,200 575 1,150 700 700 10 10 10 10 10 10 120 5 $ 2.205 $ 2.080 $ 1,505 S 1505 5 1,505 $ 1905 S 20,360 60 $ (200) S (415) $ (150) S(150) S(150) $ (350) $ (1.920) 495 295 (120) (270) (420) (570) $ 295 $(120) $ (270) S (420) $ (570) $ (920) Limited View of Formulas B Single Accounting Underline. See if you can find it in Excel if not, Just use the underline icon. Jan Feb Mar - coloolwoloslaw N- 640 5 700 640 5 700 640 5 700 SUM(B6:B9) SUM(C6:09) SUM(D6:09) 600 75 150 600 75 150 600 75 150 1 Student Name 2 Personal Budget 3 Monthly 4 5 Income: Salary, net Interest & Dividends Parent Subsidy Other 10 Total Income 11 12 Expenses: 13 Apartment Rent 14 Car Fuel 15 Auto Insurance 16 Auto Repair and Service 17 Renters Insurance 18 Telephone & Internet 19 Cable TV 20 Groceries 21 Dining Out 22 Beer 23 Clothing 24 Entertainment 25 School 26 Vacation 27 Other 28 Total Expenses 29 30 Net 31 32 Beginning Cash 33 Ending Cash 15 55 40 15 55 40 220 100 60 80 220 100 60 80 100 15 55 40 220 100 60 80 100 100 575 10 =SUM(813.827) 10 10 =SUM(C13:C27) SUM(D13:D27) =B10-B28 =C10-C28 =D10-D28 1000 =B33 =C33 ESUM(B30:832) ESUM(C30:C82) =SUM(D30 D32) D E F G 640 A B 1 Student Name 2 Personal Budget 3 Monthly 4 Jan Feb 5 Income 6 Salary, nel 640 7 8 9 10 Total Income 11 12 Expenses 13 14 15 16 Auto Repair and Service 17 Renters Insurance 18 Telephone & Internet 19 20 21 22 23 24 Entertainment 25 School 26 Vacation 27 Other 28 Total Expenses 29 30 Net 31 32 Beginning 1000 33 Ending Cash 34 35 36 37 38 39 40 41 42 43 44 45 Check-Off Instructions Example Budget1 HA M N P R v - Spreadsheet Accounting 2 E1 - Personal Budget 3 4 7 s Purpose: This model will allow the student to practice inputting text and data, simple sum formulas, simple referencing formulas, using the fill-handle, and using various format features Accounting/Business Context: Financial Budget 50 11 14 15 12 Background: 19 Businesses that don't prepare and manage to a budget run a higher risk of failing to meet their financial obligations as they come due. By managing to a budget, a business can seek to obtain necessary financing in advance or forestall discretionary expenditures until funds are available. Individuals that budget are much more likely to achieve their financial goals and avoid credit problems. How can one follow the wise adage, "Live within your means" when you are unaware of what your "means" and "necessary" expenses are? 16 17 18 19 20 2. Situation: 22 You are a 21 year old college student living in an apartment local to the college. You are single, fun loving, and serious Check off Instructions Budget -A D 19 are? 20 23 24 21 Situation: You are a 21 year old college student living in an apartment local to the college. You are single, fun loving, and serious about your education. You take a full load at college but need to work part-time to help support yourself. Your parents are surprised you made it into college given your performance in high school and have offered you a generous monthly stipend for as long as you are in school. Priorities: You want to get a college degree, you enjoy living in that particular apartment alone, your job requires that you maintain a car, you are a social person and enjoy dining out with friends, you want to be financially responsible. 25 27 20 29 3. Requirements: Place your name in Cell A1 of all Printouts 31 32 3 35 1 Develop Model On the EXAMPLE sheet in this workbook is a completed image of the schedule you are to create. On the BUDGET1 sheet the model has been started for you. Complete the schedule in the BUDGET1 sheet by mirroring the image on the EXAMPLE Sheet. Use the same columns and rows. Format your schedule so that it looks as close to the Example as possible. Below are formula and technique requirements that you should use in building your model. Save frequently as you build Complete Structure, Titles and Headings - o Put Your Name in Cell A1 of the BUDGET1 Sheet Bu Chuck Shrutto A H N 0 LJKLM Below are formula and technique requirements that you should use in building your model. Save frequently as you build P Q 7 1 Complete Structure, Titles and Headings o Put Your Name in Cell A1 of the BUDGET1 Sheet o Finish inputting the missing income and expense account titles. eo Widen Column A so that the longest Expense title fits in the column without running over into Column B. Fast & Use the Fill Handle to complete the monthly column headings Fast & Use the Spelling icon on the Review Tab in the Proofing Group to detect misspelled words and typos. Save-A Resist the temptation to print the EXAMPLE sheet. Instead copy the object from the Example sheet onto the BUDGET1 sheet. Move it out of the way as you fill in the information. When you are done with Step 1, but before you print, delete the object as you don't need it anymore. Enter Income and Expense Amounts o input the income and Expense amounts and the January Beginning Cash, DO NOT INPUT to the TOTALS columns and rows Fast & Notice that many of the Income and Expense amounts are the same from month to month. For most accounts, Input January data and then copy the amounts to Feb through Dec. Note: The following accounts have varying monthly amounts INCOME: Salary, Other EXPENSE: Auto Repair, School, Vacation 58 Use Sum Function 5 o Use the AutoSum icon to put =SUMO) formulas for Total Income, Total Expense, and the Annual Total of each Income and Expense category. 61 Fast & If you highlight the area B6:N10 and click the AutoSum icon all your totals for Income will be created. Manual Formula Reference O NET Input the formula for NET as Total Income - Total Expense for January Copy the formula to the remaining months and annual total. GO BEGINNING CASH You should have an amount in the January beginning cash already For February reference the Ending Cash cell from January, =B33 Copy the formula to the remaining months. 71 12 O ENDING CASH Input an =SUM formula that sums Net and Beginning Cash Copy the formula to the remaining months. 66 68 69 70 73 TE 75 75 Verify To Check your various totals and make sure they agree to the EXAMPLE sheet P 19 R S T U y Format 30 o Try to format the schedule to be exactly like the Example o Globally set the format for the BUDGET1 Sheet to be comma","and zero decimal places wo Set the font size and bolding of the different titles o Indent the accounts and subtotals using the Indent Icon on the formatting tool bar o Underline, bold, and center the column headings 15 o Use the Borders icon to put the proper underscores and double underscores 18 o Add $ dollar signs to the rows that start a totaling or are directly under an underscore by highlighting the area and using the $ icon on the format toolbar and then reducing the decimal places 37 09 50 Print o Print Preview Your Schedule and make sure that it fits on one page landscape. 20 Print Completed Schedule (with Row and Column Headings) o Print Formula view of your schedule using the Ctrl-. The schedule must be of a size that can be easily read. Reduce the column widths where appropriate to increase the font size. It is OK if titles in Column A are truncated in this view. % Fast & Keep track of how many column widths you reduced. After printing the formula sheet, click the Undo icon an equal number of times to return your schedule to its previous look. 94 95 95 99 FYI: Because you are printing with the Column and Row Headers turned ON, your indented text will appear as if it was NOT indented on the printout. The Instructor is aware of this issue and it is OK. 100 Instructions Engle Budget 104 mos 03 2 What IF It looks like there are going to be some months where you are going to fall short on cash. Increase the January - Beginning Cash to the exact amount necessary so that Ending Cash never goes below zero in any month. Make Sure You Understand the Requirement: NO month should have an Ending Cash Balance that is a negative amount AND at least one month should have an Ending Cash Balance that is ZERO. Print Completed Schedule mo 100 10 11 12 - 3 Critical Thinking 114 Return the January Beginning Cash Balance in your schedule to the original amount as you have determined that 5 increasing your beginning cash for January is not possible. You called Dad to see if he could increase your 11 monthly stipend, but every time you mentioned needing more money (mysteriously) the cell phone connection would drop. Your study schedule would suffer significantly if you tried to work more hours. It looks like you are going to have to cut expenses In business we view some expenses as necessary and some expenses as discretionary. Discretionary expenses are those that could be discontinued or postponed without detrimentally impacting the normal operating goals of the 117 118 120 121 Check on 113 cut expenses. 113 120 121 122 125 In business we view some expenses as necessary and some expenses as discretionary. Discretionary expenses are those that could be discontinued or postponed without detrimentally impacting the normal operating goals of the business. As individuals we have to prioritize our spending. Some spending is critical to us achieving lifestyle and career 12 goals; this spending has priority in our budget. Other spending may fulfill an immediate desire but has little long-term 124 impact Consider the expense categories and identify the three discretionary expenses that you believe could be reduced or eliminated given your priorities described in the Situation section above. Briefly justify why you feel the expense is 127 discretionary. (Do not use other as one of your discretionary expenses.) Mark your answers in the space provided on the Check-off sheet. Adjust the spending in the discretionary categories you just identified and produce a revised budget that prevents a 12 negative cash balance in any month, but allows as balanced an amount of discretionary spending as possible. Print Revised Budget Schedule 129 130 131 133 134 1 END PROBLEM 179 10 Chickor P e S Below is another example to help you with your formulas. Jul Aug Sep Oct Nov Dec Total $ 1,300 $ 960 $ 650 $ 650 $ 650 $ 650 $ 9,680 5 5 5 5 5 60 700 700 700 700 700 700 8,400 200 300 $ 2,005 $ 1,665 $ 1,355 $ 1,355 $ 1,355 $ 1,555 S 18.440 FOLLOW *** $ 600 $ 600 $ 600 $ 600 $ 600 $ 600 $ 7,200 75 75 75 75 75 75 900 150 150 150 150 150 150 1.800 400 450 15 15 15 15 15 15 180 55 55 55 55 55 55 660 40 40 40 40 40 40 480 220 220 220 220 220 220 2.640 100 100 100 100 100 100 1.200 60 60 60 60 60 720 80 80 80 80 80 80 960 3 100 100 100 100 100 100 1,200 575 1,150 700 700 10 10 10 10 10 10 120 5 $ 2.205 $ 2.080 $ 1,505 S 1505 5 1,505 $ 1905 S 20,360 60 $ (200) S (415) $ (150) S(150) S(150) $ (350) $ (1.920) 495 295 (120) (270) (420) (570) $ 295 $(120) $ (270) S (420) $ (570) $ (920) Limited View of Formulas B Single Accounting Underline. See if you can find it in Excel if not, Just use the underline icon. Jan Feb Mar - coloolwoloslaw N- 640 5 700 640 5 700 640 5 700 SUM(B6:B9) SUM(C6:09) SUM(D6:09) 600 75 150 600 75 150 600 75 150 1 Student Name 2 Personal Budget 3 Monthly 4 5 Income: Salary, net Interest & Dividends Parent Subsidy Other 10 Total Income 11 12 Expenses: 13 Apartment Rent 14 Car Fuel 15 Auto Insurance 16 Auto Repair and Service 17 Renters Insurance 18 Telephone & Internet 19 Cable TV 20 Groceries 21 Dining Out 22 Beer 23 Clothing 24 Entertainment 25 School 26 Vacation 27 Other 28 Total Expenses 29 30 Net 31 32 Beginning Cash 33 Ending Cash 15 55 40 15 55 40 220 100 60 80 220 100 60 80 100 15 55 40 220 100 60 80 100 100 575 10 =SUM(813.827) 10 10 =SUM(C13:C27) SUM(D13:D27) =B10-B28 =C10-C28 =D10-D28 1000 =B33 =C33 ESUM(B30:832) ESUM(C30:C82) =SUM(D30 D32) D E F G 640 A B 1 Student Name 2 Personal Budget 3 Monthly 4 Jan Feb 5 Income 6 Salary, nel 640 7 8 9 10 Total Income 11 12 Expenses 13 14 15 16 Auto Repair and Service 17 Renters Insurance 18 Telephone & Internet 19 20 21 22 23 24 Entertainment 25 School 26 Vacation 27 Other 28 Total Expenses 29 30 Net 31 32 Beginning 1000 33 Ending Cash 34 35 36 37 38 39 40 41 42 43 44 45 Check-Off Instructions Example Budget1