Upon graduation from college in May, you decide to establish a monthly budget to include the following items 1 Gross Starting Annual Salary $30,000. (After 3 months you will get a 10% increase.) Withholdings (Income tax soal security. Medicare, health insurance, etc - 25 percent of gross salary) 3. Food/householdina dentals (Monthly, you select the amount) Monthly Rent (You select the amount) Utilities (monthly, 20% of rent) Auto payment (Monthly, per amortization schedule you will prepare) Auto gas and maintenance (Monthly, you select the amount) Auto Insurance (You select the amount that can be paid annually, semi-annually. quarterly, or monthly) 9. Savings (monthly, 5% percent of gross salary) 10. Clothing (You select the amount and when to spend it). 11. Entertainment (as a percentage of salary, monthly) 12. Anything else you want to include As a graduation present, you receive $10,000 cash on May 31 to help you with "startup" expenses as you join the working world You take the month of June to find a place to live, pay the first month's rent and a deposit equal to one month's rent, and move in on July 1. You purchase a car in June, making a minimum down payment of $2,000, and finanang the balance over 5 to 7 years with monthly payments beginning in July. You choose the purchase price of the car, the interest rate, and the financing period You begin work on July 1 and are paid on the 15th and last day of each month Utilities are due on the 10th of the month following the month used You are to prepare projected personal finana al statements for six months (July through December) using EXCEL including separate worksheets for a balance sheet, a statement of cash receipts and disbursements, and an amortization schedule for the purchase ofyour car. The amortization schedule must link to the balance sheet (asset and related liability) and the statement of cash receipts and disbursements (for the payments made). The statement of cash receipts and disbursements must then link to the balance sheet (cash balance, capital account etc.) B I c 1. 3 4 5 Initial Salary Salary increase withholdings utilities savings Entertainment Loan 10 11 12 13 14 15 16 Cover Assumptions CashStmt Balance Sheet Amor 2 Cash Receipts and Disbursements 3 Month Ended 2020 5 For the month of August Sept. Oct. June July Nov. Dec. 7 Cash Receipts: 8 Gross Salary 9 Cash Disbursements: 10 Withhholdings (25%) 11 Rent 12 Rent Deposit 13 Food/household incidentals 14 Utilities (20%) 15 Auto Monthly Payment 16 Auto Down Payment 17 Auto Insurance 18 Gas and Auto Maintenance 19 Retirement Savings (5%) 20 Clothing 21 Entertainment 22 Total Disbursements 23 Net Cash Inflows (Outflows) 24 Beginning Cash Balance 25 Ending Cash Balance E Cover Assumptions CashStmt BalanceSheet Amortization June July Aug Sept. Oct. Nov. Dec. 2 Balance Sheet 3 Month Ended 2020 May 5 Cash 6 Retirement Savings 7 Automobile Rent Deposit 9 Total Assets 10 Automobile Loan 11 Capital 12 Total Liabilities and Capital 16 NOTE: The monthly auto loan payment reduces the liabiity and thereby increases the amount of equity in the automobile. 19 To keep in balance, you can either increase capital by the amount of principal payment on the auto or decrease the amount of the vehicle value. 20 This one decreases the vehicle value since in essence the actual value of the vehicle declines over time. Upon graduation from college in May, you decide to establish a monthly budget to include the following items 1 Gross Starting Annual Salary $30,000. (After 3 months you will get a 10% increase.) Withholdings (Income tax soal security. Medicare, health insurance, etc - 25 percent of gross salary) 3. Food/householdina dentals (Monthly, you select the amount) Monthly Rent (You select the amount) Utilities (monthly, 20% of rent) Auto payment (Monthly, per amortization schedule you will prepare) Auto gas and maintenance (Monthly, you select the amount) Auto Insurance (You select the amount that can be paid annually, semi-annually. quarterly, or monthly) 9. Savings (monthly, 5% percent of gross salary) 10. Clothing (You select the amount and when to spend it). 11. Entertainment (as a percentage of salary, monthly) 12. Anything else you want to include As a graduation present, you receive $10,000 cash on May 31 to help you with "startup" expenses as you join the working world You take the month of June to find a place to live, pay the first month's rent and a deposit equal to one month's rent, and move in on July 1. You purchase a car in June, making a minimum down payment of $2,000, and finanang the balance over 5 to 7 years with monthly payments beginning in July. You choose the purchase price of the car, the interest rate, and the financing period You begin work on July 1 and are paid on the 15th and last day of each month Utilities are due on the 10th of the month following the month used You are to prepare projected personal finana al statements for six months (July through December) using EXCEL including separate worksheets for a balance sheet, a statement of cash receipts and disbursements, and an amortization schedule for the purchase ofyour car. The amortization schedule must link to the balance sheet (asset and related liability) and the statement of cash receipts and disbursements (for the payments made). The statement of cash receipts and disbursements must then link to the balance sheet (cash balance, capital account etc.) B I c 1. 3 4 5 Initial Salary Salary increase withholdings utilities savings Entertainment Loan 10 11 12 13 14 15 16 Cover Assumptions CashStmt Balance Sheet Amor 2 Cash Receipts and Disbursements 3 Month Ended 2020 5 For the month of August Sept. Oct. June July Nov. Dec. 7 Cash Receipts: 8 Gross Salary 9 Cash Disbursements: 10 Withhholdings (25%) 11 Rent 12 Rent Deposit 13 Food/household incidentals 14 Utilities (20%) 15 Auto Monthly Payment 16 Auto Down Payment 17 Auto Insurance 18 Gas and Auto Maintenance 19 Retirement Savings (5%) 20 Clothing 21 Entertainment 22 Total Disbursements 23 Net Cash Inflows (Outflows) 24 Beginning Cash Balance 25 Ending Cash Balance E Cover Assumptions CashStmt BalanceSheet Amortization June July Aug Sept. Oct. Nov. Dec. 2 Balance Sheet 3 Month Ended 2020 May 5 Cash 6 Retirement Savings 7 Automobile Rent Deposit 9 Total Assets 10 Automobile Loan 11 Capital 12 Total Liabilities and Capital 16 NOTE: The monthly auto loan payment reduces the liabiity and thereby increases the amount of equity in the automobile. 19 To keep in balance, you can either increase capital by the amount of principal payment on the auto or decrease the amount of the vehicle value. 20 This one decreases the vehicle value since in essence the actual value of the vehicle declines over time