Assignment
Using Word or Excel prepare the following budgets in order, for Peter's Pet Toys. Use good form. Double rule the completed budgets. Use $ signs for totals that represent dollars, to distinguish from units. Each budget should show 4 quarters of calendar year, and a total for the whole year, where appropriate. Terminology can be flexible. For example some schedules can be labeled budgets.
Taken together, these individual budgets create the Master Budget.
SALES FORECAST/BUDGET INFO
1) Prepare twoSales Budget by Quarter, given the following information. Format for 2 decimal places.
|
|
Cat
|
Dog
|
Pet
|
|
|
Castle
|
Den
|
Palace
|
|
Sales price, constant all year
|
$384.94
|
$466.67
|
$412.37
|
|
Q1 projected sales units
|
247
|
180
|
100
|
|
Q2 projected sales units
|
281
|
188
|
115
|
|
Q3 projected sales units
|
308
|
190
|
131
|
|
Q4 projected sales units
|
333
|
192
|
139
|
General format/layout of the first sales budget.
|
|
Q1
|
|
|
Q2
|
|
|
Q3
|
|
|
Q4
|
|
|
Castle
|
Den
|
Palace
|
Castle
|
Den
|
Palace
|
Castle
|
Den
|
Palace
|
Castle
|
Den
|
Palace
|
|
Budgeted Units
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Sales prices
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Total Budgeted Sales
|
|
|
|
|
|
|
|
|
|
|
|
|
General format of the second sales budget which will show only total sales for each quarter
|
|
Q1
|
Q2
|
Q3
|
Q4
|
Total
|
|
Total Budgeted Sales Dollars
|
|
|
|
|
|
2) CASH COLLECTION INFORMATION for SCHEDULE OF CASH COLLECTIONS
80% of sales - customers use a credit card
Peter's Pet Toys receives cash in the month of sale, minus a credit card fee of 5% of the sale.
10% of sales - cash, immediate receipt of cash upon sale
10% of sales - Peter's Pet Toys offers terms of n/90, allowing the customer to pay in full the next quarter after the sale is made.
Beginning A/R Balance is $25,000, collected in its entirety in the first quarter.
Format for whole #s or 2 decimals, as you wish.An outline of the schedule is shown below.
|
Q1
|
Q2
|
Q3
|
Q4
|
Year
|
|
A/R, Beginning balance
|
$25,000
|
|
|
|
|
|
Cash Sales (10% of sales)
|
|
|
|
|
|
|
Credit card sales (80% of sales less 5% credit card fee)
|
|
|
|
|
|
|
Sales on account, n/90
|
|
|
|
|
|
|
Totals
|
|
|
|
|
|
3) PRODUCTION INFORMATION for PRODUCTION BUDGET
Production - The 3 products can be aggregated for production purposes.
The sum of all the castles, dens, and palaces--combined--are the units used in the production budget.
There is not a separate production budget for each product as they undergo the same production process.
Peter's Pet Toys likes to keep 5% of the next quarter's sales level as the current quarter's Finished Goods ending inventory.
Projections are that Projected Units Sold in Q1 of the following year will be unchanged from Q4 the previous year.
Format for whole #s. No decimal places. Rounding differences or errors of +/- 1 are acceptable.
Beginning Finished Goods Inv for Q1 is 20 units.
Below is the start of the body of the production budget.
|
|
|
Q1
|
Q2
|
Q3
|
Q4
|
|
Budgeted Unit Sales per Quarter, based on all products combined
|
|
|
|
|
4) DIRECT MATERIALS BUDGET INFORMATION for DIRECT MATERIALS BUDGET
The totals of the Production Budget will be used to determine the amount and cost of Direct Material needed.
The amount of raw material needed to produce each completed toy is 15 lbs.
Raw material cost is $1.05/lb. for Q1, Q2, Q3. It is projected to rise to $1.08/ lb. in Q4
Peter's Pet Toys likes to keep 10% of the next quarter's raw material needs,plus an additional 20 lbs. in the current quarter's ending raw material inventory.
It is anticipated that Raw Material required in Q1 of the following year will be unchanged from Q4 the previous year.
Format for whole #s except for the raw material cost per unit of $1.05 or $1.08. No other decimals besides these.
Rounding differences or errors of +/- 1 are acceptable.
Beginning Raw Materials Inv for Q1 is 75 lbs.
DIRECT LABOR BUDGET INFORMATION
Direct labor hours per unit of production is 4.2. Wages are $18.50 per hour for Q1, Q2, and will rise to $19.50 per hour in Q3, Q4
Format for whole #s except for the labor hours of 4.2 and cost per unit of $18.50 or $19.50 No other decimals besides these.
5) CASH PAYMENTS BUDGET INFORMATION
To obtain the low prices for materials, Peter's Pet Toys must pay its supplier in cash upon receipt. As a result the final line of the Direct
Materials Budget also serves as the Cash Payments Budget. An additional Cash Payments Budget is not needed.
6) MANUFACTURING OVERHEAD BUDGET--TOTAL FACTORY OH AND CASH SPENT ON MFG OVERHEAD SHOULD BE SHOWN IN BUDGET
Variable Mfg Overhead per quarter is for each successive quarter, $13,845, $13,972, $14,010, $14,216.
The Fixed Mfg Overhead includes:
Storage Shed Rental Expense (Cat Castle) $10,000 for each of the 4 quarters
Depreciation Expense-Buzz Saw (Dog Den) $9,500 for each of the 4 quarters
Depreciation Plant, $100,000 per year; equally divided among the 4 quarters
Fixed Component of Maintenance Costs: $20,000 each quarter
Fixed Component of Utilities Costs, $8,000 each quarter
Plant Insurance Costs, $3,000 each quarter
Format for whole #s. No decimals.
7) SELLING AND ADMINISTRATIVE EXPENSES---TOTAL S&A EXPENSE AND CASH SPENT ON S&A SHOULD BE SHOWN IN BUDGET
Donation Expense, $20,000 in Q1 only. This will be an annual donation.
Delivery and Assembly, $16 per unit sold. (See sales budget)
Sales Commission, $59,000, all paid in Q4. (Pet Palace)
Salaries Expenses, $200,000, divided evenly among quarters
Depreciation Expense on Office, $20,000 annually, evenly divided each quarter
Fixed component of Maintenance Expense, $1500per quarter
Fixed Component of Utilities Expense, $1,000 each quarter
Office Insurance Expense, $9,000 each quarter
Format for whole #s. No decimals.
The outline for this budget is presented for you.
Note: Certain items appear in both the Mfg OH Budget AND the S&A Budget. Same type of cost/expense appears in the factory and the office.
|
For the Year Ending Dec 31, 20xx
|
|
|
|
|
|
|
|
Q1
|
Q2
|
Q3
|
Q4
|
Year
|
|
Variable:
|
|
|
|
|
|
|
Delivery and Assembly
|
|
|
|
|
|
|
Sales Commission (Pet Palace) Q4 only
|
|
|
|
|
|
|
Fixed:
|
|
|
|
|
|
|
Donation Expense
|
|
|
|
|
|
|
Salaries Expense
|
|
|
|
|
|
|
Depreciation Expense
|
|
|
|
|
|
|
Maintenance Expense
|
|
|
|
|
|
|
Utilities Expense
|
|
|
|
|
|
|
Insurance Expense
|
|
|
|
|
|
|
Total Selling and Admin Expenses
|
|
|
|
|
|
|
Less: Non-cash S&A Expenses
|
|
|
|
|
|
|
Total Cash paid for Selling and Admin Expenses
|
|
|
|
|
|
8) CASH BUDGET info to create a CASH BUDGET.
In addition to the above budgets, which show the effects on cash, the following information is needed to complete the Cash budget:
Beginning cash balance is $4,000,000.
An addition of $40,000 was made to Plant, Property, and Equipment in Q3. Cash was paid.
In Q4 cash dividends to investors in the amount of $17,000 were issued.
No financing, no borrowing, no interest, no repayments. The cash balance remains very high throughout the year.
Format for whole #s. No decimals.