Question

************I AM LOOKING FOR AN EXCEL VERSION + PHOTOS OF THE FORMULAS SHOWN IN THE EXCEL PLEASE. **************

Check figures:

cash collections at end of quarter = $814,800

Deficiency in cash at the end of the quarter = $(99,206)

I am unsure if my excel spread is correct but it is what I have so far.. PLEASE SHOW YOUR EXCEL + THE FORMULAS SHOWN IN EXCEL (ex: =B13-A12 or =SUM(B17:B37) ETC.)

Kline Sisters Company operates a gift shop where peak sales and activity occur in the months of December and January. Data regarding the store's operations follow: Sales are budgeted at $360,000 for January, 320,000 for February, and $250,000 for March and $240,000 in April. Collections are expected to be 30% in the month of sale, 65% in the month following the sale, 3% in the second month following sale and 2% uncollectible. The cost of goods sold is 76% of sales. The company desires ending merchandise inventory to equal 13% of the following month's cost of goods sold. Payment for merchandise is made 50% in the month of purchase and 50% in the month following the purchase Monthly operating expenses to be paid in cash are $12,000 Equipment purchases of $40,000 in February and $30,000 in March were paid in cash Monthly depreciation is $13,000. Dividends of $48,000 were declared and paid in January. Any borrowings must be in $1,000 increments at 12% annual interest. Assume interest accrues at the beginning of the month and is paid at the end of the month. The company must maintain a minimum cash balance of $30,000. Ignore income taxes All accounts receivable from December 31 will be collected in January and all accounts payable at December 31 will be paid in January The balance sheet as of December 31st: Kline Sisters Company Balance Sheet 12/31/14 Assets: Cash $22,000 $83,000 $36,000 Net Accounts Receivable Merchandise Inventory $1,600,000 $588,000 Property Plant and Equipment Less: accumulated depreciation $1,012,000 $1,153,000 Total Assets Liabilities & Stockholder's Equity Accounts Payable $190,000 $350,000 Common Stock Retained Earnings $613,000 Total liabilities and stockholder's equity $1,153,000 Prepare the following budgets for each month January, February, March and Total for the quarter in good form in excel with proper use of formulas and formatting: Prepare a Schedule of Expected Cash Collections a. What is the budgeted accounts receivable at March 31st 2 b. Prepare a Merchandise Purchases Budget and a Schedule of Expected Cash Disbursements What is the budgeted accounts payable at March 31st ? c. Prepare a Cash Budget How much does the company need to borrow for the quarter? How much can the company repay for the quarter? d. Prepare a Budgeted Income Statement Please highlight your answers. Formatting is as important as the correct answer for this project. Your spreadsheet should be in good form, presented and printed clearly, and labeled appropriately Your print out should be in report format and clearly presented. Each budget should not span more than one page, i.e. pay attention to what prints out on each page Schedule of Expected Cash Collection Part A Feb March Total for Quarter Jan Budgeted Sales 360000 250000 320000 930000 Collection of December AR Jan. Sales Feb. Sales March Sales Total Cash Collection Balance as of March 31 Merchandise Purchase Budgeted Part B Feb. March Total for Quarter April Jan Budgeted Sales COGS Desired Ending Inventory Total Required Beginning Inventory Budgeted Purchase Schedule of Expected Cash Disbursements Total Feb. March Jan Budgeted Purchase December Accounts Payable January Sales February Sales March Sales Total Expected Cash Disbursements Balance of Accounts Payable on March 31st Cash Budget Part C Total for Quarter Feb Mar Jan Beginning Cash Balance Total Cash Collection Total Cash Available Disbursements: Payments to AP Operating Expenses Equipments Purchases Dividends Total Disbursements Cash Surplus/(Deficiency) Minimum Cash Balance Borrowings/ (Repayments) Interest Cash Balance Er Budgeted Income Statement For Quarter Ending on March 31 Part D Sales Revenue Cost of Goods Sold Gross Profit Expenses Operating Expenses Depreciation Interest Bad Debt Expenses Total Expenses Net Income

Answer 1

ANS Microsoft Excel ? File Home Insert Page Layout Formulas Data Review View Cut AutoSum A A Wrap Text Callibr 11 General Fill- ECopy B I Merge & Center Conditional Format Formatting Paste A- Cell Insert Delete Format Sort & Find & % Format Painter Clear er Select ble Styles Clipboard Font Alignment Cells Number Styles Editing 197 M. A B C D G H N 80 Cash Collections 81 March 82 Feb. Total Jan Accounts receivable 31/12/14 January Sales $83.000.00 83 83.000.00 360,000.00 $ 108.000.00 $ 234,000.00 $ $352,800.00 84 S 10,800.00 $304,000.00 85 February Sale 96,000.00 320.000.00 208.000.00 $ 75,000.00 86 March Sale 250,000.00 75.000.00 $ 191.000.00 $ $814,800.00 87 Total 330,000.00 $ 293,800.00 98 83000 +D90*30/100 SUM(E89:G89) -SUM(E90:G90) +D91 65/100 -SUM(E91:G91) +D92 30/100 SUM(E92:G92) SUM(E89:E92) -SUM(F89:F92) SUM(G89: G92) -SUM(E93:G93) 89 +D90*65 /100 +D91 30/100 +D90 3/100 90 360000 91 320000 250000 92 93 gs 96 Accounts Recelvables $ 930,000.00 SUM(E84:E86) $ 731,800.00 SUM(F84:H86) $ 198,200.00 +F97- F98 97 Total Sales 98 Cash Collected 99 Accounts Receivables Balance 100 101 102 4 H Sheet43 Sheet44 Sheet51Sheet5 2 Sheet45 Sheet46Sheet47 Sheet48 Sheet49 Sheet50 100% Ready 1:14 PM lalla 07-Jun-19

