Prepare the corresponding cash t-account in the columns

Reference no: EM131349257

Excel Workbook Assignment

A. Three company scenarios are listed below. Based on the information provided in each scenario, prepare the corresponding Statement of Cash Flows in the columns to the right of each scenario. Your Statement of Cash Flows should be in good form and utilize cell referencing and formulas where applicable. You must link your spreadsheet cells to get full credit.

Scenario 1: Barnaby's Circus, Ltd., had a Cash Balance of $350,000 at year end, December 31, 2014. Barnaby's paid cash for the following items during the year ending December 31, 2015:

1	Dividends paid to stockholders	$ 20,000
2	Purchased inventory from suppliers	43,000
3	Purchased a new building	125,000
4	Paid employee salaries	657,000
5	Paid taxes to the IRS	15,000
6	Purchased insurance policies to cover the circus	120,000

Barnaby's Circus, Ltd., received cash for the following items during the year ending December 31, 2015:

1	Received cash from customers	$ 880,000
2	Issued shares of common stock to their new shareholders	67,000
3	Received interest from an investment	3,000

Scenario 2: Cletus' BBQ, Inc., started the year with a cash balance of 452,000 on January 1, 2015. Cletus' paid cash for the following items during the year ending December 31, 2015:

1	Paid supplier for brisket, pulled pork, and chicken	$82,000
2	Paid a principle payment on a long-term note they owe	9,500
3	Paid employees wages	76,000
4	Purchased a new delivery truck	47,500
5	Paid selling and administrative expenses	23,600

Cletus' BBQ, Inc., received cash for the following items during the year ending December 31, 2015:

1	Sold an old BBQ smoker they replaced with a bigger one last year	$2,000
2	Received cash from customers	197,000
3	Received rent revenue from a tenant in their building	16,000
4	Issued shares of stock to new shareholders	40,000

Scenario 3: Quackpot Gardens Corp. ended the year with a cash balance of 452,000 on December 31, 2015. Quackpot paid cash for the following items during the year ending December 31, 2015:

1	Paid supplier for gardening inventory	$139,000
2	Paid a principle payment on a long-term note they owe	22,300
3	Paid employees wages	78,000
4	Purchased a new huge greenhouse	230,000
5	Paid interest on the long-term note	12,000
6	Paid all utility bills owed	33,900
7	Paid dividends to shareholders	4,000

Quackpot Gardens Corp. received cash for the following items during the year ending December 31, 2015:

1	Sold an old delivery van	$5,600
2	Received cash from customers	354,000
3	Received cash from a lawsuit settlement	20,000

B. Two company scenarios are listed below. Based on the information provided in each scenario, prepare the corresponding Cash T-Account in the columns to the right of each scenario. Your T-Account should be in good form and utilize cell referencing and formulas where applicable.

Scenario 1: Hannah's Hot Dogs, Ltd., engaged in the following cash transactions when she started up the business this year. Record each transaction in the Cash T-Account Provided in Columns H and I of this worksheet. Be sure to label each item in the T-Account with its corresponding number. The T-Account is drawn for you in this first scenario.

1	Received cash from investors	$30,000
2	Paid cash for hot dog cart	4,900
3	Paid cash for hot dog, buns, and condiment inventory	2,000
4	Paid cash for cart license	1,200
5	Received cash from customers	6,700
6	Paid cash for insurance	3,000
7	Paid cash to accountant	1,000
8	Paid cash for taxes	450

Scenario 2: Harry's Hamburgers, Inc., engaged in the following cash transactions during the year. Harry had a cash balance of $45,000 at the beginning of the year. Record each transaction in a Cash T-Account in Columns H and I of this worksheet. Be sure to label each item in the T-Account with its corresponding number or label. Don't forget your beginning balance!

1	Made principal payment on long-term note	$ 8,700
2	Received rent from other hamburger wagon franchisees	13,400
3	Received cash from customers	72,000
4	Got a new loan from the bank	10,000
5	Paid employee wages	28,000
6	Paid for liability insurance policy	4,000
7	Paid all other expenses	22,300

C. Two company scenarios are listed below. Based on the information provided in each scenario, calculate the corresponding (a) Cash Coverage of Growth Ratio and the (b) Free Cash Flows in the columns to the right of each scenario. Your Ratios and Calculations should include the written definition, be labeled clearly, be presented in good form, and utilize cell referencing and formulas where applicable.

