Part (a) Given the information that follows, prepare a cash budget for the XYZ S
ID: 2721315 • Letter: P
Question
Part (a)
Given the information that follows, prepare a cash budget for the XYZ Store for the first six months of 2016. • All prices and costs remain constant. • Sales are 88% for credit and 12% for cash. • With respect to credit sales, 45% are collected in the month after the sale, 35% in the second month, and 20% in the third. Bad-debt losses are insignificant.
• Sales, actual and estimated, are (* for actual sales):
October 2015
$275,000*
March 2016
$340,000
November 2015
350,000*
April 2016
310,000
December 2015
320,000*
May 2016
375,000
January 2016
290,000
June 2016
280,000
February 2016
300,000
July 2016
370,000
Merchandises are purchased one month before the anticipated sales at 78% (COGS). Assume all purchases arrived in the same month of ordering, and the company will pay the purchase exactly 1 month after placing the order.
Wages and salaries are:
January 2016
$40,000
April 2016
$60,000
February 2016
45,000
May 2016
55,000
March 2016
50,000
June 2016
52,000
Rent is $5,000 a month.
Interest of $7,500 is due on the last day of each calendar quarter, and no quarterly cash dividends are planned.
A tax prepayment of $50,000 for 2016 income is due in April.
A capital investment of $50,000 is planned in June, to be paid for then.
The company has a cash balance of $100,000 at December 31, 2015, which is the minimum desired level for cash. Funds can be borrowed in multiples of $10,000. (Ignore interest on such borrowings.)
part ( b)
Use the cash budget worked out in Part (a) and the following additional information to prepare a forecast income statement for the first half of 2016 for the XYZ Store. (Note that the store maintains a safety stock of inventory.) Inventory at 12/31/15 was $180,000.
Depreciation is taken on a straight-line basis on $240,000 of assets with an average remaining life of 10 years and no salvage value.
The tax rate is 35 percent.
Part (c)
Given the following information and that contained in Parts (a) and (b), construct a forecast balance sheet as of June 30, 2016, for the XYZ Store.
XYZ Store balance sheet at December 31, 2015
ASSETS LIABILITIES AND EQUITY
Cash
$100,000
Accounts payable $100,000
Accounts receivable
427,500
Bonds 500,000
Inventory
180,000
Common stock and retained earnings
Fixed assets, net
240,000
347,500
$947,500
$947,500
Can you solve part C please? I uploaded this question before but no one solved part C and in reallity all I need is part c
October 2015
$275,000*
March 2016
$340,000
November 2015
350,000*
April 2016
310,000
December 2015
320,000*
May 2016
375,000
January 2016
290,000
June 2016
280,000
February 2016
300,000
July 2016
370,000
Explanation / Answer
Note:
There are certain unmatched information in the problem:
1) Accounts payable is only 100000 in the beginning; this should be equal to the merchandise purchase of march 2015 and it should be 226200.
Similary, receivables of 427500 should be what is left to be collected on the sales of october, november and december 2015, which would amount to 499400. Those figures do not agree.
However, these have been taken care of while preparing the balance sheet.
Sales Budget: jan feb mar apr may jun total july Sales-$ 290000 300000 340000 310000 375000 280000 1895000 370000 Cash sales (12%) 34800 36000 40800 37200 45000 33600 227400 Credit sales (88%) 255200 264000 299200 272800 330000 246400 1667600 after june Sales collection budget: from october 2015 sales 48400 48400 from november 2015 sales 107800 61600 169400 from december 2015 sales 126720 98560 56320 281600 from january 2016 sales 0 114840 89320 51040 255200 from february 2016 sales . 118800 92400 52800 264000 from march 2016 sales 134640 104720 59840 299200 from april 2016 sales 122760 95480 218240 54560 from may 2016 sales 148500 148500 181500 from june 2016 sales 246400 282920 275000 264440 278080 280280 303820 1684540 482460 Cost of goods sold (78% of sales) 226200 234000 265200 241800 292500 218400 1478100 Purchase Budget: Purchases made during the month 234000 265200 241800 292500 218400 288600 1540500 beginning balance 180000 187800 219000 195600 246300 172200 180000 total avaialable for sale 414000 453000 460800 488100 464700 460800 1720500 cogs for the month 226200 234000 265200 241800 292500 218400 1478100 ending balance 187800 219000 195600 246300 172200 242400 242400 Cash disbursements for purchases cash paid against accounts payable 226200 234000 265200 241800 292500 218400 1478100 Cash budget: Beginning balance of cash 100000 106520 100520 108060 100540 103320 100000 Receipts: cash sales 34800 36000 40800 37200 45000 33600 227400 receipts from customers 282920 275000 264440 278080 280280 303820 1684540 317720 311000 305240 315280 325280 337420 1911940 Total cash available 417720 417520 405760 423340 425820 440740 2011940 Disbursements: against purchases 226200 234000 265200 241800 292500 218400 1478100 wages and salaries 40000 45000 50000 60000 55000 52000 302000 rent 5000 5000 5000 5000 5000 5000 30000 interest 7500 7500 15000 tax prepayment 50000 50000 capital invesmtment 50000 50000 Total disbursements 321200 284000 377700 306800 352500 282900 1925100 surplus/deficit 96520 133520 28060 116540 73320 157840 86840 Financing: borrowing 10000 0 80000 0 30000 0 120000 repayment 0 -33000 0 -16000 0 -57000 -106000 payment of interest 0 0 0 0 0 0 0 Total financing 10000 -33000 80000 -16000 30000 -57000 14000 Ending balance of cash 106520 100520 108060 100540 103320 100840 100840 INCOME STATEMENT: Sales 1895000 less cogs 1478100 gross profit 416900 Less: operating expenses: wages and salaries 302000 rent 30000 depreciation 12000 344000 operating profit 72900 Less: other expenses interest 15000 Income before tax 57900 tax expense (35%) 20265 Net Income 37635 BALANCE SHEET: ASSETS: Cash 100840 Inventory 242400 Tax paid in advance 50000 Acounts receivable 410560 Fixed assets-net 278000 1081800 LIABILITIES: Accounts payable 162400 Bank borrowing 14000 Bonds 500000 Tax payable 20265 Common stock + retained earnings 385135 1081800