Question

Hey tutor: Please look at the bold only #2- #6

Please type only. THanks

Preparation of Master Budgets, Budgeted Contribution Margin Income Statemnet, and Budgeted Blanace Sheet

Scenario Summary:

You have just been hired as a budget analyst for Polo Wears Company, a nationwide distributor of polo shirts. As a budget analyst, who reports to the CFO, you are responsible for all planning and budgeting. Your first assignment is to prepare a master budget for the next three months, beginning in April. You have been provided with the following information:

1. The company desires a minimum cash balance each month of $12,000.

2. The shirts are sold to retailers for $10 each.

3. Sales forecasts in units are as follows:

Month Sales volume

January (actual sales) 40,000

February (actual sales) 48,000

March (actual sales) 56,000

April 70,000

May 90,000

June 120,000

July 80,000

August 72,000

September 64,000

4. Ending inventories are supposed to equal 90% of next month's sales.

5. The shirt costs the company $5 to make.

6. Purchases are paid as follows: 50% in the month of sales and remaining 50% in the following month.

7. All sales are on credit, with no discount, and payable within 15 days. The company experience has been that only 25% of a month's sales are collected by month-end. An additional 50% is collected in the following month, and remaining 25% is collected in the second month following sales. Bad debts have been negligible. The company's monthly selling and administrative expenses are given below:

Variable: Amount

Amount Sales Commissions $2 per shirt

Fixed: Wages and Salaries $44,000

Utilities $28,000

Insurance $2,400

Depreciation $3,000

Other fixed costs $6,000

Total fixed expenses $83,400

8. All selling and administrative expenses are paid during the month, in cash, with exception of the depreciation and insurance expired. Land will be purchased during May for $50,000 in cash. The company declares dividends of $24,000 each quarter, payable in the first month of the following quarter. The company balance sheet at March 31, is given below:

Assets

Cash $28,000

Accounts Receivables ($120,000 Feb Sales; $420,000 March sales) $540,000

Inventory $315,000

Prepaid Insurance $28,800

Fixed assets, net of depreciation $345,400

Total assets $1,257,200

Liabilities and Stockholder's Equity

Accounts Payable $279,500

Dividends Payable $24,000

Common Stock $600,000

Retained Earnings $353,700

Total liabilities and SE $1,257,200

9. The company has an agreement with a bank that allows it to borrow in increments of $2,000 at the beginning of each month, up to a total loan balance of $280,000. The interest rate on these loans is 2% per month and is not a compound interest (simple interest). At the end of the quarter, the company would pay the bank all of the accumulated interest on the loan and as much as of the loan as possible, while still retaining at least $12, 000 in cash balance. Required: Using well-formatted and labeled spreadsheets, prepare a master budget for the three-month period ending June 30. Include the following detailed budgets:

(1) a. A sales budget by month and in total.

b. A schedule of expected cash collections from sales, by month and in total.

c. A merchandise purchases budget in units and in dollars. Show budget by month and in total.

d. A schedule of expected cash disbursements for merchandise purchases, by month and in total.

(2) Prepare a cash budget, showing budget by month and in total.

(3) Prepare a budgeted income statements for the three-month period ending June 30. Use the contribution margin approach.

(4) Prepare a budgeted balance sheet as of June 30.

(5) Using MS-Document, write a one page memo to the Chief Financial Officer (CFO) of Polo Wears Corporation in which you summary the master budget results and make any recommendation as to whether the company should continue with it is current borrowing arrangements. In your memo comment on the potential large accounts receivable balance in June (will this be a problem for the company, why or why not?). Other ideas and suggestions you may have to improve the operations.

(6) Provide information on the company's strengths or weakness based on pro forma income statement and balance sheet.

