Ask Question, Ask an Expert

+61-413 786 465

info@mywordsolution.com

Ask Accounting Basics Expert

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

Check Figure for Scenario 3: Cash Used for Investing Activities is ($224,400). Note that this is not a start-up company so you need to solve for Beginning Cash. It is not zero.

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.

Good Grub Grocery Statement of Cash Flows For the year ending December 31, 2015

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:- Excel Workbook Assignment.rar

Accounting Basics, Accounting

  • Category:- Accounting Basics
  • Reference No.:- M92102619
  • Price:- $40

Guranteed 36 Hours Delivery, In Price:- $40

Have any Question?


Related Questions in Accounting Basics

Question -what are the factors that affect the decision to

Question - What are the factors that affect the decision to prosecute an entity? How can computers and technology help in investigating a fraud? What kinds of challenges can the involvement of technology present to a cas ...

Question - if colleen mooney invests 476550 now and she

Question - If Colleen Mooney invests $4,765.50 now and she will receive $12,000 at the end of 12 years, what annual rate of interest will Colleen earn on her investment? Compute the number of periods of a single amount. ...

Question - 1 what is gross profit margin and what is net

Question - 1. What is gross profit margin and what is net profit margin? What are in the between the two? Give a few examples to explain the items between these margins, and show how to analyze these items for improvemen ...

Question - on december 31 year 1 day co leased a new

Question - On December 31, year 1, Day Co. leased a new machine from Parr with the following pertinent information: Lease term 8 years Annual rental payable at beginning of each year $60,000 Useful life of machine 10 yea ...

Question - cannonier inc has identified an investment

Question - Cannonier, Inc., has identified an investment project with the following cash flows. Year Cash Flow 1 $1,060 2 1,290 3 1,510 4 2,250 If the discount rate is 6 percent, what is the future value of these cash fl ...

Question - anwer owns a rental home and is involved in

Question - Anwer owns a rental home and is involved in maintaining it and approving renters. During the year he has a net loss of $8,000 from renting the home. His other sources of income during the year are a salary of ...

Question - henry company manufactures two types of office

Question - Henry Company manufactures two types of office chairs, Model A and Model B. It estimates the following results for next year.   Model A Model B Sales Revenue $450,000 $50,000 Variable Expenses (total): $140,00 ...

Question - at december 31 2014 the available for sale

Question - At December 31, 2014, the available for sale equity portfolio for xyz corp. is as follows. Security Cost Fair Value Stock A 33,600 31,000 Stock B 175,000 174,000 Stock C 59,400 68,500 Total 268,000 273,500 Dec ...

Question - lopez company purchased goods with the following

Question - Lopez Company purchased goods with the following terms and details: Sales price, $5,000 Terms, 1/10, n/30 Date of sale, March 8 Date of payment, March 19 Returns and allowances (before payment), $200 Shipping, ...

Question - on january 1 year 1 homeland entity he signed a

Question - On January 1, year 1, Homeland Entity (HE) signed a 20-year lease contract for an office building. The lease contract calls for HE to make payments of $10,000 at the beginning of each year, with the first paym ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As