Question

I am having a really hard time completing this so if someone can help me that would be great. also if this can be completed in excel so i know how the problem was done that would be great to thanks. i will give a rating at the end

ett forner and ter 6 Case Problem 1: KELLY'S BOUTIQUE Kelly's Boin special equipment. One alternative is to borrow $2000 of $200,000 in equi ue is contemplating several means of financing thet r 10 years at 12 percent per annum. The bank has asked 0.000 froma cash budget broken down by quarters. Sales of $40.000 uce a 1-year first quarter, with each quarter thereafter increasing 2 percen. er thereafter i are based on an expected cost of sales of 55 percent and a inventory of 70 percent of next quarter's cost of sales. Beginning inver- s11.000. Sales for the first quarter next year are expected to be tory was $11 $50,000. Sales ious year's third quarter were $30,000. Expenses include ady roll expense of $8,000 the quarter during which they are incurred. Collections in the qua in the previous year's fourth quarter were $28,000. Sales in the advertising pay- supplies expense of $500, and utilities expense of $600 expense of $900, depreciation expense of s800, interest expense of $1.00, uarter throughout the year. All expenses except depreciation are paid in rter of sale are expected to be 80 percent, collections in the first quarter following a sale 15 percent, and in the second quarter 5 percent. Payments in the quarter of pur chase are expected to be 85 percent, payments in the first quarter followinga purchase 10 percent, and payments in the second quarter to be 5 percent. Pur- chases in the previous year's fourth quarter were $20,000. Purchases in the pre- expected in the second quarter and $200,000 of equipment will be parchased n rd quarter. The beginning cash balance in the first quarter was $15,00. were $17,000. Proceeds from the $200,000 loan are ous year's third quarter the third quarter. Quarterly payments of $4,200 on the loan also begin in the el for Aervunring chó-04 file to start your work, create a cash budget (as you did in revious paragraph, Using is based on the assumptions listed in the ) that grouping feature to group operating cash rece ipts, oe Parag the cel's group from. h from (to) operating activities, cash (to) inv ment. pay cing activities and also to group theourctiviti lacing student name and cash from oether. Save your file as ch6-04 student name (replaci names as appropriate. completed worksheet in Value view, with your name). Define Print the newly a. and date printed in the lower left footer and the file name inur nae right footer. b. Collapse rows to level 2 and columns to level 1, and then print the printed in the worksheet in Value view with your name and date and the file name in the lower right footer. Print lower left footer budget only, no assumptions. to level 3 and columns to level 2, and then use what what-if calculate end-of-year cash if the sales growth each quarter Expand rows e. were 4 percent and payroll expense were $18,000 per quarter. Print in Value view, with your name and date nted in the lower left footer and the file name in the lower right the resulting worksheet footer. Print cash budget only, no assumptions d. Undo the what-if analysis performed in part c. Use goal seek to deter- mine what sales growth would be needed to produce an ending cash balance of $50,000. 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 and assumptions.

Answer 1

hi i can not use excel file to solve this question but for an answer i am preparing cash budget as followed by instructions and then prepare cash flow statement. after getting answer u can easily convert it into excel file.

cash budget

particular quarter 1 quarter 2 quarter 3 quarter 4

opening cash balance 15000 15148 219842 15581

sales 40,000 40800 41616 42448

collection in current period (80%) 32000 32640 33293 33958

collection in next period (15%) 4200 6000 6120 6242

collection in next period (5%) 1500 1400 2000 2040

total collection (a) 37700 40040 41413 42240

payment for purchase (W.N. 1) (b) 26552 24346 24474 22995

expenses

advertisment 900 900 900   900

interest 1000 1000 1000 1000

payroll expense 8000 8000 8000 8000

supplies expense 500 500 500 500

utility expense 600 600 600 600

total expense (c) 11000 11000 11000 11000

payment of loan 4200 4200

loan received 200,000   

equipment purchased 200,000

payment of interest 6000 6000

closing cash balance 15148 219842 15581 13626

working note 1

purchase (55% of sales) 22000 22440 22888 23346

(+)ending inventory    15708 16022 16342 19250

(-) beginning inventory   11000 15708 16022 16342

total inventory to be purchased 26708 22754 23208 26254

payment in current period (85%) 22702 19340 19726 18377

payment in next period (15%) 3000 4006 3413 3481

payment in next period (5%) 850 1000 1335 1137

total payment 26552 24346 24474 22995

cash flow statement

cash balance at the beginning 15000

cash flows from operating activities

collection from customers 161393

payment to suppliers 98367

payment for expenses 44000

cash inflows from operating activities 19026

cash flows from investing activities

purchase of equipment ( 200,000)

cash flows from financing activities

proceeds from loan 200,000

payment of loan 8400

payment of interest 12000

cash inflows from financing activities 179600

closing cash balance 13626