Master Budget P9-57
Master Budget (P9-57A) GRADING:
Student Name: HIDE/LOCK
CASH COLLECTIONS BUDGET:
Cell Ref Co
ect answer Co
ect/Inco
ect Points Awarded
SALES BUDGET: D14 24,000 INCORRECT 0
Jan Feb Mar Quarter D15 49,000 INCORRECT 0
Budgeted unit sales D16 73,000 INCORRECT 0
Selling price per unit E14 27,600 INCORRECT 0
Total Sales E15 56,000 INCORRECT 0
E16 83,600 INCORRECT 0
F14 29,700 INCORRECT 0
SCHEDULE OF EXPECTED CASH COLLECTIONS: F15 64,400 INCORRECT 0
Jan Feb Mar Quarter F16 94,100 INCORRECT 0
Cash sales G16 250,700 INCORRECT 0
Credit sales TOTAL 0
Total Cash Collections
PRODUCTION BUDGET:
PRODUCTION BUDGET: D23 10,300 INCORRECT 0
Jan Feb Mar Quarter Apr D24 2,000 INCORRECT 0
Budgeted unit sales D25 8,300 INCORRECT 0
Add desired ending inventory E23 11,675 INCORRECT 0
Total needs E24 2,300 INCORRECT 0
Less beginning inventory E25 9,375 INCORRECT 0
Units to produce F23 12,325 INCORRECT 0
F24 2,475 INCORRECT 0
DIRECT MATERIALS BUDGET: F25 9,850 INCORRECT 0
Jan Feb Mar Quarter Apr G25 27,525 INCORRECT 0
Units to be produced TOTAL 0
Multiply by: Quantity of DM needed per unit
Quantity of DM needed for production
Plus: Desired ending inventory of DM DM BUDGET:
Total quantity of DM needed D33 18,475 INCORRECT 0
Less beginning inventory of DM D35 16,815 INCORRECT 0
Quantity of DM to purchase D37 33,630 INCORRECT 0
Multiply by: Cost per pound E33 20,720 INCORRECT 0
Total cost of DM purchases E35 18,845 INCORRECT 0
E37 37,690 INCORRECT 0
F33 21,580 INCORRECT 0
CASH PAYMENTS FOR DM PURCHASES: F35 19,610 INCORRECT 0
Jan Feb Mar Quarter F37 39,220 INCORRECT 0
Accounts payable G37 110,540 INCORRECT 0
Januray purchases TOTAL 0
Fe
uary purchases
March purchases
Total cash payments CASH PAYMENT FOR DM PURCHASES
D46 49,126 INCORRECT 0
E46 34,442 INCORRECT 0
CASH PAYMENTS FOR DIRECT LABOR: F46 37,996 INCORRECT 0
Jan Feb Mar Quarter G46 121,564 INCORRECT 0
Direct labor TOTAL 0
CASH PAYMENT FOR DIRECT LABOR
D51 996 INCORRECT 0
CASH PAYMENTS FOR MANUFACTURING OVERHEAD E51 1125 INCORRECT 0
Jan Feb Mar Quarter F51 1182 INCORRECT 0
Variable manufacturing overhead costs G51 3303 INCORRECT 0
Rent (fixed) TOTAL 0
Other MOH (fixed)
Total cash payments CASH PAYMENT FOR MOH
D60 17960 INCORRECT 0
E60 19250 INCORRECT 0
CASH PAYMENTS FOR OPERATING EXPENSES F60 19820 INCORRECT 0
Jan Feb Mar Quarter G60 57030 INCORRECT 0
Variable operating expenses TOTAL 0
Fixed operating expenses
Total cash payments CASH PAYMENT FOR OPERATING EXPENSES
D67 9000 INCORRECT 0
E67 10200 INCORRECT 0
CASH BUDGET F67 10900 INCORRECT 0
FOR THE THREE MONTHS ENDING March 31 G67 30100 INCORRECT 0
Jan Feb Mar Quarter TOTAL 0
Cash balance, beginning
Add collections from customers
Total cash available CASH BUDGET
D75 77500 INCORRECT 0
Less cash payments D84 82082 INCORRECT 0
DM purchases D91 9000 INCORRECT 0
Direct labor D93 4418 INCORRECT 0
MOH costs E75 88018 INCORRECT 0
Operating Expenses E84 87017 INCORRECT 0
Tax Payment E91 3000 INCORRECT 0
Equipment purchases E93 4001 INCORRECT 0
Total Disbursements F75 98101 INCORRECT 0
F84 85898 INCORRECT 0
Ending cash balance before financing F91 -7330 INCORRECT 0
Financing: F93 4873 INCORRECT 0
Bo
owings G75 255200 INCORRECT 0
Repayments (enter as a neg. number) G84 254997 INCORRECT 0
Interest payments (enter as a neg. number) G91 4670 INCORRECT 0
Total financing G93 4873 INCORRECT 0
TOTAL 0
Cash balance, ending
BUDGETED MANUFACTURING COST PER UNIT MANUFACTURING COST PER UNIT
Charms: Review the information given about DM, DL, and MOH
Charms: Hint: This is not a sum of the quarter. It is asking for the balance at the end of the quarte
Charms: Hint: This is not a sum of the quarter. It is asking for the balance at the end of the quarte
D98 4 INCORRECT 0
Direct materials cost per unit D99 0.12 INCORRECT 0
Direct labor cost per unit D100 1.2 INCORRECT 0
Variable manufacturing costs per unit D101 0.8 CORRECT 2
Fixed manufacturing overhead per unit 0.80
Charms: Amount given: based on $
8,000 fixed ovh per month divided by approx. 10,000 units per month
D102 6.12 INCORRECT 0
Cost of manufacturing each unit TOTAL 2
Martin Manufacturing
BUDGETED INCOME STATEMENT
FOR THE THREE MONTHS ENDED March 31
INCOME STATEMENT
Sales F108 271000 INCORRECT 0
Less COGS: F109 165852 INCORRECT 0
Gross profit F110 105148 INCORRECT 0
Less expenses:
Operating Expenses F112 30100 INCORRECT 0
Depreciation F113 4600 INCORRECT 0
Operating Income F114 70448 INCORRECT 0
Interest Expense F115 330 INCORRECT 0
Income tax expense
Charms: Round number to the nearest whole dollar. (You can show your work in the cell next to this one or below).
Charms: Hint: This is not a sum of the quarter. It is asking for the balance at the end of the quarte
Charms: Hint: This is not a sum of the quarter. It is asking for the balance at the end of the quarte
Charms: ENTER EXPENSES AS POSITVE NUMBERS
F116 21035 INCORRECT 0
Net income F117 49083 INCORRECT 0
TOTAL 0
GRADING IS SHOWN TO THE RIGHT---> GRAND TOTAL GRAND TOTAL 2
Assignment #2– Master Budget Project
Due Sunday Week 5
100 Points –Excel Worksheet
1. Complete problem P9-63A from the textbook using the attached template. The
template must be used to complete this project (no points will be given if
submitted in a different format or file).
2. You must show your work. This is done by inputting formulas/calculations directly
into the cells where necessary. If formulas/calculations are not used, points may
e deducted (a max of 10 points) at the discretion of the instructor.
3. The blue shaded areas are for data input.
4. Look out for a red triangle in the upper right-hand corner of a cell. If you hover
over the cell you will see a hint or instructions for that cell.
5. To the right of the budget schedules is the grading area that is being calculated
as you input information. Please use this area to see where any mistakes are
eing made.
6. The template begins with the Sales Budget. This is not a required schedule but is
included as you will need to reference this information to complete other
schedules.
7. Please make sure to put your name at the top of the sheet AND also save the file
with your name before uploading.
8. With the exception of the Budgeted Manufacturing Cost per Unit schedule, round
all numbers to the nearest whole dollar.
9. Helpful Resources
a. The textbook and PowerPoint slides for Chapter 9 include detailed
examples of how to complete the required schedules
. In My Accounting Lab, on the left side menu, click ‘Multimedia Li
ary.’
Select ‘Chapter 9’, click ‘Select All’, and click ‘Find Now.’ There is a Try it
in Excel video that you may find useful
c. You can search Youtube for videos explaining how to input
formulas/calculations into Excel
*If you have any question about completing this project, please email your instructor.