Answer 1

Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View - A - General 1 1 - Wrap Text Merge & Center 3 Autosum * * Cut La Copy Paste - Format Painter Clipboard Calibri BIU 12 - A A Conditional Format Formatting as Table Styles Cell Styles Insert Delete Format 2 Clear Sort & Find & Filter Select Editing Font Alignment Number Cells for Sales Budget IETFO Hik3 1 2 Months 3 Budgeted unit sales 4 Selling Price Per unit 5 Total sales Sales Budget April |May 70000 $ 10.00 $ $ 7,00,000.00 $ June 90000 10.00 $ 9,00,000.00 $ Quarter 120000 10.00 $ 12,00,000.00 $ 280000 10.00 28,00,000.00 Eco Uw collection From sales 25% In the month of sales 50% In the month following sales 25% In the Second month following sales Schedule of expected cash collections Months Sales April May $ 4,80,000.00 $ 1,20,000.00 June Quarter $ 1,20,000.00 12 13 February sales=(48000*$10)* 25% in April March Sales=(56000*$10)*50% in April and 14 (56000*$10)*25% in May $ 5,60,000.00 $ 2,80,000.00 $ 1,40,000.00 $ 4,20,000.00 $ 7,00,000.00 $ 1,75,000.00 $ 3,50,000.00 $ 1,75,000.00 $ 7,00,000.00 $ 2,25,000.00 $ April sales=($700000*25%) in April,($700000*50%) 15 in May and ($700000*25%) in June May sales=($900000)*25% in May and 16 ($900000*50%) in June 17 June Sales=($1200000*25%) in June 18 Total Cash collections He Sheet1 Sheet2 Sheet3 Ready $ 9,00,000.00 $ 12,00,000.00 $ 38,40,000.00 $ 4,50,000.00 $ 3,00,000.00 $ 9,25,000.00 $ 6,75,000.00 3,00,000.00 22,15,000.00 5,75,000.00 $ 7,15,000.00 $ Average: 445606.7486 Count: 293 Sum: 77981181 B op OU 100% 0 ve 10:17 ^ ENG 22-11-2019 + .

Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View BE * Cut Copy = = = * General Calibri BIU - 12 - A A - A Wrap Text Merge & Center 1 D . LE Paste Format Painter Clipboard 3 A AutoSum" AT 2 Conditional Format Formatting as Table Styles Cell Styles 2222222 Insert Delete Format 2 Clear Sort & Find & Filter Select Editing Font Alignment Number Cells A1 fx Sales Budget в Тc TD E Merchandise Purchase Budget April |May June Quarter 70000 90000 120000 280000 20 21 22 Budgeted Unit Sales=(A) Add:Ending Inventory=(900000*90%) in April,(120000*90% in May),(80000*90%) in 23 June=(B) 24 Total Needs=(C)=(A)+(B) 25 Less: Opening Inventory=(D) 26 Required Purchases=(E )=(C)-(D) Cost of Purchase Per Unit=(F) 28 Total Cost of Purchase=(E )*(F) 81000 151000 63000 88000 5.00 $ 4,40,000.00 $ 108000 198000 81000 117000 5.00 $ 5,85,000.00 $ 72000 192000 108000 84000 5.00 $ 4,20,000.00 $ 72000 352000 63000 289000 5.00 14,45,000.00 $ $ April Quarter $ 2,79,500.00 33 May 4,40,000.00 30 Budgeted Cash disbursement of Merchandise Purchase 31 Months May June 32 Accounts Payable $ 2,79,500.00 April=($4400000*50%) in April,($440000*50%) in $ 2,20,000.00 $ 2,20,000.00 May=($585000*50%) in May an ($585000*50%) in 34 June $ 2,92,500.00 $ 35 June=($420000*50%) in June $ 36 Total Cash Payments $ 4,99,500.00 $ 5,12,500.00 $ 37 2,92,500.00 $ 2,10,000.00 $ 5,02,500.00 $ 5,85,000.00 2,10,000.00 15,14,500.00 Sheet1 Sheet2 Sheet30 Ke Ready Average: 445606.7486 Count: 293 + Sum: 77981181 B op O ^ 100% 0 u 10:17 ENG 22-11-2019 -

Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View BELLEZA * Cut Copy = = = * General Calibri BIU - 12 - A A - A Wrap Text Merge & Center 1 1 - 3 Autosum * Paste Insert Delete Format Conditional Format Formatting as Table Styles Format Painter Clipboard Cell Styles 2 Clear Sort & Find & Filter Select Editing Font Alignment Number Cells E G H I J K 28,000.00 22,15,000.00 22,43,000.00 A1 fx Sales Budget B C D 39 Cash Budget for the three months ending June 30th 40 Months April |May June Quarter 41 Cash Balance $ 28,000.00 $ 12,500.00 $ 12,000.00 $ 42 Add: Collections from customer $ 5,75,000.00 $ 7,15,000.00 $ 9,25,000.00 $ 43 Total Cash available $ 6,03,000.00 $ 7,27,500.00 $ 9,37,000.00 $ 44 Less: Disbursement 45 Merchandise Purchase $ 4,99,500.00 $ 5,12,500.00 $ 5,02,500.00 $ 46 Wages and Salaries $ 44,000.00 $ 44,000.00 $ 44,000.00 $ 47 Utilities $ 28,000.00 $ 28,000.00 $ 28,000.00 $ 48 Commissions(70000*$2),(90000*$2),(120000*$2) | $ 1,40,000.00 $ 1,80,000.00 $ 2,40,000.00 49 Other Fixed Cost $ 6,000.00 $ 6,000.00 $ 6,000.00 $ 50 Land Purchased 50,000.00 51 Dividend Paid $ 24,000.00 Total Disbursemnts $ 7,41,500.00 $ 8,20,500.00 $ 8,20,500.00 $ 53 Excess/Deficiency of receipts over $ -1,38,500.00 $ -93,000.00 $ 1,16,500.00 $ 54 disbursements 55 Financing: Borrowings $ 1,51,000.00 $ 1,05,000.00 $ 57 Repayments -91,000.00 $ 58 Interest=(1510000*2%*3+105000*2%*2) $ -13,260.00 $ 59 Total Financing $ 1,51,000.00 $ 1,05,000.00 $ -1,04,260.00 $ 60 Cash Balance ,ending $ 12,500.00 $ 12,000.00 $ 12,240.00 $ ** Sheet1 Sheet2 Sheet3 Ready 15,14,500.00 1,32,000.00 84,000.00 5,60,000.00 18,000.00 50,000.00 24,000.00 23,82,500.00 -1,39,500.00 52 56 Bor 2,56,000.00 -91,000.00 -13,260.00 1,51,740.00 12,240.00 511 Average: 445606.7486 Count: 293 O 100% 0 + Sum: 77981181 B op ^ ENG 10:17 22-11-2019 W

Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View * Cut Copy = = = * General Calibri BIU - 12 - A A - A Wrap Text Merge & Center 1 D . LE Paste Format Painter Clipboard 3 A AutoSum" AT Conditional Format Formatting as Table Styles Cell Styles Insert Delete Format 2 Clear Sort & Find & Filter Select Editing Font Alignment Number Cells AD fx Sales Budget G H I J K 63 в стрт Budgeted Income Statement For the three months ended June 30th $ 28,00,000.00 $ 28,00,000.00 $ $ 14,00,000.00 5,60,000.00 $ $ 19,60,000.00 8,40,000.00 65 66 Sales=(A) 67 Variable Expenes Cost of goods sold=($315000+$1445000- 68 72000*5)=(B) 69 Commission=(C) 70 Contribution Margin=D )=(A)-(B)-(C) 71 Fixed Expenses= (E) 72 Wages & Salaried 73 Utilities 74 Other Fixed Cost 75 Insurance($2400*3) 76 Depreciation=($3000*3) 77 Net Operating Income=(D )-(E) 78 Interest Expense=(F) 79 Net Income=(D )+(F) 1,32,000.00 84,000.00 18,000.00 7,200.00 9,000.00 $ $ $ $ 2,50,200.00 5,89,800.00 13,260.00 5,76,540.00 80 81 82 83 84 Budgeted Balance Sheet Sheet1 Sheet2 Sheet30 Ke Ready Average: 445606.7486 Count: 293 + Sum: 77981181 B op O ^ 100% 0 ve 10:17 ENG 22-11-2019

Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View * Cut Copy = = = * General Calibri BIU - 12 - A A - A Wrap Text Merge & Center 1 D . LE Paste Format Painter Clipboard 3 A AutoSum" AT Conditional Format Formatting as Table Styles Cell Styles Insert Delete Format 2 Clear Sort & Find & Filter Select Editing Font Alignment Number Cells A1 for Sales Budget A Тв Тc To TE G H I J K Budgeted Balance Sheet 85 June 30th 86 Assets 87 Cash 88 Accounts Receivable(Schedule A) 89 Inventory 90 Prepaid insurance=($28800-$7200) $ 12,240.00 $ 11,25,000.00 $ 3,60,000.00 $ 21,600.00 91 Property & Equipment ,net=(345400+50000+9000) 92 Total Assets $ 3,86,400.00 $ 19,05,240.00 93 94 Liabilities and stockholder's equity 95 Accounts Payable=($420000*50%) 96 Dividend Payable 97 Bank Loan($256000-$91000) 98 Common Stock 99 Retained Earning (Schedule B) 100 Total Liabilities and stockholder's equity 101 $ 2,10,000.00 $ 24,000.00 $ 1,65,000.00 $ 6,00,000.00 $ 9,06,240.00 $ 19,05,240.00 102 103 Schedule (A) 104 Accounts Receivable 105 Mauealaclcannon$2504 Sheet1 Sheet2 Sheet3 Ready © 225 on nnl Average: 445606.7486 Count: 293 + Sum: 77981181 B op O ^ 100% 0 u 10:17 ENG 22-11-2019 -

Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View * Cut Copy = = = * General Calibri BIU - 12 - A A - A Wrap Text Merge & Center 1 D . LE Paste Format Painter Clipboard 3 A AutoSum" AT Conditional Format Formatting as Table Styles Cell Styles Insert Delete Format 2 Clear Sort & Find & Filter Select Editing Font Alignment Number Cells A1 for Sales Budget с TD TE 99 Retained Earning (Schedule B) 100 Total Liabilities and stockholder's equity $ 9,06,240.00 $ 19,05,240.00 101 $ 2,25,000.00 $ 9,00,000.00 $ 11,25,000.00 102 103 Schedule (A) 104 Accounts Receivable 105 May sales($900000*25%) 106 June sales=(1200000*75%) 107 Total 108 Schedule (B) 109 Retained Earnings at june 30th 110 Balance ,March 31st 111 Add: Net Income 112 Total 113 Less: Dividend declared 114 Balance June 30th $ $ $ $ $ 3,53,700.00 5,76,540.00 9,30,240.00 24,000.00 9,06,240.00 117 119 120 121) Ke Ready Sheet1 Sheet2 Sheet30 Average: 445606.7486 Count: 293 + Sum: 77981181 B O 100% 0 u 10:18 PADENG 224 22-11-2019 -