Description
Competency
In this project, you will demonstrate your mastery of the following competency:
Prepare reports for internal stakeholder use
Overview
All organizations need a plan, and, for financial purposes, the budget is that plan. Cost accountants add value to their organizations by creating budgets for managers that plan for expected revenues, expenses, and use of organizational resources. In this project, you will take on the role of a cost accountant tasked with creating budgets for one of an organization’s most important resources: cash.
Directions
Using the scenario provided in the Supporting Materials section, complete the Project Two Workbook Template found in the What to Submit section. Appropriate formulas are required in the Excel cells. For all quarterly budgets, report monthly amounts and the total for the quarter. Round answers to two decimal places (e.g, 40.35).
Specifically, you must address the following rubric criteria:
Prepare accurately the sales forecasts and cash receipts for the third quarter using correct Excel formulas. Include the following details in your response:
Sales forecast
Cash receipts budget
Prepare accurately the production and direct material purchases for the third quarter using correct Excel formulas. Include the following details in your response:
Production budget
Direct materials purchases budget
Prepare?accurately the cash disbursements budget for the direct materials purchases for the third quarter using correct Excel formulas.
Calculate accurately what the total cost of goods sold (COGS) per unit should be for the company to generate 50% gross margin on its sales using correct Excel formulas. Consider the following question to guide your response:
Considering the direct material cost per unit already known, how much of this total COGS per unit is available to cover direct labor and manufacturing overhead (MOH) costs?
Prepare accurately the cash budget for the third quarter using correct Excel formulas.ACC 311 Project Two Scenario
You are the cost accountant at Posey’s Pet Emporium tasked with preparing quarterly budgets that
determine the cash effects of the company’s sales and production-related expenditures. The company
uses a calendar year, and it is time to prepare the third-quarter budget. You have the following
information:
1. The budgeted selling price for the year is $4.99 per unit. Sales volumes are budgeted as follows
for the last month of quarter two, for all of quarter three, and for part of quarter four.
June
33,500
July
32,000
August
35,200
September
35,000
October
34,000
2. Historically, 20% of Posey’s sales are cash sales. Of the remaining credit sales, 45% are collected
in the month of sale, while 52% are collected the following month. The remainder is deemed
uncollectible.
3. Management sets its ending finished goods inventory goal at 15% of the following month’s sales
volume. The accounting team expects this policy will be met at the beginning of the second
quarter.
4. The target ending inventory for Posey’s primary direct material is 20% of the following month’s
production needs. Each completed unit requires five pounds of direct materials at an expected
cost of $0.25 per pound.
5. Posey’s pays for 40% of its purchases in the month of purchase and 60% the month after
purchase. Total budgeted purchases in March are $20,000.
6. Posey’s ending cash balance on June 30 was $57,950.
7. Posey’s non-production cash disbursements are estimated at $80,000 per month.
Posey’s Pet Emporium
Budget Data
Budgeted selling price
[Insert value]
June
Sales Volume Budgets
[Insert value]
July
[Insert value]
August
[Insert value]
September
[Insert value]
October
[Insert value]
Cash sales
Credit collected in
Month of sale
Following month
Uncollectable
Percentage of Sales
[Insert value]
FG (finished goods) inventory goal
Target ending inventory DM (direct materials)
Pounds per completed unit of DM
Expected cost per pound
[Insert value]
[Insert value]
[Insert value]
[Insert value]
[Insert value]
[Insert value]
[Insert value]
Percentage of Purchase Payments
Month of purchase
[Insert value]
Month after purchase
[Insert value]
Budgeted purchases – October
[Insert value]
Other cash disbursements per month
[Insert value]
Beginning cash balance – July
[Insert value]
following
month’s sales
volume
Posey’s Pet Emporium
Sales Forecast
Budgeted sales
volume
× Budgeted selling
price
Budgeted sales
revenue
July
[Insert formula]
[Insert formula]
[Insert formula]
August
September
Quarter
[Insert formula] [Insert formula] [Insert formula]
[Insert
[Inseert
formula]
[insert formula] formula]
[insert
formula]
[Insert formula] [Insert formula]
Cash Receipts Budget
July
Beginning A/R (June
collections)
July cash sales
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
July credit sales
[Insert formula]
August cash sales
August credit sales
September cash sales
September credit
sales
Total cash receipts
[Insert formula]
August
[Insert
formula]
[Insert
formula]
[Insert
formula]
September
Quarter
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula] [Insert formula]
[Insert formula]
Posey’s Pet Emporium
Production Budget
Budgeted sales volume
Add: Target ending FG
inventory
Total units needed
Less: Beginning FG
inventory
Budgeted units to be
produced
July
[Insert formula]
August
[Insert formula
September
[Insert formula]
[Insert formula]
[insert formula]
[insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
DM Purchases Budget
Budgeted units to be
produced
July
August
September
[insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
× Pounds of DM per unit
Total production needs
(pounds)
Add:Target ending DM
inventory (pounds)
Total DM inventory
needs (pounds)
Less: Beginning DM
inventory (pounds)
Budgeted pounds of DM
to be purchased
× DM cost per pound
Total budgeted cost of
DM purchases
Quarter
[Insert formula]
[insert formula]
[Insert formula]
[Insert formula]
Quarter
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
Posey’s Pet Emporium
DM Cash
Disbursements
A/P balance from June*
July purchases
August purchases
September purchases
Total DM Cash
Disbursements
Other Cash
Disbursements
Total Cash
Disbursements
July
August
[Insert formula]
[Insert formula]
[Insert formula]
September
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
Quarter
[insert value}
[insert value}
[insert value}
[insert value}
[Insert formula]
[Insert formula]
[Insert formula]
Posey’s Pet Emporium
Selling price
Less: Target gross margin
percentage
Cost of goods sold
Less: DM cost per unit
Additional cost per unit to
cover DL and MOH
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
Posey’s Pet Emporium
Cash Budget
Beginning Cash Balance
Plus: Cash Receipts
Cash Sales
Cash Collections
Total Cash Receipts
Total Cash Available
Less: Cash Disbursements
Ending Cash Balance
July
[Insert formula]
August
[Insert formula]
September
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
Quarter
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
[Insert formula]
Purchase answer to see full
attachment
