Scenario Part 1
You have decided that it might be a good idea to create a personalhousehold budget for yourself. You know you are able to pay allyour bills, attend school, and even have money left for fun geta-ways. But you really want to be able to save money to purchase ahouse after you graduate with your degree.
You decide to create a personal budget and decide to use MS Excel2007 to help you with this. You begin to gather your pay stubs andall the bills you have to pay each month and sit down in front ofyour computer and open Excel 2007.
Project Details
Open Microsoft Excel 2007. You will begin with a newworksheet.
Step 1: Create and format a row to show the months for the year(Jan-Dec).
Step 2: Create and format a row, beneath the month row, to showyearly income for each month (Jan-Dec), and properly label the rowand value.
Step 3: Add each expense in a separate row (as identified in step4) and properly label each row and value.
Step 4: For the expenses and income, create a year's history bymonth. Use different values for each month. For example, forelectric, you would pick a number between $125 and $142 forJanuary, a different number between $125 and $142 for February, andso on. Feel free to make up the amounts you use for eachexpense.
Step 5: Select an appropriate style for your budget and apply it toyour worksheet.
Project Part 2
Now that you have all the appropriate budget data added to yourworkbook, you want to analyze it to determine how much yourexpenses are each month and year as well as how much you make peryear to determine if you are spending too much and where you arespending it.
NOTE: For this part you will add formulas to your workbook. In thefollowing unit you will create a chart to visually show yourexpenses.
Step 6: Create formulas or use the AutoSum function to have Excelcalculate total the expense for each month.
Step 7: Create formulas or use the AutoSum function to have Excelcompute the Total Annual Income.
Step 8: Create a formula or use the AutoSum function to have Excelcalculate the Total Annual Expenses and properly label it.
Step 9: Create a formula to determine the percentage of income usedfor each expense. Be sure to format it as a percentage. Place thatformula in the column next to the total of each monthly expense.(GEL 2.3)
Step 10: Rename your worksheet tab to Budget Detail.
You have decided that it might be a good idea to create a personalhousehold budget for yourself. You know you are able to pay allyour bills, attend school, and even have money left for fun geta-ways. But you really want to be able to save money to purchase ahouse after you graduate with your degree.
You decide to create a personal budget and decide to use MS Excel2007 to help you with this. You begin to gather your pay stubs andall the bills you have to pay each month and sit down in front ofyour computer and open Excel 2007.
Project Details
Open Microsoft Excel 2007. You will begin with a newworksheet.
Step 1: Create and format a row to show the months for the year(Jan-Dec).
Step 2: Create and format a row, beneath the month row, to showyearly income for each month (Jan-Dec), and properly label the rowand value.
Step 3: Add each expense in a separate row (as identified in step4) and properly label each row and value.
Step 4: For the expenses and income, create a year's history bymonth. Use different values for each month. For example, forelectric, you would pick a number between $125 and $142 forJanuary, a different number between $125 and $142 for February, andso on. Feel free to make up the amounts you use for eachexpense.
Step 5: Select an appropriate style for your budget and apply it toyour worksheet.
Project Part 2
Now that you have all the appropriate budget data added to yourworkbook, you want to analyze it to determine how much yourexpenses are each month and year as well as how much you make peryear to determine if you are spending too much and where you arespending it.
NOTE: For this part you will add formulas to your workbook. In thefollowing unit you will create a chart to visually show yourexpenses.
Step 6: Create formulas or use the AutoSum function to have Excelcalculate total the expense for each month.
Step 7: Create formulas or use the AutoSum function to have Excelcompute the Total Annual Income.
Step 8: Create a formula or use the AutoSum function to have Excelcalculate the Total Annual Expenses and properly label it.
Step 9: Create a formula to determine the percentage of income usedfor each expense. Be sure to format it as a percentage. Place thatformula in the column next to the total of each monthly expense.(GEL 2.3)
Step 10: Rename your worksheet tab to Budget Detail.
Related questions
In proper General Journal format,record the transactions for the month; include descriptions of eachtransaction with your journal entry. Use only the accounts shown inthe chart of accounts.
8/1 YOUfiled a charter with the State of Louisiana to form the YOURAccounting Corporation. The charter authorizes you to issue 5000shares of $2 par common stock. The state charged you a $75 fee tofile the charter. Since your business is not yet approved, you hadto pay this fee using personal funds.
9/1 Youreceived your charter from the State of Louisiana and officiallyopened your business. Your first order of business wasto become a shareholder of YOUR Accounting Corp. To do this, youpurchased 500 shares of common stock by issuing a check to YOURAccounting Corp for $10,000. You used this money to open a checkingaccount at First Funds Bank.
9/1 Yourented an office for YOUR Accounting Corp. The monthly rent is$500, with the first monthâs rent due immediately. You issue check#100 to Office Builders for the first monthâs rent.
9/2 Youthen went to the Apple store and purchased a new computer systemfor your business. Your Mac Pro cost $2700 and your new printercost $450. You set up a 30-day account with Apple to make thispurchase.
9/3 Youordered business cards and stationary from Marketing Media onaccount. The order totaled $250 and will be shipped FOBDestination. (Record all purchases of supplies in the Supplies onHand account).
9/4 Youdecided to purchase a new vehicle for your business. Upon visitingPro-Auto, you decide on a new SUV at a cost of $55,000. Thisvehicle will be used 100% for business purposes. You finance thevehicle with Pig E Bank at a rate of 5% for 6 years. Your firstmonthly payment is due on October 4.
NOTE: You will need to create a loan amortization schedule todetermine the amount of the monthly note and the interest expensefor each month. You can use a website such as www.bankrate.com tocreate the schedule. When recording your journal entries, round allamounts to the nearest dollar.
9/5 Youwent to the KEM Supply to purchase supplies for your business at acost of $600. KEM opened a customer charge account for you. Thepayment terms on your account will be 2/10, net 30. The time periodfor determining the payment amount begins on the purchase date.
9/6 Youpurchased a one-year auto insurance policy from InsureMart for$1200. InsureMart will send you a bill for the policy. YOURAccounting Corporation capitalizes all insurance policies on thedate of purchase and records the necessary expense at year-end asan adjusting entry.
9/10 Your firstclient, Red Fische, came in today needing assistance with filingthe appropriate paperwork to start his new seafood restaurant. Youissued invoice #1 to Red Fische and he paid you an initial $2,000Engagement Fee. Red Fische also agreed to contract with you toprovide accounting services for $2,500 per month.
9/12 You issue check#101 to YOUR Accounting Corp to establish a $500 Petty Cash Fund.You will use this account to make small cash purchases.
9/12 You reimburseyourself for the filing fees associated with forming yourcorporation.
9/14 You paid KEM Supply by issuingcheck #102
9/15 You hire anadministrative assistant, Mandi Handi, she will be paid a monthlysalary of $1500. You have decided that all pay periods will end onthe last day of the month and that checks will be issued on the5th of each month.
NOTE: Assume the following rates when preparing the payroll:federal income tax 15%, state income tax 5%, and FICA 7.65%.
YOUR Accounting Corp. has state and federal unemployment insurancerates of 1% (FUTA) and 2% (SUTA) on the first $7,700 of wages peremployee. The employer FICA rate is 7.65%.
9/16 Marketing Mediadelivered your business cards and stationary. Check #103 was issuedto pay for the supplies.
9/20 You visited anew client, Anita Cooke, to set up a Quickbooks accounting systemfor her new business, Cooking For You. You gave Anita and invoice#2 for the Engagement Fee and she paid you by issuing a check inthe amount of $2,000. Anita also agreed to a monthly fee of $1,500for you to handle her ongoing accounting needs.
9/22 You purchased$50 of fuel for your new SUV from Get ân Go. You charged this toyour Get ân Go account.
9/30 Mandi sentpro-rated invoices, #3 & #4 , to Red Fische and Cooking For Youfor Monthly Accounting Services. The payment terms are 1/10, net30.
9/30 You accruedinterest on the Pig E. Bank note. Accrue interest based on thenumber of days in the month.
9/30 You computedand accrued the payroll for September.
9/30 You receivedmonthly bills for the following:
Max Power Company - $100, terms n/30
WaterWorks #1 - $20, terms n/30.
CHART OF ACCOUNTS:
| Cash | 105 | ||
| Petty Cash | 107 | ||
| Accounts Receivable | 110 | ||
| Supplies on Hand | 130 | ||
| Prepaid Insurance | 140 | ||
| Computer Equipment | 220 | ||
| AccoumulatedDepreciation - Computer Equipment | 221 | ||
| Cell Phone | 230 | ||
| AccoumulatedDepreciation - Cell Phone | 231 | ||
| Vehicles | 240 | ||
| AccumulatedDepreciation - Vehicles | 241 | ||
| Accounts Payable | 310 | ||
| Customer Deposits (UnearnedRevenue) | 320 | ||
| SUTA Payable | 330 | ||
| FICA Payable | 332 | ||
| FUTA Payable | 334 | ||
| Federal Income TaxPayable | 336 | ||
| State Income Tax Payable | 338 | ||
| Current Maturities ofLong-Term Debt | 375 | ||
| Notes Payable (long-term) | 410 | ||
| Interest Payable | 420 | ||
| Salaries Payable | 425 | ||
| Common Stock ($2 parvalue) | 520 | ||
| Additional Paid-in Capital onCommon Stock | 521 | ||
| Retained Earnings | 550 | ||
| Dividends | 560 | ||
| Engagement Fees | 605 | ||
| Monthly Accounting ServicesRevenue | 610 | ||
| Hourly Accounting ServicesRevenue | 620 | ||
| Tax Services Revenue | 612 | ||
| Sales Discounts | 614 | ||
| Advertising & PromotionExpense | 725 | ||
| Depreciation Expense | 727 | ||
| Rent Expense | 730 | ||
| Insurance Expense | 735 | ||
| Supplies Expense | 740 | ||
| Meals & Entertainment | 745 | ||
| Taxes & Licenses | 767 | ||
| Telephone Expense | 770 | ||
| Utilities Expense | 775 | ||
| Fuel Expense | 780 | ||
| Interest Expense | 820 | ||
| Payroll Tax Expense | 825 | ||
| Salaries Expense | 830 | ||
| Income Summary | 900 | ||
