Expert answer:excel question
Solved by verified expert:look at the attachment all of the requirement is attached
personal_finance_exercise_instructions.docx
personal_financial_statements_exercise_v1.1.xlsx
Unformatted Attachment Preview
BUS 101 Accounting and Finance Exercise, Chapters 9 and 10
Fall Semester, 2017
Complete this accounting and finance exercise by inputting figures in the YELLOW CELLS
ONLY on the “Decisions” worksheet and the “Income Statement” worksheet (please be careful as there
are formulas embedded in the spreadsheet) and providing a one page write up using bullet points shown
in lines 1 to 4 below) supporting your decisions. You must make entries in all yellow boxes with borders
in Years 1-3 on the decisions page and years 1-5 on the income statements. Yellow boxes without borders
are for optionally using cash to reduce financed balances. Using research to estimate expenses and
purchases and your decisions for optional payments, your goal is to maximize Owner’s Net Worth in
year 5 of the Balance Sheet. Throughout the exercise, your Cash Balance (on Balance Sheet) must always
remain positive (note you are allowed to have a negative net income on your Income Statement as long as
your Cash Balance remains positive. i.e. you can cover your down payment or retirement of debt with
your available cash) .
Enter information on the Decisions page as well as Income Statement for your variable expenses
(“Food” and “Travel / Entertainment”). After each year’s decisions are recorded, check the income
statement and balance sheet for the results.
1) In year 1, estimate your starting salary – use glassdoor.com for example – for a city where
you want to live. The rest of your decisions are listed in the table below and as yellow blocks on the
Decisions and Income Statement worksheets. For your furniture purchase, use Ikea or any other
online retailer for all the furnishings you need for your apartment. Finance terms are embedded in the
formulas in the spreadsheet, just enter the total dollar amount of the purchase. You will also estimate
monthly costs for Food and annual costs for Travel / Entertainment. Use amounts that are reasonable
and allow you to live a lifestyle that you are accustomed to. Enter only in the yellow blocks, the
formulas will do the rest.
2) In year 2, you decide to buy a car. Research the price of the car you want and enter the
purchase price in the spreadsheet. Taxes, license, registration and finance terms are embedded for
you. You have the option to finance the entire purchase or use available cash as a down payment.
When deciding, try using the cash for other purposes, such as paying off other debt, and check to see
which option is best for you financially (hint: the one that increases Owner’s Net Worth). Finally, you
will need to purchase Auto Insurance based on your actual or estimated Insurance premiums and enter
the monthly amount in the income statement.
3) In year 3, you will purchase a house with a mortgage. All the terms of the mortgage and
the costs of purchase are in the spreadsheet and included on the decisions page. Support the house
price based on where you would live and what type of house you would buy (zillow.com provides
home descriptions and prices throughout the USA). You will also purchase and finance more
furnishings.
4) In year 4 and 5 you have the option to retain cash or use cash to pay some or all of the
balances on financed accounts.
Decisions made in prior years will continue to affect your net worth The income statements have
formulas to adjust for loan amortization and inflationary increases in expenses. The year 5 income
statement and balance sheet will confirm decisions in years 1 through 4.
The exercise allows you to experiment by paying off (or down) some of the financed amount and
check to see how your net worth changes. To pay off or pay down any financed amount you must have at
least that amount of available cash in the current year’s balance sheet. A yellow block is provided for
optional cash pay downs to each financed purchase.
Your summary MUST provide support and rationale for your final inputs, after varying decisions to
maximize net worth using the statements below for years 1-5.
BUS 101 Accounting and Finance Exercise, Chapters 9 and 10
Fall Semester, 2017
Finally, provide 2 or 3 sentences stating whether this exercise has value for you and why. Do you
believe an exercise of this type is useful for BUS101? What changes do you recommend?
YEAR 1
1)
2)
3)
4)
5)
Explain your starting salary and the source of your information
Describe your first furniture/furnishings decision and the source of your cost estimates
Describe the size, layout and location of your apartment and how you reached the decision
Describe your rationale for monthly transportation costs
Explain your cost estimate for food and household supplies. Enter a monthly cost for years 2-5
and explain the increase or decrease. Costs generally increase from year to year.
YEAR 2
1) Describe the car you purchased, why you want its features, and how you determined the price
2) Estimate your annual auto insurance premium and operating costs
YEAR 3
1) Describe your rationale for the size, location and rationale of the home purchase.
2) Describe your second furniture/furnishings decision and the source of your cost estimates
OPTIONAL CASH PAYMENTS
1) For each cash payment, explain your chose and how net worth increased
Decision Entry – All decisions are effect
YEAR 1
Period =
YEAR 2
Monthly
Annual Salary
Annual
Apartment Rental
Sq. Ft.
Rent @1.25/sf
Insurance
Utilities
Furniture Purchase 1
Price
Sales Tax
IR
Years
PMT
Year 1 Principal Balance
Pd
Bal
0
0
0
0
0
0
Monthly
Annual Salary
Apartment Rental
Sq. Ft.
0
Rent
Insurance
Utilities
0
0
0
Furniture Purchase 1
Balance
0
Annual
0
0
0
0
0
10%
5
0
0
0
Down Pmnt
IR
Years
PMT
Year 2 Principal Balance
Pd
Bal
Auto Purchase
Price
Tax, Title, Reg.
Down Pmnt
IR
Years
PMT
Year 1 Principal Balance
Pd
Bal
10%
5
0
0
0
0
6.5%
5
0
0
0
`
All decisions are effective January 1st of the current year
YEAR 3
YEAR 4
Monthly
Annual Salary
Furniture Purchase 1
Balance
Down Pmnt
IR
Years
PMT
Year 3 Principal Balance
Pd
Bal
Annual
0
Furniture Purchase 1
Balance
0
0
0
Down Pmnt
IR
Years
PMT
Year 4 Principal Balance
Pd
Bal
0
Balance
10%
5
0
Auto Purchase
Balance
Down Pmnt
IR
Years
PMT
Year 2 Principal Balance
Pd
Bal
6.5%
5
0
0
0
0
0
0
Year 1 Interest Paid
Furniture Purchase 2
Price
Sales Tax
IR
Annual
0
Annual Salary
Furniture Purchase 1
Balance
0
10%
5
0
Down Pmnt
IR
Years
PMT
Year 5 Principal Balance
0
0
Auto Purchase
Home Purchase
Price
Sq. Ft.
Down Payment @ 3%
Closing Costs @ 1.5%
PMT (30 years@4.5%)
Year 1 Principal Balance
Pd
Bal
YEAR 5
Monthly
Annual Salary
Down Pmnt
IR
Years
PMT
Year 3 Principal Balance
Pd
Bal
Auto Purchase
0
6.5%
5
0
Down Pmnt
IR
Years
PMT
Year 4 Principal Balance
0
0
Home Purchase
Balance
0
Down Pmnt
PMT
0
0
Year 2 Interest Paid
0
0
0
Year 2 Principal Balance
Pd
Bal
0
0
Furniture Purchase 2
Balance
0
0
Balance
Home Purchase
Balance
0
Down Pmnt
PMT
Year 3 Interest Paid
Year 3 Principal Balance
Furniture Purchase 2
Balance
0
10%
Down Pmnt
IR
10%
Down Pmnt
IR
Years
PMT
Year 1 Principal Balance
Pd
Bal
5
0
0
0
Years
PMT
Year 2 Principal Balance
Pd
Bal
5
0
0
0
Years
PMT
Year 3 Principal Balance
YEAR 5
Monthly
Annual Salary
Annual
0
Furniture Purchase 1
0
Down Pmnt
10%
5
0
Year 5 Principal Balance
Pd
Bal
0
0
Auto Purchase
0
Down Pmnt
6.5%
5
0
Year 4 Principal Balance
Pd
Bal
0
0
Home Purchase
0
Down Pmnt
0
Year 3 Interest Paid
0
Year 3 Principal Balance
Pd
Bal
0
0
Furniture Purchase 2
0
Down Pmnt
10%
0
5
0
Year 3 Principal Balance
Pd
Bal
0
0
Income Statement Year 1
Year 2
Year 3
Year 4
Year 5
Income Statements in U.S. Dollars as of December 31st
Revenue
Salary/Wages
Gross Income
$
–
$
$
–
–
$
$
–
–
$
$
–
–
$
$
–
–
$
–
Expenses
Payroll Taxes
SSA @ 6.2%
Medicare @ 1.4%
Transportation:
Public/Uber/Other (Monthly)
Car Purchase Yr 2: Payment
Insurance (Monthly)
Maintenance/Gas/Repairs
401K Savings
Medical Insurance
Furnishings 1 Payment
Furnishings 2 Payment
Rent/Mortgage Payment
Renters’/Homeowner’s Insurance
Property Tax
Food/Household Supplies (Monthly)
Telephone/Wifi/Cable
Travel/Entertainment
Utilities
Down Payment/Principal Payments
Federal Income Tax
Gross Income
Standard Deduction
Mortgage Int. Ded.
Prop. Tax Deduction
401 K Savings Deduction
Adjusted Gross Income
Net Tax
Total Expenses
$
150
$
$
–
$
$
–
$
$
–
$
$
–
$
$
–
$
–
$
$
$
–
$
$
$
$
$
$
$
–
$
$
–
$
$
–
$
$
1,800
$
$ 1,836
$
$
$
$
$
$
$
$
$
$
$
$
$
1,873
$
$
$
$
$
$
$
$
$
$
$
$
$
1,910
$
$
$
$
$
$
$
$
$
$
$
$
$
1,948
$
–
$
$
$
$
–
$
$
–
$
$
–
–
`
$
$ 6,350
$
$ 6,477
$
$ (6,350)
$ (5,849) $
$
$ (6,477)
$ (5,998) $ (5,998)
(5,849)
Net Income
$
$ 6,607
$
$
$
$ (6,607)
$ (6,031) $
$ (4,049)
$
(4,162)
$ 4,049
$
4,162
$
$
$
$
$
$
$
$
$
(6,031)
6,739
(6,739)
(6,065) $
$
$
$
$
$
$
$
(6,065)
6,873
(6,873)
(6,099) $
(6,099)
$ (4,158)
$
(4,155)
$
(4,151)
$
4,158
$
4,155
$
4,151
–
$
–
$
–
$
–
4,162
$
4,158
$
4,155
$
4,151
Other Income
Employer 401K Match
Total Other Income
Net Income (Loss)
$
–
$
$
–
$ 4,049
–
$
–
$
–
$
–
Balance Sheets
Year 1
Balance Sheets as of December 31st
Year 2
Year 3
Year 4
Year 5
Current Assets
Cash Balance
$
4,049
$
8,211
$
12,369
$
16,524
$
401K Balance
$
–
$
–
$
–
$
–
$
20,675
–
Total
$
4,049
$
8,211
$
12,369
$
16,524
$
20,675
$
–
$
–
$
–
$
–
$
–
$
–
$
–
Fixed Assets
Home
Car
Furniture
$
–
$
–
$
–
$
–
$
–
Less accumulated depreciation (Negative Value)
$
–
$
–
$
–
$
–
$
–
Total
$
–
$
–
$
–
$
–
$
–
$
4,049
$
8,211
$
12,369
$
16,524
$
20,675
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
Mortgage payable
$
–
$
–
$
–
$
–
$
–
Auto
$
–
$
–
$
–
$
–
$
–
Furniture
$
–
$
–
$
–
$
–
$
–
Total Long-term Liabilities
$
–
$
–
$
–
$
–
$
Total Assets
Current Liabilities (Next 12 Months)
Current Portion of Long Term Debt
Other
Total Current Liabilities
Long-term Liabilities
Total Liabilities
Owner’s Net Worth
$
$
4,049
$
$
8,211
$
$
12,369
$
$
16,524
$
$
20,675
…
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades