Posted: February 26th, 2023

I need help with an account excel spread sheet

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.

<h2>Master Budget P9-57 00 0 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 Jan Feb Mar Quarter INCORRECT 0 INCORRECT 0 0 PRODUCTION BUDGET: INCORRECT 0 Jan Feb Mar Quarter INCORRECT 0 Budgeted unit sales INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 Jan Feb Mar Quarter Apr INCORRECT 0 TOTAL 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 Jan Feb Mar Quarter INCORRECT 0 INCORRECT 0 TOTAL 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 Jan Feb Mar Quarter INCORRECT 0 TOTAL 0 996 INCORRECT 0 INCORRECT 0 Jan Feb Mar Quarter INCORRECT 0 3 INCORRECT 0 TOTAL 0 Total cash payments INCORRECT 0 INCORRECT 0 INCORRECT 0 Jan Feb Mar Quarter INCORRECT 0 TOTAL 0 Total cash payments INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 Jan Feb Mar Quarter TOTAL 0 CASH BUDGET INCORRECT 0 INCORRECT 0 9000 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 INCORRECT 0 4873 INCORRECT 0 TOTAL 0 MANUFACTURING COST PER UNIT Charms: Hint: This is not a sum of the quarter. It is asking for the balance at the end of the quarter 4 INCORRECT 0 INCORRECT 0 INCORRECT 0 CORRECT 2 INCORRECT 0 TOTAL 2 INCOME STATEMENT INCORRECT 0 INCORRECT 0 INCORRECT 0 Operating Expenses 30100 INCORRECT 0 INCORRECT 0 INCORRECT 0 330 INCORRECT 0 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 INCORRECT 0 INCORRECT 0 TOTAL 0 GRAND TOTAL 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

Selling price per unit

E14

27,600

Total Sales

E15

56,000

E16

83,600

F14

29,700

SCHEDULE OF EXPECTED CASH COLLECTIONS:

F15

64,400

F16

94,100

Cash sales

G16

250,700

Credit sales

TOTAL

Total Cash Collections

PRODUCTION BUDGET:

D23

10,300

Apr

D24

2,000

D25

8,300

Add desired ending inventory

E23

11,675

Total needs

E24

2,300

Less beginning inventory

E25

9,375

Units to produce

F23

12,325

F24

2,475

DIRECT MATERIALS BUDGET:

F25

9,850

G25

27,525

Units to be produced

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

Less beginning inventory of DM

D35

16,815

Quantity of DM to purchase

D37

33,630

Multiply by: Cost per pound

E33

20,720

Total cost of DM purchases

E35

18,845

E37

37,690

F33

21,580

CASH PAYMENTS FOR DM PURCHASES:

F35

19,610

F37

39,220

Accounts payable

G37

110,540

Januray purchases

February purchases

March purchases

Total cash payments

CASH PAYMENT FOR DM PURCHASES

D46

49,126

E46

34,442

CASH PAYMENTS FOR DIRECT LABOR:

F46

37,

996

G46

121,564

Direct labor

CASH PAYMENT FOR DIRECT LABOR

D51

CASH PAYMENTS FOR MANUFACTURING OVERHEAD

E51

1125

F51

1182

Variable manufacturing overhead costs

G51

330

Rent (fixed)

Other MOH (fixed)

CASH PAYMENT FOR MOH

D60

17960

E60

19250

CASH PAYMENTS FOR OPERATING EXPENSES

F60

19820

G60

57030

Variable operating expenses

Fixed operating expenses

CASH PAYMENT FOR OPERATING EXPENSES

D67

9000

E67

10200

CASH BUDGET

F67

10900

FOR THE THREE MONTHS ENDING March 31

G67

30100

Cash balance, beginning

Add collections from customers

Total cash available

D75

77500

Less cash payments

D84

82082

DM purchases

D91

Direct labor

D93

4418

MOH costs

E75

88018

Operating Expenses

E84

87017

Tax Payment

E91

3000

Equipment purchases

E93

4001

Total Disbursements

F75

98101

F84

85898

Ending cash balance before financing

F91

-7330

Financing:

F93

4873

Borrowings

G75

255200

Repayments (enter as a neg. number)

G84

254997

Interest payments (enter as a neg. number)

G91

4670

Total financing

G93

Cash balance, ending

BUDGETED

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

D98

Direct materials cost per unit

D99

0.12

Direct labor cost per unit

D100

1.2

Variable manufacturing costs per unit

D101

0.8

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

Cost of manufacturing each unit

Martin Manufacturing

BUDGETED

INCOME STATEMENT

FOR THE THREE MONTHS ENDED March 31

Sales

F108

271000

Less COGS:

F109

165852

Gross profit

F110

105148

Less expenses:

F112

Depreciation

F113

4600

Operating Income

F114

70448

Interest Expense

F115

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: ENTER EXPENSES AS POSITVE NUMBERS

F116

21035

Net income

F117

49083

GRADING IS SHOWN TO THE RIGHT--->

GRAND TOTAL