can someone help me with how to make an excel spreadsheet for an accounting class? using the provided template
Assignment #2– Master Budget Project
Due Sunday Week 6
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
be 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
being 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
b. In My Accounting Lab, on the left side menu, click ‘Multimedia Library.’
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.
>Master Budget P9-57
00
0 0 INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT Jan INCORRECT INCORRECT 0 INCORRECT Jan INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT Jan INCORRECT TOTAL INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT Jan INCORRECT INCORRECT TOTAL INCORRECT INCORRECT INCORRECT Jan INCORRECT TOTAL 996 INCORRECT Jan INCORRECT 3
INCORRECT TOTAL INCORRECT INCORRECT INCORRECT Jan INCORRECT TOTAL INCORRECT INCORRECT INCORRECT INCORRECT Jan TOTAL CASH BUDGET INCORRECT INCORRECT 9000 INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT 4873 TOTAL 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 quarter
Charms: Hint: This is not a sum of the quarter. It is asking for the balance at the end of the quarter 4 INCORRECT INCORRECT CORRECT Charms: Amount given: based on $ INCORRECT TOTAL INCOME STATEMENT INCORRECT INCORRECT INCORRECT 30100 INCORRECT INCORRECT 330 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 quarter Charms: Hint: This is not a sum of the quarter. It is asking for the balance at the end of the quarter Charms: ENTER EXPENSES AS POSITVE NUMBERS INCORRECT INCORRECT TOTAL GRAND TOTAL 2
2
Master Budget (P9-63A)
GRADING:
Student Name:
HIDE/LOCK
CASH COLLECTIONS BUDGET:
Cell Ref
Correct answer
Correct/Incorrect
Points Awarded
SALES BUDGET:
D1
4
24,
0
IN
CORRECT
Jan
Feb
Mar
Quarter
D15
49,000
INCORRECT
Budgeted unit sales
D16
73,000
0
Selling price per unit
E14
27,600
0
Total Sales
E15
56,000
0
E16
83,600
0
F14
29,700
0
SCHEDULE OF EXPECTED CASH COLLECTIONS:
F15
64,400
0
Feb
Mar
Quarter F16
94,100
0
Cash sales
G16
250,700
0
Credit sales
TOTAL
Total Cash Collections
PRODUCTION BUDGET:
PRODUCTION BUDGET:
D23
10,300
0
Feb
Mar
Quarter
Apr
D24
2,000
0
Budgeted unit sales
D25
8,300
0
Add desired ending inventory
E23
11,675
0
Total needs
E24
2,300
0
Less beginning inventory
E25
9,375
0
Units to produce
F23
12,325
0
F24
2,475
0
DIRECT MATERIALS BUDGET:
F25
9,850
0
Feb
Mar
Quarter
Apr G25
27,525
0
Units to be produced
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
0
Less beginning inventory of DM
D35
16,815
0
Quantity of DM to purchase
D37
33,630
0
Multiply by: Cost per pound
E33
20,720
0
Total cost of DM purchases
E35
18,845
0
E37
37,690
0
F33
21,580
0
CASH PAYMENTS FOR DM PURCHASES:
F35
19,610
0
Feb
Mar
Quarter F37
39,220
0
Accounts payable
G37
110,540
0
Januray purchases
0
February purchases
March purchases
Total cash payments
CASH PAYMENT FOR DM PURCHASES
D46
49,126
0
E46
34,442
0
CASH PAYMENTS FOR DIRECT LABOR:
F46
37,
996
0
Feb
Mar
Quarter G46
121,564
0
Direct labor
0
CASH PAYMENT FOR DIRECT LABOR
D51
INCORRECT
0
CASH PAYMENTS FOR MANUFACTURING OVERHEAD
E51
1125
0
Feb
Mar
Quarter F51
1182
0
Variable manufacturing overhead costs
G51
330
0
Rent (fixed)
0
Other MOH (fixed)
Total cash payments
CASH PAYMENT FOR MOH
D60
17960
0
E60
19250
0
CASH PAYMENTS FOR OPERATING EXPENSES
F60
19820
0
Feb
Mar
Quarter G60
57030
0
Variable operating expenses
0
Fixed operating expenses
Total cash payments
CASH PAYMENT FOR OPERATING EXPENSES
D67
9000
0
E67
10200
0
CASH BUDGET
F67
10900
0
FOR THE THREE MONTHS ENDING March 31
G67
30100
0
Feb
Mar
Quarter
0
Cash balance, beginning
Add collections from customers
Total cash available
D75
77500
0
Less cash payments
D84
82082
0
DM purchases
D91
INCORRECT
0
Direct labor
D93
4418
0
MOH costs
E75
88018
0
Operating Expenses
E84
87017
0
Tax Payment
E91
3000
0
Equipment purchases
E93
4001
0
Total Disbursements
F75
98101
0
F84
85898
0
Ending cash balance before financing
F91
-7330
0
Financing:
F93
4873
0
Borrowings
G75
255200
0
Repayments (enter as a neg. number)
G84
254997
0
Interest payments (enter as a neg. number)
G91
4670
0
Total financing
G93
INCORRECT
0
0
Cash balance, ending
BUDGETED
MANUFACTURING COST PER UNIT
D98
INCORRECT
0
Direct materials cost per unit
D99
0.12
0
Direct labor cost per unit
D100
1.2
0
Variable manufacturing costs per unit
D101
0.8
2
Fixed manufacturing overhead per unit
0.80
8,000 fixed ovh per month divided by approx. 10,000 units per month
D102
6.12
0
Cost of manufacturing each unit
2
Martin Manufacturing
BUDGETED
INCOME STATEMENT
FOR THE THREE MONTHS ENDED March 31
Sales
F108
271000
0
Less COGS:
F109
165852
0
Gross profit
F110
105148
0
Less expenses:
Operating Expenses
F112
INCORRECT
0
Depreciation
F113
4600
0
Operating Income
F114
70448
0
Interest Expense
F115
INCORRECT
0
Income tax expense
F116
21035
0
Net income
F117
49083
0
0
GRADING IS SHOWN TO THE RIGHT—>
GRAND TOTAL
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
