In: Accounting
The BCJ Company needs a master budget for the three months beginning April 1, 2020. The company retails widgets. The 2020 budget should be based on the following information. An ending minimum cash balance of $10,000 each month is required. Sales are forecasted at an average selling price of $8 per widget. Merchandise costs are $4 per widget. Currently, the company maintains an ending inventory balance equal to 20% of the next month’s projected cost of goods sold. Purchases during any given month are paid half in the month of purchase and half during the following month. Sales are 20% cash and 80% on credit (payable within 30 days), but experience has shown that 60% of monthly credit sales is collected in the current month, 40% in the next month.
Monthly operating expenses are as follows:
Wages and salaries $15,000
Insurance expired 150
Depreciation 1,200
Utilities 1,000
Advertising 300
Miscellaneous 500
Rent 400 per month+ 10% of monthly sales.
All operating expenses are paid as incurred, except insurance, depreciation, and rent. Rent of $400 is paid at the beginning of each month, and the additional 10% of sales is paid in the month following the sales. The company plans to buy some new equipment for $5,000 cash in June. Cash dividends of $1,500 are to be paid quarterly, beginning April 15. Dividends are declared on the 15th of the last month in the calendar quarter.
BCJ has an established line of credit with its bank, Third Fifth National. Money can be borrowed and repaid in multiples of $1,000, at an interest rate of 6% per annum. Management wants to minimize borrowing and repay rapidly. Interest is computed and paid when the principal is repaid. Assume that borrowing occurs at the beginning and repayments at the end of the months in question. Money is never borrowed at the beginning and repaid at the end of the same month. Compute interest to the nearest dollar.
Balance Sheet
March 31, 2020
Assets Liabilities
Cash $16,300 Accounts payable (inventory) $13,750
Accounts receivable (net) 19,200 Dividends payable 1,500
Inventory 4,000 Rent payable 6,000
Prepaid insurance 1,800 Total 21,250
Land, Building, Equipment (net) 75,000
Stockholders' Equity
Capital stock ($1 par value) 54,400
Retained earnings 40,650
Total assets $116,300 Total Liabilities & Stockholders' Equity $116,300
Recent and forecasted sales:
January $45,000 February $50,000 March $60,000 April $40,000
May $50,000 June $70,000 July $60,000
Required: 1. Prepare a master budget, using Excel, and all supporting schedules (including sales) for the months April, 2020 through June, 2020.
2. Prepare the budgeted Income Statement and Statement of Cash Flows for the quarter ended June 30, 2020 and the budgeted Balance Sheet at June 30, 2020.
Please give positive ratings so I can keep answering. It would help me a lot. Please comment if you have any query. Thanks! |
Due to character limit I am attaching the image of Balance Sheet |
BCJ | |||||
Sales Budget | April | May | June | Total | Note |
Budgeted Sales Revenue | 40,000.00 | 50,000.00 | 70,000.00 | 160,000.00 | C |
Cash sale is 20% | 8,000.00 | 10,000.00 | 14,000.00 | 32,000.00 | D=C*20% |
Credit sale is 80% | 32,000.00 | 40,000.00 | 56,000.00 | 128,000.00 | E=C*80% |
Collection Budget | April | May | June | Total |
Cash sale | 8,000.00 | 10,000.00 | 14,000.00 | 32,000.00 |
60% of Credit sale in the same month | 19,200.00 | 24,000.00 | 33,600.00 | 76,800.00 |
40% of Credit sale in the next month | 12,800.00 | 16,000.00 | 28,800.00 | |
Collection of December 31, 2019 accounts receivable | 19,200.00 | - | - | 19,200.00 |
Total Scheduled Collections | 46,400.00 | 46,800.00 | 63,600.00 | 156,800.00 |
Monthly merchandise purchases budget | April | May | June | Total | July | Note |
Monthly Sales Budget | 40,000.00 | 50,000.00 | 70,000.00 | 160,000.00 | 60,000.00 | C |
Sell price | 8.00 | 8.00 | 8.00 | 8.00 | A | |
Monthly Sales units | 5,000.00 | 6,250.00 | 8,750.00 | 20,000.00 | 7,500.00 | B=C/A |
Add: Closing | 1,250.00 | 1,750.00 | 1,500.00 | 1,500.00 | M= 20% of A of next month. | |
Less: Opening | 1,000.00 | 1,250.00 | 1,750.00 | 1,000.00 | N= 20% of A of same month. For April its $ 4,000/8. | |
Material Purchase Budget | 5,250.00 | 6,750.00 | 8,500.00 | 20,500.00 | H |
Monthly merchandise payment budget | April | May | June | Total | Note |
Material Purchase Budget | 5,250.00 | 6,750.00 | 8,500.00 | See H | |
Cost per unit | 4.00 | 4.00 | 4.00 | O | |
Material cost | 21,000.00 | 27,000.00 | 34,000.00 | 82,000.00 | P=H*O |
50% paid in the same month | 10,500.00 | 13,500.00 | 17,000.00 | 41,000.00 | Q= 50% of P |
50% paid in the next month | 10,500.00 | 13,500.00 | 24,000.00 | R= 50% of P of previous month | |
Payment for December 31, 2019 purchases | 13,750.00 | - | 13,750.00 | ||
Material Payment | 24,250.00 | 24,000.00 | 30,500.00 | 78,750.00 | S |
Operating expense budget | April | May | June | Total | |
Wages and salaries | 15,000.00 | 15,000.00 | 15,000.00 | 45,000.00 | |
Utilities | 1,000.00 | 1,000.00 | 1,000.00 | 3,000.00 | |
Insurance expired | 150.00 | 150.00 | 150.00 | 450.00 | |
Advertising | 300.00 | 300.00 | 300.00 | 900.00 | |
Miscellaneous | 500.00 | 500.00 | 500.00 | 1,500.00 | |
Rent (Fixed) | 400.00 | 400.00 | 400.00 | 1,200.00 | |
Rent (Variable) | 4,000.00 | 5,000.00 | 7,000.00 | 16,000.00 | T=C*10% |
Operating expense budget | 21,350.00 | 22,350.00 | 24,350.00 | 68,050.00 |
Answer 6- Cash budget | April | May | June | Total |
Beginning Cash Balance | 16,300.00 | 13,750.00 | 15,350.00 |
&nbs
Related Solutions1. It is April 2019. A US company needs to borrow $100,000,000 for three months starting...1. It is April 2019. A US company needs to borrow $100,000,000
for three months starting five months from now. The current 3-month
LIBOR is 2.5%. The company is afraid that rates may rise during
those five months before it obtains the loan. Should the company
buy or sell Eurodollar futures? And how many (ignoring the present
value of the basis point change)?
Which month should the futures settle/expire?
Assume that the appropriate Eurodollar future is trading at
97.4. What...
OJ Small Company needs a cash budget for the month of April 2020. The company's controller...OJ Small Company needs a cash budget for the month of April
2020. The company's controller has provided you with the following
information and assumptions:
A. The April 1, 2020 cash balance is expected to be $14,000.
B. All sales are on account. Credit sales are collected over a 3
month period -- 50 percent in the month of sale, 35 percent in the
month following sale, and 15 percent in the second month following
sale. Actual sales for Feb....
To prepare a master budget for April, May, and June of 2020, management gathers the following...To prepare a master budget for April, May, and June of 2020,
management gathers the following information:
Sales for March total 19,500 units. Forecasted sales in units
are as follows: April 19,500 ; May 17,100; June 21,300; July
19,500. Sales of 248,000 units are forecasted for the entire year.
The product’s selling price is $30.00 per unit and the total
product cost is $24.60 per unit.
b. Company policy calls for a given month’s ending finished
goods inventory to equal...
Budget Preparation Collins Company is preparing its master budget for April. Use the given estimates to...Budget Preparation
Collins Company is preparing its master budget for April. Use the
given estimates to determine the amounts necessary for each of the
following requirements. (Estimates may be related to more than one
requirement.)
a. What should total sales revenue be if territories A and B
estimate sales of 10,000 and 12,000 units, respectively, and the
unit selling price is $40?
$Answer
b. If the beginning finished goods inventory is an estimated
2,000 units and the desired ending inventory...
Herald company, a jacket selling company, prepares its master budget for the year 2020 on a...Herald company, a
jacket selling company, prepares its master budget for the year
2020 on a quarterly basis. The budget officer has gathered the
following data:
1. Estimated Sales for
each quarter is $230,000. All sales are on credit and all sales are
collected in the following quarter.
2. Cost of goods sold
for each quarter is $100,000.
3. Variable selling
and administrative expenses for each jacket are 10% sales
commission.
4. Fixed selling and
administrative (S&A) expenses for the...
Our company prepared a Master Budget for planning purposes before the beginning of the year. That...Our company prepared a Master Budget for planning purposes
before the beginning of the year. That budget was for unit sales of
32,000 chairs at a budgeted price of $100 per chair.
Actual sales for the period were 30,000 units totaling $2,910,000.
T
What was the total sales budget variance?
How much of the sales budget variance is attributable to a
difference in sales price (the Sales Price Variance)?
How much of the sales budget variance is attributable to the...
A company paid the $1,350 premium on a three-year insurance policy on April 1, 2020. The...A company paid the $1,350 premium on a three-year insurance
policy on April 1, 2020. The policy gave protection beginning on
that date. How many dollars of the premium will appear as an
expense on the calendar year 2020 income statement assuming the
accrual basis of accounting? Assuming the cash basis of
accounting?
Master Budget Project Okay Company is preparing to build its master budget. The budget will detail...Master Budget Project
Okay Company is preparing to build its master budget. The budget
will detail each quarter’s activity and the activity for the year
in total. The master budget will be based on the following
information:
a. This will be the first year of operation for Okay Company.
b. Budgeted unit sales by quarter for 2017 are projected as
follows: First quarter 6,300, Second quarter 6,100, Third quarter
6,100 & Fourth quarter 6,450. First and second quarter 2018
budgeted...
Master Budget Project Okay Company is preparing to build its master budget. The budget will detail...Master Budget Project
Okay Company is preparing to build its master budget. The budget
will detail each quarter’s activity and the activity for the year
in total. The master budget will be based on the following
information:
This will be the first year of operation for Okay Company.
Budgeted unit sales by quarter for 2017 are projected as
follows: First quarter 6,300, Second quarter 6,100, Third quarter
6,100 & Fourth quarter 6,450. First and second quarter 2018
budgeted sales units...
ABC Company has the following sales budget for the last six months of 2020:
Note: Not all sales are always collected. Remember Bad Debt Expense. So don’t worry if the problems do not add up to 100%.
1. ABC Company has the following sales budget for the last six months of 2020:
July $9,000
August $8,000
September $11,000
October $9,000
Historically, the cash collection of sales has been as follows:
35% of sales collected in the month of sale,
45% of sales collected in the month...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
ADVERTISEMENT
|