ANS Microsoft Excel ? File Home Insert Page Layout Formulas Data Review View AutoSum Cut A A Wrap Text 11 Calibri General Fill- ECopy B I U Conditional Format Formatting Paste aMerge & Center Cell Insert Delete Format Sort & Find & Format Painter Clear ble Styles er Select Font Clipboard Alignment Cells Number Styles Editing G123 M. A B F G H N Merchandise Purchase budget Jan Feb March Total 107 Exnected Sales ... 930000 360000 320000 250000 Cost of Goods Sold 76 % 273600 243200 190000 706800 104 Ending Inventory Less Beginning Inventory 31616 24700 23712 105 106 31616 24700 36000 107 Purchases Made 269216 236284 189012 694512 108 March 109 Cash Payments Feb Total Jan $190,000.00 Accounts Payable 31/12/14 190,000.00 $ 190,000.00 269,216.00 $ 134,608.00 $ 110 S $269,216.00 $236,284.00 94,506.00 $ 94,506.00 252,750.00 $ 212,648.00 $790,006.00 111 January Purchases 134.608.00 118,142.00 $ 118,142.00 112 February Purchases 236,284.00 March Purchases Total $ 189,012.00 113 324,608.00 $ S 114 115 Total Cash Payments Accounts Payable 31/12/14 Feb March 116 Jan 190000 -SUM(E117:G117) 117 190000 +D118 50 /100 D118 50/100 uary Purchases Febr D119 50/100 +D119 50/100 ss D120*50/100 SUM(E120:G120) sUM(E117:E120-SUM(F117: F120) -SUM (G117:G12 =SUM(H117: H120) 236284 ses March Purchases 189012 120 Total 121 122 Accounts Payable 123 Purchases Made 694,512.00 sUM(E111:E113) 124 125 Payments Made 600,006.00 SUM(F111:H113) 126 Accounts Payable balance 94,506.00+E124-E125 Sheet46 Sheet47 Sheet48Sheet49 Sheet50 Sheet51 Sheet43 Sheet44 Sheet45 Sheet52 100% Ready 1:22 PM lalla ll 07-Jun-19

ANS Microsoft Excel X ? File Home Insert Page Layout Formulas Data Review View AutoSum Cut Wrap Text A 11 Calibri General Fill- ECopy BIU - A- Merge & Center Conditional Format Formatting Paste Cell Insert Delete Format Sort & Find & % Format Painter ble Styles Clear er Select Font Clipboard Alignment Number Cells Styles Editing H142 G H M. A B C D F N Cash Budget 130 Feb March Jan 30,352.00 $ 131 Opening Balance 22,000.00 30,792.00 132 Receipts 191,000.00 S 133 Collected from Customers 330,000.00 293,800.00 Total Cash Available 213,000.00 $ 360,352.00 $ 134 324.592.00 135 Payments 136 Paid for purchases $ 324,608.00 252,750.00 212,648.00 12,000.00 $ 137 12,000.00 Operating Expenses 12,000.00 Equipment Purchase 40.000,00 $ 138 30.000.00 139 Dividend Paid $ 48,000.00 $384,608.00 $ 304,750.00 140 Total Payments 254,648.00 55.602.00 $ Preliminary Balance of Cash (171,608.00) 204,000.00 $ 141 69,944.00 Borrowings (Repayment) (23.000.00) S (39,000.00) 142 (1,810.00) $ 30,792.00 $ Interest paid Ending Cash Balance (2,040.00) 30,352.00 $ (1,420.00) 143 144 30,944.00 ... 147 148 149 150 151 152 153 4 H Sheet43 Sheet44 Sheet46 Sheet47 Sheet48 Sheet49 Sheet50 Sheet51 Sheet52 Sheet45 100% Ready W 1:40 PM alla 07-Jun-19

Cash Budget	Jan	Feb	March
Opening Balance	$       22,000.00	$       30,352.00	$          30,792.00
Receipts
Collected from Customers	$    191,000.00	$    330,000.00	$        293,800.00
Total Cash Available	$    213,000.00	$    360,352.00	$        324,592.00
Payments
Paid for purchases	$    324,608.00	$    252,750.00	$        212,648.00
Operating Expenses	$       12,000.00	$       12,000.00	$          12,000.00
Equipment Purchase		$       40,000.00	$          30,000.00
Dividend Paid	$       48,000.00
Total Payments	$    384,608.00	$    304,750.00	$        254,648.00
Preliminary Balance of Cash	$ (171,608.00)	$       55,602.00	$          69,944.00
Borrowings (Repayment)	$    204,000.00	$    (23,000.00)	$        (39,000.00)
Interest paid	$       (2,040.00)	$       (1,810.00)	$          (1,420.00)
Ending Cash Balance	$       30,352.00	$       30,792.00	$          30,944.00
Income Statement
Sales Revenue		$    930,000.00
Expenses
Cost of Goods Sold	$    706,800.00
Operating Expenses	$       36,000.00
Depreciation	$       39,000.00
Bad Debts Expense	$       18,600.00
Interest	$         5,270.00
Total Expenses		$    805,670.00
Income		$    124,330.00