Budgeting in Excel
Hints
Sunny and Clear, Inc. is a small wholesale distributor of consumer goods. The company generates a gross margin shown in the blue table. The percent of cash sales is shown in the blue table; the remainder is sold on account and is collected one month later. Accounts receivable on March 31,2020 are the result of March's credit sales to be collected the next month. Budgeted sales for the period were as follows: \begin{tabular}{|l|r|} \hline Budgeted Sales & \\ \hline April & $55,000 \\ \hline May & $57,000 \\ \hline June & $54,000 \\ \hline July & $53,000 \\ \hline \end{tabular} Required: Using the data above, for the 2nd quarter ending June 302020 , prepare the following: a. The schedule of the expected cash collections b. The merchandise purchases budget: c. The schedule of expected cash disbursements - merchandise purchases. d. The schedule of expected cash disbursement-Selling and administrative expenses e. The cash budget: f. An absorption costing income statement, for the quarter ending June 302020 g. A balance sheet as of June 30,2020 -Provide a short write up (2-3 paragraphs) of the cashflow situation at this company after you completed the budgets. What are your concerns and what would you recommend to management? - -(a) Cash collection, for April, what you collect will be the A/R balance from March 31 and April's cash sale. - -(b) Purchase budget, you will find that you need to calculate your COGS to fulfill the desired ending inventory. You are not given the COGS directly, but if you recall from earlier chapters when we discussed income statement: - Sales - COGS = Gross Profit Margin - OP expenses = Net Income - Sales (100%)= COGS + Gross Profit Margin, you will find in your blue data box that you are given the Gross Profit Margin \%. You can then calculate your COGS. - - (c) expected cash disbursement for purchasing your inventory: April's cash disbursement is going to be March 31's A/P balance + April's Inventory Purchase in cash (\% found in blue box x the budgeted purchase you calculated in schedule (b) - May's cash disbursement is going to be April's purchase on account + May's cash purchase. Schedule a; Total cash collection for Q2 is $155,290 *A/R at the end of Qtr that you will list on your balance sheet is June's budgeted credit sales that you have not collected in June. Schedule b: Total Merchandise Purchase for Q2 is $134,782 Schedule c: Total Merchandise Purchase Cash Disbursements for Q2 is $136,239 *A/P at the end of Qtr that you will list on your balance sheet is Sept budgeted purchase that you have not yet paid Schedule d: SG\&A Cash Disbursements for Q3 is $43,120 *Remember depreciation is a non cash expense, so you would not include the depreciation in this schedule Schedule d: Cash budget, for April, the cash balance before financing is $16,769 Line of credit available is $30,000 and loan can be taken out a $1,000 increment Schedule f: Income Statement *Remember to include depreciation expense and interest expense. The instructions stated the interest rate is 1.5% per month and interest is not compound. In other words, April interest expense will be April's borrowing x 1.5%, in May's interest will be (April's borrowing + May's borrowing) 1.5%, and in Sept interest will be (April's borrowing + May's borrowing+ June's borrowing) 1.5%. The sum of the three months interest expense will be your total interest expense for the qtr that will show up on your income statement. Schedule f: Balance Sheet *Review the example in the Royal Case Study or review your BA 211 notes. Net Equipment is cost - accumulated depreciation (remember there are new equipment purchases) and remember to calculate the new Retained Earnings ending balance. Hopefully you remember: RE Beginning Bal + Net Income Dividends =RE Ending Balance Sunny and Clear, Inc. is a small wholesale distributor of consumer goods. The company generates a gross margin shown in the blue table. The percent of cash sales is shown in the blue table; the remainder is sold on account and is collected one month later. Accounts receivable on March 31,2020 are the result of March's credit sales to be collected the next month. Budgeted sales for the period were as follows: \begin{tabular}{|l|r|} \hline Budgeted Sales & \\ \hline April & $55,000 \\ \hline May & $57,000 \\ \hline June & $54,000 \\ \hline July & $53,000 \\ \hline \end{tabular} Required: Using the data above, for the 2nd quarter ending June 302020 , prepare the following: a. The schedule of the expected cash collections b. The merchandise purchases budget: c. The schedule of expected cash disbursements - merchandise purchases. d. The schedule of expected cash disbursement-Selling and administrative expenses e. The cash budget: f. An absorption costing income statement, for the quarter ending June 302020 g. A balance sheet as of June 30,2020 -Provide a short write up (2-3 paragraphs) of the cashflow situation at this company after you completed the budgets. What are your concerns and what would you recommend to management? - -(a) Cash collection, for April, what you collect will be the A/R balance from March 31 and April's cash sale. - -(b) Purchase budget, you will find that you need to calculate your COGS to fulfill the desired ending inventory. You are not given the COGS directly, but if you recall from earlier chapters when we discussed income statement: - Sales - COGS = Gross Profit Margin - OP expenses = Net Income - Sales (100%)= COGS + Gross Profit Margin, you will find in your blue data box that you are given the Gross Profit Margin \%. You can then calculate your COGS. - - (c) expected cash disbursement for purchasing your inventory: April's cash disbursement is going to be March 31's A/P balance + April's Inventory Purchase in cash (\% found in blue box x the budgeted purchase you calculated in schedule (b) - May's cash disbursement is going to be April's purchase on account + May's cash purchase. Schedule a; Total cash collection for Q2 is $155,290 *A/R at the end of Qtr that you will list on your balance sheet is June's budgeted credit sales that you have not collected in June. Schedule b: Total Merchandise Purchase for Q2 is $134,782 Schedule c: Total Merchandise Purchase Cash Disbursements for Q2 is $136,239 *A/P at the end of Qtr that you will list on your balance sheet is Sept budgeted purchase that you have not yet paid Schedule d: SG\&A Cash Disbursements for Q3 is $43,120 *Remember depreciation is a non cash expense, so you would not include the depreciation in this schedule Schedule d: Cash budget, for April, the cash balance before financing is $16,769 Line of credit available is $30,000 and loan can be taken out a $1,000 increment Schedule f: Income Statement *Remember to include depreciation expense and interest expense. The instructions stated the interest rate is 1.5% per month and interest is not compound. In other words, April interest expense will be April's borrowing x 1.5%, in May's interest will be (April's borrowing + May's borrowing) 1.5%, and in Sept interest will be (April's borrowing + May's borrowing+ June's borrowing) 1.5%. The sum of the three months interest expense will be your total interest expense for the qtr that will show up on your income statement. Schedule f: Balance Sheet *Review the example in the Royal Case Study or review your BA 211 notes. Net Equipment is cost - accumulated depreciation (remember there are new equipment purchases) and remember to calculate the new Retained Earnings ending balance. Hopefully you remember: RE Beginning Bal + Net Income Dividends =RE Ending Balance
