Question

Required: 1. Read the case "Cravat Sales Company". 2. Prepare the following budgets and schedules for the second quarter (April- June) using MS Excel. A Sales budget, by month and in total. A Schedule of expected cash collections from sales, by month and in total. A Merchandise purchases budget in units and in dollars. Show the budget by month and in total. A Schedule of expected cash disbursements for merchandise purchases by month and in total. An Operating expenses budget, by month and in total. A Schedule of expected cash disbursements for operating expenses, by month and in total. A Cash budget; Show the budget by month and in total. A Budgeted income statement for the three-month period ending June 30 (Use the contribution approach). You must have separate and clearly marked input and output sections. 3. 4. You may enter the details in the repayments and interest lines of the cash budget manually. This is because the programming skills required to perform this function are quite complicated. Apart from repayments and interest, there is to be no manual input into the Output Section. All cells are to contain formula. 6 For this project use the exact amount of borrowing. For example: Cash Deficiency Minimum Cash Requirement Borrowing Read the helpful hints given below 7. (51,500) 40,000 91,500 8.

Can you use excel and take a picture of the formulas used after you are done

Answer 1

Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View = = = General Calibri BI U - - 12 - A À - >- A Wrap Text Merge & Center 1 1 - * Cut La Copy Paste - Format Painter Clipboard 3 Autosum * Conditional Format Formatting as Table Styles Cell Styles Insert Delete Format 2 Clear Sort & Find & Filter Select Editing Font Alignment Number Cells A3 - For Sales Budget F G H I J 2 June 4 Months 5 Budgeted unit sales 6 Selling Price Per unit 7 Total sales Cravet Sales Company Sales Budget April |May 35000 $ 8.00 $ $ 2,80,000.00 $ 45000 8.00 $ 3,60,000.00 $ Quarter 60000 8.00 $ 4,80,000.00 $ 140000 8.00 11,20,000.00 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 $ 1,92,000.00 $ 48,000.00 13 June Quarter $ 48,000.00 $ 2,24,000.00 $ 1,12,000.00 $ $ 1,68,000.00 $ 2,80,000.00 $ 70,000.00 $ 1,40,000.00 $ 70,000.00 $ 2,80,000.00 15 February sales=(24000*$8)*25% in April March Sales=(28000*$8)*50% in April and 16 (28000*$8)*25% in May April sales=($280000*25%) in April,($280000*50%) 17 in May and ($280000*25%) in June May sales=($360000)*25% in May and 18 ($360000*50%) in June 19 June Sales=($4800000*25%) in June Sheet1 Sheet2 Sheet3 Ready $ 90,000.00 $ $ $ 3,60,000.00 4,80,000.00 1,80,000.00 $ 1,20,000.00 $ 270.000 onlc 2,70,000.00 1,20,000.00 ooc ooo on I 20 TALLIC-L--Il- + 1° Et See BOU 100% 0 - PADENG 121 12:35 13-11-2019 0

Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View = = = * Cut La Copy Paste - Format Painter Clipboard Calibri BI U - - 12 - A À - >- A Wrap Text Merge & Center General 1 1 % 9 18 23 conditional a forme - 3 insert Delete Format Autosum * 2 Clear so Conditional Format Formatting as Table Styles Cell Styles Insert Delete Format 2 Clear Sort & Find & Filter Select Editing se Font Alignment Number Cells A3 - Fax Sales Budget F G H I J Schedule of expected cash collections Months Sales April [May $ 1,92,000.00 $ 48,000.00 Quarter $ 48,000.00 $ 2,24,000.00 $ 1,12,000.00 $ 56,000.00 $ 1,68,000.00 13 14 15 February sales=(24000*$8)*25% in April March Sales=(28000*$8)*50% in April and 16 (28000*$8)*25% in May April sales=($280000*25%) in April,($280000*50%) 17 in May and ($280000*25%) in June May sales=($360000)*25% in May and 18 ($360000*50%) in June 19 June Sales=($4800000*25%) in June 20 Total Cash collections $ 2,80,000.00 $ 70,000.00 $ 1,40,000.00 $ 70,000.00 $ 2,80,000.00 90,000.00 $ 1,80,000.00 $ 1,20,000.00 $ 3,70,000.00 $ 2,70,000.00 1,20,000.00 8,86,000.00 2,86,000.00 $ 21 $ 3,60,000.00 $ $ 4,80,000.00 $ 15,36,000.00 $ 2,30,000.00 $ Cravet Sales Company Mercendise Purchase Budget April May June 35000 45000 Quarter 60000 140000 24 Budgeted Unit Sales=(A) Add:Ending Inventory=(450000*90%) in 25 April (60000*90% in May),(40000*90%) in June=(B) 26 Total Needs=(C)=(A)+(B) 27 Less: Opening Inventory=(D) 28 Required Purchases=(E)=(C)-(D) 29 Cost of Purchase Per Unit=(F) 30 Total Cost of Purchase=(E)*(F) n " Sheeti Sheet2 Sheet3 - Ready 40500 75500 31500) 44000 5.00 2,20,000.00 54000 99000 40500 58500 5.00 2,92,500.00 36000 96000 54000 42000 5.00 2,10,000.00 36000 176000 31500 144500 5.00 7,22,500.00 $ $ $ $ $ $ $ ä I + 1 e B e 9 BOJ 100% 0 op - ENG 13-1-2019

Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View * Cut Copy = = = Calibri BI U - - 12 - A À - >- A Wrap Text Merge & Center General 9.% , o -. 1 1 conditional Fore - 3 Insert Delete Format Autosum * Ž clear that Paste Insert Delete Format Format Painter Clipboard Conditional Format Formatting as Table Styles Cell Styles 2 Clear Sort & Find & Filter Select Editing there Font Alignment Number Cells A3 fx Sales Budget F G H I J 31 Required Purchases=Budgeted Sales+Ending Inventory-Opening Inventory 32 Total cost of Purchae=Required Purchases* cost of purchase per unit 33 June Quarter $ 85,750.00 34 Cravet Sales Company 35 Budgeted Cash disbursement of Mercendise Purchase 36 Months April May 37 Accounts Payable $ 85,750.00 April=($2200000*50%) in April,($220000*50%) in 38 May $ 1,10,000.00 $ 1,10,000.00 May=($292500*50%) in May an ($292500*50%) in 39 June $ 1,46,250.00 $ 40 June=($210000*50%) in June $ 41 Total Cash Payments $ 1,95,750.00 $ 2,56,250.00 $ 2,20,000.00 1,46,250.00 $ 1,05,000.00 $ 2,51,250.00 $ 2,92,500.00 1,05,000.00 7,03,250.00 43 44 45 Months 46 Cash Balance 47 Add: Collections from customer 48 Total Cash available 49 Less: Disbursement 50 Mercendise Purchase 61 Nazoandcalacios Sheet1 Sheet2 Sheet3 Ready Cravet Sales Company For the three months ending June 30th April May June $ 14,000.00 $ 10,250.00 $ $ 2,30,000.00 | $ 2,86,000.00 $ $ 2,44,000.00 $ 2,96,250.00 $ Quarter 10,000.00 $ 3,70,000.00 $ 3,80,000.00 $ 14,000.00 8,86,000.00 9,00,000.00 $ 1,95,750.00 $ 27 non no lo 2,56,250.00 $ 22 nnnnnlo 2,51,250.00 $ 22. nnn no lo 7,03,250.00 cc non nn 1 Đ 1 2 • Ô e $ 90 OD op - 100% 0 ENG 13-1-2019

Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View * Cut Copy = = = General Calibri BI U - - 12 - A À - >- A Wrap Text Merge & Center Fill Paste Format Painter Clipboard Insert Delete Format Conditional Format Formatting as Table Styles Cell Styles 2 Clear Z Et Sort & Find & Filter Select Editing Font Alignment Number Cells A3 for Sales Budget D E F G H I J в с Cravet Sales Company For the three months ending June 30th April May June $ 14,000.00 $ 10,250.00 $ $ 2,30,000.00 | $ 2,86,000.00 $ $ 2,44,000.00 $ 2,96,250.00 $ Quarter 10,000.00 $ 3,70,000.00 $ 3,80,000.00 $ 14,000.00 8,86,000.00 9,00,000.00 $ $ $ $ $ 1,95,750.00 $ 22,000.00 $ 14,000.00 35,000.00 3,000.00 43 44 45 Months 46 Cash Balance 47 Add: Collections from customer 48 Total Cash available 49 Less: Disbursement 50 Mercendise Purchase 51 Wages and Salaries 52 Utilities 53 Commissions(35000*$1),(45000*$1),(60000*$1) 54 Miscellaneous Expenses 55 Land 56 Dividend Paid 57 Total Disbursemnts 58 Excess/Deficiency of receipts over 59 disbursements 60 Financing: 61 Borrowings 62 Repayments 63 Interest=(48000*1%*3)+($79000*1%*2) 64 Total Financing 65 Cach Balance anding Sheet1 Sheet2 Sheet3 Ready 2,56,250.00 $ 22,000.00 $ 14,000.00 $ 45,000.00 $ 3,000.00 $ 25,000.00 2,51,250.00 $ 22,000.00 $ 14,000.00 60,000.00 $ 3,000.00 7,03,250.00 66,000.00 42,000.00 1,40,000.00 9,000.00 25,000.00 12,000.00 9,97,250.00 -97,250.00 $ $ $ 12,000.00 2,81,750.00 $ 37,750.00 $ 3,65,250.00 $ -69,000.00 $ 3,50,250.00 29,750.00 $ 48,000.00 $ 79,000.00 $ $ - 16,000.00 $ -3,020.00 $ - 19,020.00 $ 10 730 001 1,27,000.00 -16,000.00 -3,020.00 1,07,980.00 10 730.00 $ I$ 48,000.00 $ 10.250.000 $ 79,000.00 $ 10.000.000 BOJ 100% 0 ve 12:36 g A O ENG 12 13-11-2019 + .

Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View * Cut Copy = = = General Calibri BI U - - 12 - A À - >- A Wrap Text Merge & Center Fill Paste Insert Delete Format Conditional Format Formatting as Table Styles Format Painter Clipboard Cell Styles 2 Clear Z Et Sort & Find & Filter Select Editing Font Alignment Number Cells for Sales Budget B 63 Interest=(48000*1%*3)+($79000*1%*2) 64 Total Financing 65 Cash Balance ,ending $ $ 48,000.00 $ 10,250.00 $ C $ 79,000.00 $ 10,000.00 $ D -3,020.00 $ - 19,020.00 $ 10,730.00 $ -3,020.00 1,07,980.00 10,730.00 Cravet Sales Company Budgeted Income Statement For the three months ended June 30th $ 11,20,000.00 $ 11,20,000.00 $ 7,00,000.00 1,40,000.00 $ $ 8,40,000.00 2,80,000.00 71 Sales 72 Variable Expenes 73 Cost of goods sold 74 Commission 75 Contribution Margin 76 Fixed Expenses 77 Wages and Salaries 78 Utilities 79 Insurance($1200*3) 80 Miscellaneous Expenses 81 Depreciation=($1500*3) 82 Net Operating Income 83 Interest Expense 84 Net Income Sheet1 Sheet2 Sheet3 Ready 66,000.00 42,000.00 3,600.00 9,000.00 4,500.00 $ $ $ $ 1,25,100.00 1,54,900.00 3,020.00 1,51,880.00 Os 1 e 2: * e $ 9 OO op - 100% 0 ENG 13-1-2019

Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View BE * Cut Copy = = = General Calibri BI U - - 12 - A À - >- A Wrap Text Merge & Center N Z 2222 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 D F G H I J 89 - For Sales Budget B C 85 86 Cost of goods sold=Opening Stock+Purchases-Closing stock=($157500+$722500-(36000* $5) 87 88 Cravet Sales Company Budgeted Balance Sheet 90 June 30th 91 Assets 92 Cash $ 10,730.00 93 Accounts Receivable $ 4,50,000.00 94 Inventory(36000*$5) $ 1,80,000.00 95 Prepaid insurance=($14400-3600) $ 10,800.00 96 Property & Equipment ,net=(172700+25000-4500) $ 1,93,200.00 97 Total Assets $ 8,44,730.00 98 99 Liabilities and stockholder's equity 100 Accounts Payable=($183180*50%) $ 1,05,000.00 101 Borrowings $ 1,11,000.00 102 Dividend Payable $ 12,000.00 103 Common Stock $ 3,00,000.00 104 Retained Earning $ 3,16,730.00 105 Total Liabilities and stockholder's equity $ 8,44,730.00 106 107 Pranorty Plant & Fruinment=Beginning halance+Addition. Denroristinn ► Sheet1 Sheet2 Sheet3 Ready + 1 e B ê e 9 BOJ 100% of ^ ENG 0 13-11-2019 Q

Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View * Cut Copy = = = General Calibri BI U - - 12 - A À - >- A Wrap Text Merge & Center 1 1 - 3 Autosum * Paste - % 8.09 Insert Delete Format Format Painter Clipboard Conditional Format Formatting as Table Styles Cell Styles 2 Clear Sort & Find & Filter Select Editing Font Alignment Number Cells F G H I J fax Sales Budget А 102 Dividend Payable $ 12,000.00 103 Common Stock $ 3,00,000.00 104 Retained Earning $ 3,16,730.00 105 Total Liabilities and stockholder's equity $ 8,44,730.00 106 107 Property,Plant & Equipment=Beginning balance+Addition-Depreciation 108 109 Accounts Receivable 110 May sales(May Sales*25%) $ 90,000.00 111 June sales=(June Sales 75%) $ 3,60,000.00 112 Total $ 4,50,000.00 113 114 Retained Earnings at june 30th 116 Balance ,March 31st $ 1,76,850.00 117 Add: Net Income $ 1,51,880.00 118 Total $ 3,28,730.00 119 Less: Dividend declared $ 12,000.00 120 Balance ,June 30th $ 3,16,730.00 121 122 115 123 124 Sheet1 Sheet2 Sheet3 Ready BOU 100% 0 ve 12:36 g A O ENG 12 13-11-2019 + .