Attachment # 00001957 - Chapter_6_instructions_(1).docx
Chapter_6_instructions_(1).docx (15.89 KB)
Raw Preview of Attachment:
(refer to the detailed question and attachment below)
The Wine Depot is contemplating several alternative means of financing their annual acquisition of $75,000 in wine storage facilities. One option is to borrow $300,000 from a local bank for 5 years at 11 percent per annum. The bank has asked them to produce a 4-year cash budget broken down by year (2010 155156through 2013). Sales of $500,000 are expected in the first year, with sales increasing each year thereafter by 22 percent. Sales in 2009 were $450,000. Purchases are based on an expected cost of sales of 45 percent and a required ending inventory of 10 percent of next year's sales. Purchases in 2009 were $200,000, and beginning inventory was $32,000. Annual expenses include advertising expense of $10,000, marketing expense of $6,000, depreciation expense of $8,000, interest expense of $35,000, salaries expense of $150,000, wages expense of $65,000, supplies expense of $7,500, and utilities expense of $10,000. All expenses except depreciation are paid in the year in which they are incurred and are expected to increase 5 percent each year. Collections in the year of sale are expected to be 94 percent, with the remaining 6 percent collected in the next year. Payments in the year of purchase are expected to be 93 percent, with the remaining 7 percent paid in the next year. Proceeds from the $300,000 loan are expected in 2010, and $75,000 of facilities will be purchased each year. Proceeds from expected equipment sales each year are expected to amount to $10,000. Annual payments of $81,171 on the loan also begin in 2010. The beginning cash balance in 2010 was $20,000.   Using the ch6-05 file to start your work, create a cash budget (as you did in the chapter) based on the assumptions just provided. Use Excel's grouping feature to group operating cash receipts, operating cash payment, cash from (to) operating activities, cash from (to) investing activities, and cash from (to) financing activities. Save your file as ch6-05_student_name (replacing student_name with your name).a. Print the newly completed worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer.b. Print the worksheet from part a, above, in Formula view, with your name and date printed in the lower left footer and the file name in the lower right footer. Print only columns A, B, and C of the cash budget, no assumptions.c. Collapse rows to level 2; then print the worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer. Print cash budget only, no assumptions.d. Collapse rows to level 2, and then use what-if analysis to calculate end-of-year cash if the sales growth each year were 10 percent. Print the resulting worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer. Print cash budget only, no assumptions.e. Undo the what-if analysis performed in part d. Collapse rows to level 2, and then use goal seek to determine what annual sales growth would be needed to produce an ending cash balance of $50,000 in 2013. Print the resulting worksheet in Value view, with your name and date printed in the lower left footer and the file name in the lower right footer. Print cash budget only, no assumptions.
The Wine Depot is contemplating several alternative means of financing their annual acquisition of $75,000

The Wine Depot is contemplating several alternative means of financing their annual acquisition of $75,000

Question # 00026688 Posted By: jia_andy Updated on: 09/25/2014 07:57 AM Due on: 02/28/2015
Subject Accounting Topic Accounting Tutorials:
Question
Dot Image
Wine Depot
Cash Budget 2010 2011 2012 2013
Operating activities
Operating cash receipts
Product sales revenue
Collections in the year of sale
Collections in the year following sale
Operating cash receipts
Operating cash payments
Purchases
Cost of expected sales
Required ending inventory
Beginning inventory
Purchases
Payments in the year of purchase
Payments in the year following purchase
Cash payments for purchases
Expenses
Advertising expense
Marketing expense
Interest expense
Salaries expense
Wages expense
Supplies expense
Utilities expense
Expenses
Operating cash payments
Cash from (to) operating activities
Investing activities
Facility purchases
Equipment sales
Cash from (to) investing activities
Financing activities
Loan proceeds
Loan payments
Cash from (to) financing activities
Change in cash
Beginning cash
Ending cash
Assumptions
Dot Image
Attachments
Chapter_6_instructions_(1).docx (15.89 KB)
ch6-05.xlsx (12.81 KB)
Tutorials for this Question
  1. jia_andy
    Tutorial # 00026117 Posted By: jia_andy Posted on: 09/25/2014 07:58 AM
    Puchased By: 3
    Tutorial Preview
    The solution of The Wine Depot is contemplating several alternative means of financing their annual acquisition of $75,000...
      Get the Solution
    Attachments
    Ch6-05_Student_Name.xlsx (18.01 KB)
    Ch6-05_Student_Name-Formula_View.xlsx (18.02 KB)
Whatsapp Lisa