Answered step by step
Verified Expert Solution
Question
1 Approved Answer
The assignment is out of 100. Grades are allocated for each section as follows: Total Point Allocation Total Possible Sales Budget 6 Collection Budget 12
The assignment is out of 100. Grades are allocated for each section as follows:
Total Point Allocation Total Possible
Sales Budget 6
Collection Budget 12
Merchandise Purchases Budget 12
Merchandise Payments Budget 9
Labour Budget 13
General and Administration Budget 9
Cash Budget 21
Ending Inventory/Cost of Goods Sold 6
Budgeted Income Statement 5
Budgeted Balance Sheet 7
Total 100
Grades are awarded for accuracy, completeness and the use of formulas in excel to calculate those numbers where applicable.
Using formulas will actually help you and your grade so that if you make an error at the start that has a ripple effect, you will not be penalized repeatedly in different areas for that original error. If you choose to just input the number, not using a formula, if applicable, then if it is incorrect, not only will you lose marks for not using the formula but you will get repeatedly penalized for accuracy resulting from an earlier mistake.
Details:
Its March 31st, and Big Kahuna (BK) is asking you to complete their monthly budget for the next three months. They buy Patio Sets from a wholesaler for an average cost of $150 each and plan to sell the following units at the average selling price shown.
Month Sales (# of Units) Average Selling Price
February Actual 1500 $299
March Actual 2700 $289
April Estimate 3000 $285
May Estimate 4000 $275
June Estimate 4800 $275
July Estimate 5000 $275
The prices fluctuate as old units are sold off in the Winter and as the new units are rolled out in the busy Spring season. Some customers pay right away and others are invoiced on account. Historically, 55% of the sales are collected in the month of sale, with 30% more in the next month and the balance in the second month.
It takes time to build all the Patio Sets so they need to be in stock early. BK likes to end the month with enough inventory to satisfy 80% of the next months sales estimate. In practice, they are not always exact on this. On March 31, they had 2400 units in inventory at an average cost of $150.
Because BK carries so much inventory, it helps that they can delay payment to the wholesaler. Typically, BK pays 40% of the invoice cost in the month of purchase and the balance in the following month. The actual purchases made in March was $315,000.
Most of the variable labour is used in moving and assembling inventory so the number of units purchased is the most accurate driver of variable labour costs. Variable labour averages @ $20 per unit purchased in the month of purchase. Fixed labour is estimated to be $39,000 for the quarter to cover management and office staff salaries. Total Labour expense in March was $73,000.
Payroll processing and reporting cutoffs mean that 60% of the labour is paid in the month it is earned and the rest is paid the next month.
General and Administration expenses are driven by unit sales so that is the driver used to forecast them. Variable G&A averages $75 per unit and fixed G&A is $15,000 each month. The fixed G&A value includes $3,000 of depreciation expense.
The balance sheet from March 31st is given and it shows opening bank balance for the quarter. In addition to the cashflows already mentioned, the company plans to pay a $20,000 dividend in May and purchase $100,000 of new storage equipment in June. The equipment will not be used in June, so it will not affect the monthly depreciation.
The company has a $100,000 line of credit available to them and as of March 31st, it is unused. BK has a terrific relationship with the bank, so the line is provided at a 0% interest rate as long as they only use it periodically. You can assume no interest. The company has no overdraft protection on the bank account, so they need to borrow from the line to keep the minimum balance of $0.
Below is the Balance Sheet as of March 31st:
31-Mar
Cash $110,000
Accounts Receivable 418,410
Merchandise Inventory $360,000
Fixed Assets $1,000,000
Less: Depreciation -$360,000
Total Assets $1,528,410
Accounts payable $189,000
Wages Payable $29,200
Line of credit $0
Total Liabilities $218,200
Common Shares $1,104,000
Retained Earnings $206,210
Total Liabilities and Equity $1,528,410
I need just cash budget
rest i have
please and thank you
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started