Question

Question: 3. Prepare balance sheets for the end of each semester.

Edwina Haskell was an accomplished high school student who looked forward to attending Southern New England University (SNEU). SNEU was unique in that it operated on a trimester basis, its policy was to actively foster independent development among the students. Edwina’s mother and father each own their own small businesses. Soon after freshman orientation at SNEU, Edwina recognized a need among the students that could be the basis for developing a small business. Freshman students could not bring their cars on the campus. In effect, they were confined to the dorm; if they wished to travel, they had to take school-provided buses that operated on a fixed schedule. Further, the university’s cafeteria closed at eight in the evening. Students who wanted to have some food or snacks after 8:00 p.m. had to call local restaurants that delivered. The few restaurants in the neighborhood around SNEU that had delivery services often were late in their deliveries, and hot food, such as pizza, was frequently delivered cold. Edwina felt that there was a niche market on the campus. She believed that students would be interested in ordering sandwiches, snacks, and sodas from a fellow student provided that the food could be delivered in a timely fashion. After talking with several students in her dorm complex, she believed that offering a package of a sandwich, a soda, and a small snack, such as potato chips, for $5 and a guaranteed delivery of 15 minutes or less would be a winner. Because her dorm complex consisted of four large adjoining buildings that house nearly 1,600 students, she felt that there would be sufficient demand to make the concept profitable. She talked about this concept with her roommates and with her parents. Her roommates were willing to help prepare the sandwiches and deliver them. She planned on paying each of them $250 per trimester for taking orders, making sandwiches, and delivering them. All three roommates, whom she knew from high school, were willing to be paid at the end of the trimester. Edwina recognized that for this business plan to work, she would have to have a sufficient inventory of cold cuts, lettuce, tomatoes, soda, chips, and condiments to be able to meet student demands. The small refrigerators in the dorm rooms would not be sufficient. After talking to her parents, they were willing to help her set up her business. They would lend her $1,000 to buy a larger refrigerator to place in her dorm room. She did not have to repay this loan until she graduated in four years, but her parents wanted her to appreciate the challenges of operating a small business. They set up several conditions. First, although she did not have to pay back the $1,000 for the refrigerator for four years, she had to pay interest on this “loan.” She had to repay 3 percent of this loan each trimester. Further, they reminded her that although she could pay her friends at the end of the semester, she would need funds to buy the cold cuts, bread, rolls, soda, snacks, condiments, and supplies such as foil to wrap the sandwiches, plus plates and paper bags. Although Edwina was putting $500 of her own money into her business, her parents felt that she might need an infusion of cash during the first year (i.e., the first three trimesters). They were willing to operate as her bank—lending her money, if needed, during the trimesters. However, she had to pay the loan(s) back by the end of the year. They also agreed that the loan(s) would be at a rate of 2 percent per trimester. Within the first three weeks of her first trimester at SNEU, Edwina purchased the $1,000 refrigerator with the money provided by her parents and installed it in her dorm. She also went out and purchased $180 worth of supplies consisting of paper bags; paper plates; and plastic knives, spoons, and forks. She paid for these supplies out of her original $500 personal investment. She and her roommates would go out once or twice a week, using the SNEU bus system to buy what they thought would be the required amount of cold cuts, bread, rolls, and condiments. The first few weeks’ worth of supplies were purchased out of the remainder of the $500. Students paid in cash for the sandwiches. After the first two weeks, Edwina would pay for the food supplies out of the cash from sales. In the first trimester, Edwina and her roommates sold 640 sandwich packages, generating revenue of $3,200. During this first trimester, she purchased $1,710 worth of food supplies. She used $1,660 to make the 640 sandwich packages. Fortunately, the $50 of supplies were condiments and therefore would last during the two-week break between the trimesters. Only $80 worth of the paper products were used for the 640 sandwich packages. Edwina spent $75 putting up posters and flyers around the campus promoting her new business. She anticipated that the tax rate would be approximately 35 percent of her earnings before taxes. She estimated this number at the end of the first trimester and put that money away so as to be able to pay her tax bill. During the two weeks off between the first and second trimester, Edwina and her roommates talked about how they could improve business operations. Several students had asked about the possibility of having warm sandwiches. Edwina decided that she would purchase two Panini makers. So at the beginning of the second trimester, she tapped into her parents’ line of credit for two Panini grills, which in total cost $150. To make sure that the sandwiches would be delivered warm, she and her roommates spent $100 on insulated wrappings. The $100 came from cash. The second trimester proved to be even more successful. The business sold 808 sandwiches, generating revenue of $4,040. During this second trimester, the business purchased $2,100 worth of food supplies, using $2,020 of that to actually create the 808 sandwich packages. They estimated that during the second trimester, they used $101 worth of supplies in creating the sandwich packages. There was only a one-week break between the second and third trimesters, and the young women were quite busy in developing ideas on how to further expand the business. One of the first decisions was to raise the semester salary of each roommate to $300 apiece. More and more students had been asking for a greater selection of warm sandwiches. Edwina and her roommates decided to do some cooking in the dorms so as to be able to provide meatball and sausage sandwiches. Edwina once again tapped into her parents’ line of credit to purchase $275 worth of cooking supplies. One of the problems they noticed was that sometimes students would place calls to order a sandwich package, but the phones were busy. Edwina hired a fellow student to develop a website where students could place an order and select the time that they would like a sandwich package to be delivered. The cost of creating and operating this website for this third trimester was $300. This last semester of Edwina’s freshman year proved to be the most successful in terms of sales. They were able to fulfill orders for 1,105 sandwich packages, generating revenue of $5,525. Edwina determined that the direct cost of food for these sandwich packages came out to be $2,928.25. The direct cost of paper supplies was $165.75. At the end of her freshman year, Edwina repaid her parents the $425 that came from her credit line that was used to purchase the Panini makers and the cooking utensils.

Please post the excel spread sheet. Por favor y Gracias!

Answer 1

Income Statement 1st Semester

	1st Trimester	2nd Trimester	Total for semester
Revenue	3,200.00	4,040.00	7,240.00
COGS	1,660.00	2,020.00	3,680.00
Paper Bags	80.00	201.00	281.00
Advertisement	75.00		75.00
Salary		250.00	250.00
Interest	30.00	33.00	63.00
EBIT	1,355.00	1,536.00	2,891.00
Taxes(35%)	474.25	537.60	1,011.85
Net Income	880.75	998.40	1,879.15

Balance sheet for 1st Semester
Assets		Liabilities
Refridgerator	1,000.00	Loan from Parents	1,000.00
Cash	3,261.00	Capital	500.00
Food Supplies	130.00	Retained Earnings	1,879.15
Other Supplies	-	Taxes Payable	1,011.85
Panni Grills	150.00	Loan from Bank	150.00
		Accounts Payable	-
	4,541.00		4,541.00

Income Statement Last Semester

Revenue	5,525.00
COGS	2,928.25
Cooking supplies	275.00
Paper Bags	165.75
Advertisement	300.00
Salary	300.00
Interest	68.50
EBIT	1,487.50
Taxes(35%)	520.63
Net Income	966.88

Balance sheet last semester
Assets		Liabilities
Refridgerator	1,000.00	Loan from Parents	1,000.00
Cash	4,598.50	Capital	500.00
Food Supplies	130.00	Retained Earnings	2,846.03
Other Supplies	-	Taxes Payable	1,532.48
Panni Grills	150.00
	5,878.50		5,878.50