Posted: January 1st, 2020
Economic Course Project Hints
Course Project Hints: The beginning cash balance for April, is the cash from March 31 in the Asset section of the balance sheet. In the merchandise purchases budget, in April, we need 50% of March purchases (that amount is also given to us 3/31 Accounts payable of $100,000 on page 415). Therefore, Total cash disbursements for April is (50% x $316,000 April purchases) + ($100,000 remaining March purchases to be paid) = $258,000. Class, Here are some hints. Lets start from the beginning: SALES BUDGET: First, take budgeted sales in units for each month of April, May, and June, and multiply by the selling price of $10/unit.
You will get TOTAL SALES which you will also need to plug into the Income Statement later. For example, April should be 65,000 units times $10 = $650,000 Total Sales. For your Schedule of Expected Cash Collections, each months units must be multiplied by the $10 selling price to yield the months sales. Per page 414, we know 10% of a given months sales is collected in the 2nd month following the sale, 70% in the month following the sale, and 20% in the month of sale. Therefore, for example Total Cash Collection for April will consist of the last 10% of February sales and 70% of March sales and 20% of April sales: (26,000 Feb units x $10 x 0. 0) +(40,000 March units x $10 x 0. 70) + (65,000 April units x $10 x 0. 20) = $436,000. You have to repeat the same process for May and June that I just did for April, and then add up all three months to get the quarter. Next, we have the Merchandise Purchases Budget. Total Needs are Budgeted sales in units plus Budgeted ending inventory (budgeted ending inventory =40% of the next months sales in units according to the terms of the problem). Once we have Total Needs, we subtract the Beginning Inventory (which of course is the previous months ending inventory) to get Required Unit Purchases.
So for April, that is 65,000 April budgeted units + (0. 40 x 100,000 May budgeted units) = 105,000 units Total Needs less ending March inventory which is the same as April beginning inventory (which is 0. 40 x 65,000 April units = 26,000 units) = 79,000 units required purchases. [Be careful with the inventory: you cant just pull the $104,000 inventory off of the balance sheet on page 415 because the balance sheet is in dollars not units, so if you want to use that balance sheet inventory figure, you have to say $104,000 divided by $4 cost = 26,000 units. Getting back to our 79,000 units required purchases, now multiply direct materials cost of $4 times the Required Unit Purchases to get Required Dollar purchases, which would be 79,000 Required April Unit Purchases x $4 = $316,000 as Required dollar purchases for April. [Again, be careful, on page 414 the problem states that only 50% of any given months purchases are paid for during the month of purchase, with the remaining 50% paid for in the following month, that fact will come in handy for the next step: the Budgeted Cash Disbursements for merchandise purchases.
For April, we need to therefore know what 50% of March purchases are (that is also given to us 3/31 Accounts payable of $100,000 on page 415). Therefore, Total cash disbursements for April is (50% x $316,000 April purchases )+($100,000 remaining March purchases to be paid)= $258,000 which we will need in the next step for the Cash Budget. CASH BUDGET Remember that the beginning Cash balance of the month is the previous months ending cash balance. For April, we can use the 3/31 Cash ending balance of $74,000 as stated on the page 415 Balance Sheet for 3/31.
Next we add receipts from Customers (which we calculated in the Schedule of Expected Cash Collections as $436,000 for April, giving us Total Cash Available for April as $510,000. For the Disbursements, we already know what the Purchase of Inventory figure is that we calculated in the Budgeted Cash Disbursements (recall it was $258,000). Advertising is $200,000 per month per page 414; Rent is $18,000 per month per page 414; salaries are $106,000 per month per page 414; Sales commissions are 4% of Sales, so for April that is 65,000 units times $10 x 0. 04 = $26,000. Utilities are fixed at $7,000 each month.
We don’t have to consider Depreciation in the Cash Budget because depreciation is not a cash outlay. The problem states that Insurance is prepaid so we know that for this problem it will not appear on the Cash Budget just like depreciation wont (but remember that both Depreciation and Insurance will show up as expenses to be matched against revenue when we do the Income Statement later). The problem states that Dividends are paid at $15,000 per quarter in the first month of each quarter, so that means April (but not May or June) will have $15,000 for Dividends pay out in the Cash Budget.
The problem also states that equipment will be purchased in May at $16,000 and in June at 40,000 (but not in April, so we can ignore the cash outlay for equipment when calculating April Total Disbursements (which are therefore $630,000. ) Since April Total Cash Available is only $510,000 if we subtract the Disbursements of $630,000, we have a deficiency, -$120,000. Because the problem states that minimum ending cash balance must be $50,000 each month, we know we have to borrow some money from the bank. We need $120,000 to bring cash to zero plus $50,000 for the minimum ending cash balance for total borrowings of $170,000.
This $170,000 figure is already in increments of $1,000, so we dont need to round up our borrowings. (Note: When you do the cash budget you should have determined a deficiency (more cash outlay than revenue) in each of months April & May requiring you to borrow, but in June, you should have calculated an excess of receipts over disbursements leaving you money to repay interest and then principal in June. As to the interest calculation in June, lets review it: Rate is 1%, so Interest should be the sum of (the amount you borrowed in April of 170,000 times 3 months x 0. 01) and (the amount you borrowed in May times 2 months times 0. 01).
The reason that you have to include the month of June in your count of principal balance of loan amounts to multiple interest rate by is because those amounts were outstanding as owed to the bank during the month of June even though you didnt borrow any additional amounts during June. Remember, after you figured out the interest expense in June, you can calculate how much principal you can pay back (in increments of $1,000s) being careful to leave a cash balance of $50,000. P. S. April financing should be $170,000 as I showed above. When you calculate Mays borrowings, don’t even think about Aprils financing, do the calculation independently.
You wont pay any interest until June (dont worry about accruing it for a cash budget, and since the income statement is for all three months anyway, we can think of the June cash outlay for interest expense as paying off everything that would have been accrued anyway: please see my calculation for the June interest payment above (hint $5,300 is interest payment see my excel notes in the sample). You must pay off all interest due before the bank lets you pay any principal. One last Hint: Ending cash balance for the quarter is the same as June 30 cash balance, and should be $94,700.
For all of the other Quarterly amounts, you can add the April, May and June amounts. If you havent fallen asleep reading yet, on to the Income Statement and Balance Sheet: The income statement reflects revenue and expenses over a PERIOD in time (here the quarter including April, May and June). To prepare the income statement, your sales revenue should be the sum of the quarter that you calculated as TOTAL SALES (you calculated that figure for each month by multiplying budgeted sales in units each month (total of 215,000 units) times selling price per unit of $10).
For the quarter the COST OF GOODS SOLD is the 215,000 units times the direct materials cost of $4 per unit. Then the COMMISSIONS expense is 4% of sales (so 0. 04 x 215,000 units x $10). The sum of the variable cost of goods sold and commissions yields a subtotal of variable costs, and then after that amount is subtracted from sales, you get the Contribution Margin. Fixed Expenses is next, which are the Quarterly amounts you calculated in the Cash Budget for Advertising, Rent, Salaries, and Utilities, except that you now need to include DEPRECIATION expenses (that did not appear in the cash budget since depreciation is not a cash outlay).
Also, because the problem stated that INSURANCE had been prepaid, insurance did not appear in the cash budget, but it is a fixed expense that must be recorded each month like depreciation in order to match revenues against expenses. You dont need to include the Equipment purchases on the income statement as expenses because they are capitalized as assets on the balance sheet instead of expensed on the income statement per Generally Accepted Accounting Principles. Dividends are a reduction of Retained earnings on the balance sheet and are not expensed on the income statement per GAAP.
Once you total the fixed expenses and subtract them from the Contribution margin, you have Net Operating Income. Now, you have to subtract the total INTEREST expense that you calculated in the Cash budget to get Net Income. (Note: When you did the cash budget you should have determined a deficiency (more cash outlay than revenue) in each of months April ; May requiring you to borrow, but in June, you should have calculated an excess of receipts over disbursements leaving you money to repay interest and then principal. Were you confident about your interest calculation in June?
Lets review it: Interest should be the sum of (the amount you borrowed in April times 3 months x 1%) and (the amount you borrowed in May times 2 months times 1%). The reason that you have to include the month of June in your count of principal balance of loan amounts to multiple interest rate by is because those amounts were outstanding as owed to the bank during the month of June even though you didn’t borrow any additional amounts during June. Remember, AFTER you figured out the interest expense, you can calculate how much principal you can pay back (in increments of $1,000s) being careful to leave a cash balance of $50,000. Actually, since you should have enough to pay the entire interest amount and the entire loan amount in June and still have 94,700 left as ending June cash balance. ) Please note: The quarter ending cash balance is the same as the June ending cash balance since the last day of the quarter is June 30th. This in contrast to the revenue and variable expenses which will be the amounts that represent the SUM of April, May and June as I described above, and so will the fixed expenses and interest be the sum of the three months worth (not just the June amounts.
In other words, only the ending cash balance will be the amount for June. ) Balance Sheet Help The balance sheet is given at a POINT in time (unlike the Income statement that is for a period of time). This means that all amounts will represent balances at June 30. The other thing to remember about the balance sheet is that total assets ALWAYS equals the sum of liabilities and stockholders equity. Assets The ending balance of CASH that you calculated when doing the Cash Budget will therefore be your line item for cash on the balance sheet.
ACCOUNTS RECEIVABLE : Since page 414 states 20% of a months sales are collected in the month of sale, 70% in the following month, and 10% in the second month, we know at June 30th, we still have 10% of Mays sales outstanding to be collected in July, and 80% of Junes sales (60% of which will be collected in July and 10% of which will be collected in August). This means we have for your June ACCOUNTS RECEIVABLE, you have 10% of May sales (which is 100,000 May units x $10 x 0. 10 = $100,000) plus 80% of June sales (which is 50,000 June units x $10 x 0. 80 = $400,000) for a total June 30 A/R of $500,000.
Recall that the problem states that ending INVENTORY should be 40% of next months sales. (since inventory is to be 40% of the next months sales multiply 0. 40 x direct materials product cost of $4 x 30,000 July sales units). Unexpired INSURANCE is next (think of this as Prepaid Insurance: Beginning balance as of March 31st was $21,000 according to the 3/31 balance sheet n page 415, so from that amount you now have to subtract each of the amounts of insurance that represent the amounts that would have been due for April, May, and June, since those periods have already lapsed as of the date of the June 30th balance sheet you are preparing.
Hint: you already calculated the amount of insurance you now need to subtract when you prepared the fixed expense portion of the income statement.
FIXED ASSETS NET OF DEPRECIATION: Again, start with the ending balance on the March 31st balance sheet, then add the purchase in May and the purchase in June and subtract the three months of depreciation expenses that have elapsed since the March 31st balance sheet date.
Hint: you already calculated the amount of depreciation you now need to subtract when you prepared the fixed expense portion of the income statement. Total up all the assets.
LIABILITIES ACCOUNTS PAYABLE (purchases): Remember that the problem states how purchases are paid: 50% in the month of purchase and the remaining 50% in the following month. Therefore, at the end of June we know that 50% of June purchases remain unpaid, so A/P is 50% of the required dollar purchases for June that you calculated when you prepared the Merchandise purchases budget.
DIVIDENDS PAYABLE: The problem states that dividends of $15,000 are paid in the first month following each quarter, so we know that the $15,000 dividends that accrued during the quarter comprised of April, May and June wont be paid until July, and therefore have to be listed as payable as of June 30th for your balance sheet. Recall that dividends of $15,000 for the first quarter were paid in April, so the beginning balance of $15,000 in this account is not there anymore. LOANS PAYABLE: Beginning balance is zero, so all we have is the sum of borrowings during April and May (there were no June borrowings) MINUS June repayments.
Technically, we would also have to add the interest accrued on the borrowings but we could subtract it right back out because it was paid in full in our Cash budget as of June 30.
EQUITY CAPITAL STOCK of $800,000 did not change since March31 since the problem does not state that the company authorized any additional stock or bought back any treasury stock.
RETAINED EARNINGS: There are two ways to come up with this figure The quick and dirty way is to take total assets and from that figure subtract the sum of (Accounts Payable + Dividends Payable+ Loan Payable + Capital Stock).
The correct way is to take the Beginning Balance of Retained Earnings as of March 31st, which is $580,000 and add the net income you calculated from the budgeted income statement and then subtract the $15,000 dividends declared since the last balance sheet date. (In our case the dividends were only declared and not paid because they reside in the dividends payable account as noted above, but that does not affect the computation. ) Hope this helps! p. s.
Heres more hints: On the master budget, for the project budget, in the Total Column, the beginning inventory is from the first period/quarter and the ending inventory is from the last period/quarter. This is a common error. This is also an issue on the direct materials budget (beginning and ending raw materials) and the cash budgets (cash). On the cash budget schedule, the ending cash from the first period will be the beginning cash for the following period.
The ending March 31 cash balance has to be the April 1st opening balance of cash on the Cash Budget, and it is also the beginning cash balance of the “quarter” column on the Cash Budget. Since insurance is only paid in November, for the CASH BUDGET it is only a line item in Nov. We know in Nov the payment will be 3000 x 12 = 36,000 since per page 14 the company allocates 3000/mo to insurance expense (but for our purposes all we care about is the fact that the company allocates insurance expense at 3000/mo, meaning 3000 x 3 = 9,000 for the income statement for any three month quarter. Insurance will also appear on the balance sheet since the prepaid portion of it will be a current asset (it is a current as opposed to a long term asset since it will be all used up with a year).
Depreciation is stated at $14,000/mo (again depreciation is not a cash outlay, but it is an expense on the income statement with a corresponding amount going each month to accumulated depreciation account on the balance sheet (the accumulated depreciation acts as a contra account to bring down he balance of the equipment cost sheet line item on the balance sheet. ) The amount of inventory really doesn’t have anything to do with depreciation, since depreciation is a means of writing off equipment over its useful life. Assuming a five year life, a 14,000 monthly depreciation expense would mean the original cost of the equipment would be about $840,000 (I have one client who has one replacement part that costs that amount for just one manufacturing molding machine, so don’t be surprised by this large monthly depreciation expense! )
