Analyzing Data in a Personal Budget Workbook

  • Uncategorized

AnalyzingData in a Personal Budget Workbook

MicrosoftExcel is a useful accounting tool. It helps to clearly present data.This paper will use Excel features in analyzingdata in a personal budget workbook.

Analysisof Income

Theworkbook provides a comprehensive analysis of a personal budget. Thebudget classifies income as the business income, employment income orother income. The greatest source of income is the employment income.The highest amount of employment income, $16, 7812 is in January.From February to December, the monthly income is $12,782. Theemployment income for January may have been higher than the incomefor the other months due to bonuses for the previous year (thedifference is $5,000). Companies usually pay bonuses as an incentiveto workers. The income for the year is $157,384.

Thehighest business income is $8,272 (in July) while the lowest is$4,162 (in January). On average, the business income is $5,399.08.

Thetotal amount from other sources of income for the whole year is$48,835. These sources averagely earn $4,069.58. The maximum annualamount, earned from these sources, for the 12 months is $9,102 whilethe minimum is $232.

Excelscenario manager makes automating what if models easy (Walkenbach,2013).The what-if examination in the workbook uses the scenario manager. Itclearly indicates the behavior of the net income/ loss when businessincome increases or decreases. The analysis is for the month ofDecember. The table below clearly illustrates that.

DECEMBER BUDGET

Scenario Summary

&nbsp

&nbsp

&nbsp

&nbsp

Current Values:

High business income

Low Business Income

Changing Cells:

&nbsp

&nbsp

&nbsp

&nbsp

$D$5

$ 6,272.00

$ 7,000.00

$ 5,000.00

Result Cells:

&nbsp

&nbsp

&nbsp

&nbsp

$D$103

$ 4,185.00

$ 4,913.00

$ 2,913.00

Analysisof Expenses

Domesticexpenses total to $101,230 in a year which is an average of 7,786.92.Most of it, $ 30,204, is rent. The most amount of money is on cablepayment which amounts to $1,452.

Healthexpenses total to $18,406 in a year and average to $3,067.67. Most ofthe healthcare expense, $3,888, is due to the health insurance whileleast amount of money, $1200 is spent on pet care in a year.

$22,510is the total spent annually on transport. Most of the transport cost$5,400 is for repair and maintenance cost while the least is for thecar insurance. The average spent on transportation is $3,751.67.

Dailyliving expenses consume a considerable amount of money. The totalcost is $53,261. On average 5,917.89 is spent on items related todaily living expenses. Most money, $15,024, goes to education andlessons while the least goes into paying the barber ($2,532).

Theamount for entertainment-related expenses is $27,478 in a year. Theaverage amount spent on all forms of entertainment is $2,747.8. Mostmoney $ 4,980, is spent on vacations while the least, $1,344, isallocated to photos and film.

Ina year $25,672 goes into saving, and investment and the averagesavings, considering savings related activities are $ 5,134.4. Mostmoney $ 7,404 goes into investment and the least money $3,852 goesinto the emergency fund.

$6,129pays for all the subscriptions in a year. On average $1,532 goes intopaying for subscriptions. Most money, $1980, goes into subscriptiondues while the least,$1089, goes into other subscription relatedexpenses.

Loansand other debt liabilities cost $ 17,637. The average of theseexpenses is $ 3,527.4. The most $4,980 is due to a personal bank loanand the least, $2,325, due to other liabilities.

Conclusion

Inconclusion, the biggest expense, $101,230, are domestic expenses. Ofthese expenses, rent is the highest and costs $30,204 annually. Theleast amount of money, $6,129 goes into the subscriptions. Of theseexpenses, other subscription related expenses cost the least, $1089,annually. It is important during the analysis, to note that pivottables do not automatically update to reflect changes in source data.If a model relies on pivot tables, these need updating (Marsh,2013).&nbspThepivot table and pivot chart are in the budget workbook.

References

Marsh,C. (2013).&nbspBusinessand financial models.London Philadelphia, PA&nbsp

Walkenbach,J. (2013).&nbspExcel2007 bible.Hoboken, N.J: Wiley.

Close Menu