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