Show me the steps to solve Assignment DD$1$

Anju devi

LI$20240781$

Questions Part A

Preparation of accounting records $$ using spreadsheets The trial balance of Betty Investment Advisers as at $30$ June $2024$ follows:

Let's formulate five different types of adjustments and then journalize the adjusting entries.

Adjustments Details

$1.$ Accrued Service Revenue

o Details: Service revenue of $$4,000$ was earned but not yet billed to the client by June $30,2024.$

o Adjustment Date: June $30,2024$

$2.$ Depreciation

o Details: Depreciation on equipment for the year is $$2,000.$

o Adjustment Date: June $30,2024$

$3.$ Accrued Salary Expense

o Details: Salaries expense of $$5,000$ was incurred but not yet paid by June $30,2024.$

o Adjustment Date: June $30,2024$

$4.$ Expired Prepaid Rent

o Details: Prepaid rent expense of $$8,000$ for the year ending June $30,2024,$ has expired.

o Adjustment Date: June $30,2024$

$5.$ Supplies Used

o Details: Office supplies of $$2,500$ were used during the year.

o Adjustment Date: June $30,2024$

$$ Unadjusted Trial Balance Columns:

$$ Enter the account titles and corresponding debit and credit balances from the unadjusted trial balance.

$$ Adjustments Columns:

$$ Enter the adjustments in the corresponding debit and credit columns.

$$ Adjusted Trial Balance Columns:

$$ Use formulas to sum the unadjusted trial balance and adjustments for each account. For example, in cell F$2($Adjusted Trial Balance Debit for Cash$),$ enter $=$B$2+$D$2-$E$2.$

$$ Income Statement and Balance Sheet Columns:

$$ Classify each account as either an income statement account $($revenues and expenses$)$ or a balance sheet account $($assets$,$ liabilities, and equity$).$

$$ Use the IF function to separate these accounts. For example, in cell H$2($Income Statement Debit for Cash$),$ enter $=$IF$($A$2=$"Service Revenue", F$2,0).$

$$ Ensure that all debit and credit columns balance using the IF function to check for equality. For example, in cell L$2,$ enter $=$IF$($SUM$($H$2$:H$21)=$SUM$($I$2$:I$21),$ "Balanced", "Not Balanced"$).$

$$ Balance Sheet $($Credit$)$

Let's break this down step$-$by$-$step to prepare the financial statements using the ten$-$column worksheet data.

$1)$ Income Statement

The income statement summarizes revenues and expenses to calculate net income for the period. Only accounts related to revenues and expenses will be included.

Steps:

$1.$ Revenues:

o Service Revenue: $$99,000($$$95,000+$ $$4,000$ adjustment$)$

$2.$ Expenses:

o Insurance Expense: $$3,000$

o Salaries Expense: $$38,500($$$35,000+$ $$3,500$ adjustment$)$

o Supplies Expense: $$3,500($adjustment$)$

o Interest Expense: $$4,000$

o Rent Expense: $$13,000($$$8,000+$ $$5,000$ adjustment$)$

o Depreciation Expense$$Equip$.$: $$2,000($adjustment$)$

$3.$ Calculate Net Income:

o Net Income $=$ Total Revenues $-$ Total Expenses

$2)$ Statement of Changes in Equity

This statement shows changes in equity during the period, including net income and withdrawals.

Steps:

$1.$ Opening Capital Balance: $$31,000$

$2.$ Add: Net Income: $$35,000$

$3.$ Less: Withdrawals: $$27,000$

$4.$ Calculate Ending Capital:

o Ending Capital $=$ Opening Capital $+$ Net Income $-$ Withdrawals

$3)$ Classified Balance Sheet

The balance sheet shows the financial position, including assets, liabilities, and equity.

Steps:

$1.$ Current Assets:

o Cash: $$28,000$

o Accounts Receivable: $$51,000($$$47,000+$ $$4,000$ adjustment$)$

o Office Supplies: $$1,500($remaining after adjustment$)$

$2.$ Non$-$Current Assets:

o Equipment: $$21,000$

o Less: Accumulated Depreciation: $$13,000($$$11,000+$ $$2,000$ adjustment$)$

o Net Equipment: $$8,000$

$3.$ Current Liabilities:

o Accounts Payable: $$16,000$

o Salaries Payable: $$3,500($adjustment$)$

o Unearned Revenue: $$2,000$

$4.$ Non$-$Current Liabilities:

o Loans Payable: $$23,000$

$5.$ Equity:

o Frank, Capital: $$39,000($calculated in the statement of changes in equity$)$

Creating Formulas in Excel

Income Statement Formulas:

$$ Service Revenue: $=$SUM$(...)($sum of service revenue from adjusted trial balance$)$

$$ Each Expense: $=$SUM$(...)($sum of respective expense from adjusted trial balance$)$

$$ Total Expenses: $=$SUM$($B$3$:B$8)$

$$ Net Income: $=$B$2-$B$9$

Statement of Changes in Equity Formulas:

$$ Opening Capital: $=...($value from adjusted trial balance$)$

$$ Net Income: $=...($from the income statement$)$

$$ Withdrawals: $=...($value from adjusted trial balance$)$

$$ Ending Capital: $=$B$2+$B$3-$B$4$

Balance Sheet Formulas:

$$ Each Asset, Liability, and Equity account: $=$SUM$(...)($values from adjusted trial balance$)$

$$ Total Current Assets: $=$SUM$($B$2$:B$4)$

$$ Total Non$-$Current Assets: $=$B$6-$B$7$

$$ Total Assets: $=$B$9+$B$11$

$$ Total Current Liabilities: $=$SUM$($B$14$:B$16)$

$$ Total Non$-$Current Liabilities: $=$B$18$

$$ Total Liabilities: $=$B$17+$B$19$

$$ Total Liabilities and Equity: $=$B$20+$B$22$