Week 5, Accounting

User Generated

xnebaryyl18

Business Finance

Description

Hello,

Please answer the attached Excel Spreadsheets then save and upload them with the completed answers.

After saving the Excel Spreadsheet please refer to the word document for instructions to complete the word documents. You will have to utilized the attached spreadsheets to answer the questions from the word documents.

Please let me know if you have any questions, and thanks in advance.

Unformatted Attachment Preview

20170417132606copy_of_question_2.xlsx Chapter 7: Applying Excel Data Manufacturing overhead Selling and administrative overhead Manufacturing overhead Selling and administrative overhead Total activity OfficeMart orders: Customers Orders Number of filing cabinets ordered in total Selling price Direct materials Direct labor $500.000 $300.000 Assembling Processing Supporting Units Orders Customers 50% 35% 5% 10% 45% 25% 1.000 250 100 units orders customers Other 10% 20% 1 customer 4 orders 80 units $595 $180 $50 Enter a formula into each of the cells marked with a ? below Review Problem: Activity-Based Costing Perform the first stage allocations Manufacturing overhead Selling and administrative overhead Total cost Compute the activity rates Activity Cost Pools Assembling units Processing orders Supporting customers Assembling Units ? ? ? Total Cost ? ? ? Processing Orders ? ? ? Supporting Customers ? ? ? Total Activity ? units ? orders ? customers Compute the overhead cost attributable to the OfficeMart orders Activity Cost Pools Activity Rate Assembling units ? per unit Processing orders ? per order Supporting customers ? per customer Other Determine the product margin for the OfficeMart orders under a traditional cost system Manufacturing overhead ? Total activity ? units Page Manufacturing overhead per unit ? per1unit ? ? ? Activity Rate ? per unit ? per order ? per customer Activity ? units ? orders ? customer Determine the customer margin for the OfficeMart orders under Activity-Based Costing Sales ? Costs: Direct materials ? Direct labor ? Unit-related overhead ? Order-related overhead ? Customer-related overhead ? ? Customer margin ? Total ? ? ? ABC Cost ? ? ? 20170417132606copy_of_question_2.xlsx Sales Costs: Direct materials Direct labor Manufacturing overhead Traditional costing product margin ? ? ? ? Page 2 ? ? S &P Enterprises needs a cash budget for March. The following information is available Data Actual January and February and expected March sales: Cash sales Sales on account Total sales January Accounts Receivable Collections: Month of sale Month following sale Second month following sale Uncollectible $1.600 25.000 26.600 15% 60% 22% 3% Accounts payable for inventory purchases, March 1 balance Budgeted inventory purchase in March Inventory payments: Month of purchase Month following purchase $10.500 $23.500 Total budgeted selling & administrative expenses in March Budgeted selling & administrative depreciation in March $12.500 $3.200 Other budgeted cash disbursements in March Equipment purchases Dividends to be paid $14.000 $2.000 Minimum cash balance to be maintained March 1 cash balance March 1 outstanding borrowings March 1 interest due $10.000 $11.500 $0 $0 60% 40% The company has a line of credit available to bolster the cash balance as needed. When preparing budget, the company maintains their data on a separate sheet from the actual budget and schedules. Click the Schedules and Cash Budget tab to prepare the following: 1.Schedule of expected cash collections for March. 2.Schedule of expected cash disbursement for inventory purchase for March 3.Cash budget for March. Indicate in the financing section any borrowing that will be needed in March. Assume that any inter ng information is available February March $3.750 $5.100 30.000 40.000 33.750 45.100 m the actual budget and schedules. be needed in March. Assume that any interest will not be paid until the following month. Expected cash collections: March cash collections March collections on account: January sales February sales March sales Total cash collections Payments to suppliers: Accounts payable for inventory purchases, beginning balance March purchases Total cash payments S & P Enterprises Cash Budget For the Month of March Cash balance, March 1 Add cash receipts: Collections from customers Total cash available before current financing Less disbursements: Payment to suppliers Selling and administrative expenses Equipment purchases Dividends paid Total disbursements Excess (deficiency) of cash available over disbursements Financing: Borrowings Repayments Interest Total financing Cash balance, March 31 20170417132630question__5.xlsx Chapter 8: Applying Excel Data Budgeted unit sales • Selling price per unit • Accounts receivable, beginning balance • Sales collected in the quarter sales are made • Sales collected in the quarter after sales are made • Desired ending finished goods inventory is • Finished goods inventory, beginning • Raw materials required to produce one unit • Desired ending inventory of raw materials is • Raw materials inventory, beginning • Raw material costs • Raw materials purchases are paid and • Accounts payable for raw materials, beginning balance 1 40.000 $8 $65.000 75% 25% 30% 12.000 5 10% 23.000 $0,80 60% 40% $81.500 Year 2 Quarter 2 3 60.000 100.000 4 50.000 per unit of the budgeted unit sales of the next quarter units pounds of the next quarter's production needs pounds per pound in the quarter the purchases are made in the quarter following purchase Enter a formula into each of the cells marked with a ? below Review Problem: Budget Schedules Construct the sales budget 1 Budgeted unit sales Selling price per unit Total sales ? ? ? Construct the schedule of expected cash collections ? ? ? Construct the production budget ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Year 2 Quarter 2 3 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 1 Page 1 ? ? ? ? ? ? ? ? Construct the raw materials purchases budget Required production (units) Raw materials required to produce one unit Production needs (pounds) Add desired ending inventory of raw materials (pounds) Total needs (pounds) Less beginning inventory of raw materials (pounds) Raw materials to be purchased Cost of raw materials per pound Cost of raw materials to be purchased ? ? ? ? ? 4 Year 2 Quarter 2 3 ? ? ? ? ? 1 Budgeted unit sales Add desired finished goods inventory Total needs Less beginning inventory Required production 4 ? ? ? Year 2 Quarter 2 3 1 Accounts receivable, beginning balance First-quarter sales Second-quarter sales Third-quarter sales Fourth-quarter sales Total cash collections Year 2 Quarter 2 3 ? ? ? 4 ? ? ? ? ? 4 ? ? ? ? ? ? ? ? ? 20170417132630question__5.xlsx Construct the schedule of expected cash payments Year 2 Quarter 2 3 1 Accounts payable, beginning balance First-quarter purchases Second-quarter purchases Third-quarter purchases Fourth-quarter purchases Total cash disbursements ? ? ? Page 2 ? ? ? 4 ? ? ? ? ? ? 20170417132630question__5.xlsx Year 3 Quarter 1 2 70.000 80.000 t quarter Year 3 Quarter 1 2 ? ? ? ? ? ? Year ? ? ? ? ? ? Year ? ? ? ? ? Year Year 3 Quarter 1 2 ? ? ? ? ? Year 3 Quarter 1 ? ? ? ? ? ? ? ? ? ? ? ? ? Page 3 20170417132630question__5.xlsx Year ? ? ? ? ? ? Page 4
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached.