Scenario 1: A Statement of Cash Flows is presented below for Heavy Metal Corporation. Calculate the Cash Coverage of Growth Ratio and the Free Cash Flows for this company.

Heavy Metal Company Statement of Cash Flows For the year ending December 31, 2015
Cash Flows from Operating Activities:
Cash received from customers	$1,288,436
Cash paid to suppliers	(556,299)
Cash paid for selling and administrative expenses	(276,600)
Cash paid for interest	(3,500)
Cash paid for taxes	(25,460)
Net Cash Flows Provided by Operating Activities	426,577
Cash Flows from Investing Activities:
Proceeds from sale of long-term investment	75,000
Purchase of heavy metal delivery truck	(84,900)
Purchase of new heavy metal storage warehouse	(282,000)
Net Cash Flows Used for Investing Activities	(291,900)
Cash Flows from Financing Activities:
Proceeds from new note payable	300,000
Payment of principal on bond payable	(50,000)
Proceeds from issue of common stock shares	41,000
Net Cash Flows Provided by Financing Activities	291,000
Net Cash Provided during year	425,677
Beginning Cash at January 1, 2015	1,853,908
Ending Cash at December 31, 2015	$ 2,279,585

Scenario 2: A Statement of Cash Flows is presented below for Good Grub Grocery. Calculate the Cash Coverage of Growth Ratio and the Free Cash Flows for this company.

Cash Flows from Operating Activities:
Cash received from customers	$ 967,000
Cash paid to suppliers	(644,000)
Cash paid for selling and administrative expenses	(227,000)
Cash paid for interest	(4,980)
Cash paid for taxes	(16,675)
Net Cash Flows Provided by Operating Activities	74,345
Cash Flows from Investing Activities:
Proceeds from sale of delivery truck	4,500
Purchase of refrigerated display cases	(13,445)
Purchase of new forklifts	(12,980)
Net Cash Flows Used for Investing Activities	(21,925)
Cash Flows from Financing Activities:
Payment of principal on note payable	(22,000)
Proceeds from issue of common stock shares	8,900
Net Cash Flows Used For Financing Activities	(13,100)
Net Cash Provided during year	39,320
Beginning Cash at January 1, 2015	387,224
Ending Cash at December 31, 2015	$ 426,544

Attachment:- Assignment.rar

Reference no: EM131349257

Questions Cloud

What is the managers role in implementing change in the dept : What is the manager's role and responsibility in implementing change in the department?How should a manager successfully handle staff resistance to change?Define how knowledge management, organizational learning, and transformational leadership are..

Calculate the effective mass near a band gap : Extend the previous exercise to estimate the position and width of the lowest band in a superlattice of alternating 5 nm wells and 5 nm barriers of height 0.3 eV in GaAs. A numerical calculation puts the edges of the bands at -0.215 eV and -0.205 ..

Training program identifying the main topics : Create a training program identifying the main topics that you think should be covered in a new employee orientation for an existing or imaginary company. Be specific. Your training should include an outline of topics, schedule, five course learni..

What are the benefits of having a fully motivated staff : What are the benefits of having a fully motivated staff, and what are the potential consequences of not having motivated staff - What do contemporary theories tell us about the significance of motivation as a function of employee needs, extrinsic a..

Prepare the corresponding cash t-account in the columns : Two company scenarios are listed below. Based on the information provided in each scenario, prepare the corresponding Cash T-Account in the columns to the right of each scenario. Your T-Account should be in good form and utilize cell referencing a..

Explain the meaning of each of gardner five minds : Explain the meaning of each of Gardner's five minds. Analyze your level of competency in each of the five minds. Describe your experience in each of the five minds.

How does this compare with the gap at the middle of a face : Estimate the width of the gap near the corner of the first Brillouin zone for a square two-dimensional crystal. How does this compare with the gap at the middle of a face?

What is npv that makes you indifferent between two options : Suppose that you are approached with an offer to purchase an investment that will provide cash flows of $1,600 per year for 18 years. The cost of purchasing this investment is $9,200. You have an alternative investment opportunity, of equal risk, tha..

How can you not include all of your social security benefits : If you include all your Social Security benefit in the calculations, you'll be paying too much. How can you not include all of your Social Security benefits?

User Account

All Pages