BudgetingCaseStudy (2).xlsx

	C	D	E	H	I	J	K	L	N	O	P	Q	R	S	T	V	W
1
2	FDMAT 108 - Budgeting Case Study Excel File
3	Step 1.	Hover Mouse here to begin.					2-Semester Estimated Total	#3		Seth's Receipt Record						#1	Seth's Background Story
4				Semesterly Income		January	2-Semester Estimated Total
5	Step 2.	Type in Seth's Remaining Receipts.			Financial Aid	500.00		Hint		Date	Store	Details	Amount	Expense Category	Payment Method	Seth has just finished the first month of his first semester of college. He has decided it is finally time to take the advice he heard in one of his classes and start his own personal budget! What he typed in already... Seth has collected the receipts from all his purchases since the start of the semester. Today he finally sat down and started typing his receipts into his budget. Your job is to help him finish. Seth arrived at school on January 3rd and did a lot of spending. As you can see in "Seth's Receipt Record" from the receipts he already typed in, he bought groceries for $150.78, paid his housing contract for the semester at $975, paid his semester's tuition of $1,960, and bought his textbooks for $385.42. He received a $500 scholarship that he will get every semester. He also received a $50 gift from his parents, which was nice, but will probably never happen again. He paid tithing of $55 for the gift and scholarship money he had received. First things first, Seth took his friend Sarah out on a date for ice cream on January 5th, which cost $4.88. He then paid a $5.00 Fast Offering at church on January 6th. On the first day of school, January 7th, he forgot his lunch and bought lunch at the Crossroads Cafeteria, which cost $9.75. The next day he got his first paycheck for $67.50, which had his taxes of $15.52 taken out directly. He also put $20 worth of gas in his car and paid his tithing that night online for his paycheck ($6.75) his scholarship ($50) and the gift from his parents ($5), a total tithing payment of $61.75. The next day, January 9th, he purchased notebooks and pens from the Bookstore for $14.25. Later that day he returned to buy some ear muffs for $4.89. Rexburg was colder than he expected! He also bought lunch at the Crossroads Cafeteria for both Sarah and himself for $19.38. Friday the 10th he went out with his friends to play lazer tag for $32. Saturday the 11th he bought some weekly groceries for $75.32. That night he went to dinner and bowling with Sarah and some friends for $28.50. On the 18th he bought groceries again for $148.22. He also decided to buy a gift for his friend Sarah for $58.39 since he was out shopping already. Later that night he remembered to pay his monthly cell phone bill online for $99.98. He was happy to get his second paycheck of the usual $67.50 on the 23rd and recorded that $15.52 in taxes that was removed. What he needs help with... Now is where Step #2 begins. Help Seth finish typing in his remaining three receipts for January (shown to the left). He purchased "weekly groceries", put in his "regular fill up for my car", and paid his tithing, or as he would write in the description: "blessings!" One final thing needed for Steps #3 and #4... Prior to the start of school, Seth had saved up $8,615. He needs your help in coming up with a Monthly Spending Goal that ensures he doesn't run out of money before the end of the first year of school. In short, he needs a budget plan that accounts for his January spending and will last for the remaining 7 months of school.
6					Other Income	50.00				1/3	Gauss's Market	start of semester groceries	$ 150.78	Groceries	Debit Card
7	Step 3.	Compute Seth's new Savings Balance by removing his Semesterly Expenses and adding his Semesterly Income.				Estimated Total Semesterly Income:				1/3	The Housing Place	first semester housing contract	$ 975.00	Housing	Check
8				Semesterly Expenses		January	Estimated Total			1/3	BYU-Idaho Bursar's Office	tuition	$ 1,960.00	Tuition	Debit Card
9					Housing	975.00		Hint		1/3	BYU-Idaho Bookstore	textbooks	$ 385.42	Textbooks	Credit Card
10	Step 4.	Set a New Monthly Goal for Seth that will balance his budget and keep him from spending more than he has.			Tuition	1,960.00				1/3	Financial Aid Office	scholarship money	$ 500.00	Financial Aid	Direct Deposit
11					Textbooks	385.42				1/3	Mom & Dad	gift to me for starting school	$ 50.00	Other Income	Cash
12					School Supplies	14.25				1/3	The Church of Jesus Christ of Latter-day Saints	scholarship and gift tithing	$ 55.00	Tithing (Special Income)	Direct Deposit
13	Step 5.	Type a detailed description in the text box below of how you decided to set Seth's new "Monthly Goal."			Tithing (Special Income)	55.00				1/5	Frozen Berry Shoppe	ice cream (date with Sarah)	$ 4.88	Eating Out	Cash
14						Estimated Total Semesterly Expenses:	0.00			1/6	The Church of Jesus Christ of Latter-day Saints	fast offering	$ 5.00	Charitable Donations	Cash
15			#5	Previous Savings Balance		New Savings Balance				1/7	Crossroads Cafeteria	forgot my lunch	$ 9.75	Eating Out	Debit Card
16					8,615.00			Hint		1/8	My Boss	first paycheck	$ 67.50	Paycheck	Direct Deposit
17										1/8	The Government	taxes paid directly from my paycheck	$ 15.52	Taxes	Payroll Deduction
18										1/8	Fuel Center	regular fill up for my car	$ 20.00	Travel	Credit Card
19							Monthly Goal (7 months)	#4		1/8	The Church of Jesus Christ of Latter-day Saints	blessings!	$ 6.75	Tithing	Check
20				Monthly Income		January	Monthly Goal (7 months)			1/9	BYU-Idaho Bookstore	notebooks and pens	$ 14.25	School Supplies	Cash
21					Paycheck	135.00		Hint		1/9	BYU-Idaho Bookstore	ear muffs	$ 4.89	Miscellaneous	Credit Card
22					Savings Withdrawal			Hint		1/9	Crossroads Cafeteria	lunch date with Sarah	$ 19.38	Eating Out	Debit Card
23					Total Monthly Income:	135.00	0.00			1/10	The Fun Center	lazer tag with my roomates	$ 32.00	Entertainment	Credit Card
24				Monthly Expenses		January	Monthly Goal	Hint		1/11	Gauss's Market	weekly groceries	$ 75.32	Groceries	Debit Card
25					Tithing	6.75				1/11	Local Bowling Place	bowling and pizza (with Sarah and friends)	$ 28.50	Entertainment	Credit Card
26					Charitable Donations	5.00				1/18	Gauss's Market	weekly groceries	$ 148.22	Groceries	Debit Card
27					Taxes	31.04				1/18	Bix Box Store	gift for Sarah	$ 58.39	Gifts	Credit Card
28					Groceries	374.32				1/18	The Cellphone Company	monthly cell phone bill	$ 99.98	Cell Phone	Debit Card
29					Eating Out	34.01				1/23	My Boss	second paycheck	$ 67.50	Paycheck	Direct Deposit
30					Cell Phone	99.98				1/23	The Government	taxes paid directly from my paycheck	$ 15.52	Taxes	Payroll Deduction
31					Travel	20.00			#2
32					Gifts	58.39
33					Entertainment	60.50
34					Miscellaneous	4.89
35					Total Monthly Expenses:	694.88	0.00
36				Cash Flow Summary		January	Monthly Goal
37					Total Cash Flow:	(559.88)	0.00
38						This should be zero.	This should be zero.
39				Seth's Final Savings Balance at the end of Two Semesters
40						New Savings Balance - Total of Savings Withdrawals:		Hint
41							Must be zero or greater. Green means good. Red means bad.
42							Must be zero or greater. Green means good. Red means bad.
43						Remember to document your changes.
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100