20170417234307chap_7.xlsx
Chapter 7: Applying Excel
Data
Manufacturing overhead
Selling and administrative overhead

Manufacturing overhead
Selling and administrative overhead
Total activity

OfficeMart orders:
Customers
Orders
Number of filing cabinets ordered in total
Selling price
Direct materials
Direct labor

$500.000
$300.000
Assembling Processing Supporting
Units
Orders
Customers
50%
35%
5%
10%
45%
25%
1.000
250
100
units
orders customers

Other
10%
20%
1.350

1 customer
4 orders
80 units
$595
$180
$50

Enter a formula into each of the cells marked with a ? below
Review Problem: Activity-Based Costing
Perform the first stage allocations

Manufacturing overhead
Selling and administrative overhead
Total cost
Compute the activity rates
Activity Cost Pools
Assembling units
Processing orders
Supporting customers

Assembling
Units
$ 250.000
30.000
$ 280.000

Total Cost
$280.000
$310.000
$100.000

Processing
Orders
$ 175.000
135.000
$ 310.000
$ 310.000

Supporting
Customers
$ 25.000
75.000
$ 100.000

Total Activity
1.000 units
250 orders
100 customers

Compute the overhead cost attributable to the OfficeMart orders
Activity Cost Pools
Activity Rate
Assembling units
$280 per unit
Processing orders
$1.240 per order
Supporting customers
$1.000 per customer

Other
$ 50.000
60.000
$ 110.000

Activity Rate
$280 per unit
$1.240 per order
$1.000 per customer

Activity
80 units
4 orders
1 customer

Determine the customer margin for the OfficeMart orders under Activity-Based Costing
Sales
$
47.600
Costs:
Direct materials
$
14.400
Direct labor
4.000
Unit-related overhead
$22.400
Order-related overhead
$4.960
Customer-related overhead
$1.000
46.760
Customer margin
$
840
Determine the product margin for the OfficeMart orders under a traditional cost system
Manufacturing overhead
$500.000
Total activity
1.350 units
Manufacturing overhead per unit
$370,37Page
per1unit

Total
500.000
300.000
$ 800.000
$

ABC Cost
$22.400
$4.960
$1.000

20170417234307chap_7.xlsx
Sales
Costs:
Direct materials
Direct labor
Manufacturing overhead
Traditional costing product margin

$

$

47.600

$

48.030
(430)

14.400
4.000
29.630

Page 2


20170417234328chapter_8.xlsx
Chapter 8: Applying Excel
Data

Year 2 Quarter
2
3
60.000
100.000

1
40.000

Budgeted unit sales
• Selling price per unit
• Accounts receivable, beginning balance
• Sales collected in the quarter sales are made
• Sales collected in the quarter after sales are made
• Desired ending finished goods inventory is
• Finished goods inventory, beginning
• Raw materials required to produce one unit
• Desired ending inventory of raw materials is
• Raw materials inventory, beginning
• Raw material costs
• Raw materials purchases are paid
and
• Accounts payable for raw materials, beginning balance

$...


Anonymous
Excellent! Definitely coming back for more study materials.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags