Question: Unit Activity Unit: Essential Skills for Accounting Professionals This activity will help you meet these educational goals: Content KnowledgeYou will use a spreadsheet to maintain
Unit Activity
Unit: Essential Skills for Accounting Professionals
This activity will help you meet these educational goals:
Content KnowledgeYou will use a spreadsheet to maintain financial records.
InquiryYou will conduct online research in which you will collect information and communicate your results in written form.
21st Century SkillsYou will use critical thinking and problem-solving skills, and communicate effectively.
Introduction
In this Unit Activity, you will demonstrate how to use a spreadsheet to maintain financial records.
__________________________________________________________________________
Directions and Analysis
Task 1: Analyzing Data
Following is a set of data for a small company. Using this data, analyze the expenses.
Using the functions in the spreadsheet, calculate the monthly average for each expense for the year.
Using the calculations, calculate the percentage of each expenses to the total sales.
Jan-14
Feb-14
Mar-14
Apr-14
May-14
Jun-14
Jul-14
Aug-14
Sep-14
Oct-14
Nov-14
Dec-14
Sales Revenue
Actual
Actual
Actual
Actual
Actual
Actual
Actual
Actual
Actual
Actual
Actual
Actual
Product 1
48,267
50,680
52,708
55,343
57,003
56,433
56,456
57,020
57,305
57,649
57,995
58,343
Product 2
10,214
10,725
11,154
11,711
12,063
11,942
12,420
12,792
13,432
13,513
13,594
13,675
Product 3
5,845
6,137
6,383
6,386
6,388
6,324
6,577
6,774
6,778
6,782
6,788
6,829
Total Sales
64,326
67,542
$70,244
$73,440
$75,454
$74,699
$75,452
$76,587
$77,515
$77,944
$78,377
$78,848
Direct Expenses
Inventory
20,584
20,263
24,585
23,501
24,145
22,410
24,145
28,337
21,704
24,942
25,081
25,231
Labor
12,865
13,508
10,537
14,688
13,582
18,675
15,090
19,913
15,503
16,368
15,675
15,770
Supplies
3,216
4,053
3,512
3,672
6,036
3,735
5,282
2,298
3,101
3,897
3,919
3,942
Total Cost of Goods
36,666
37,824
38,634
41,861
43,763
44,820
44,517
50,547
40,308
45,207
44,675
44,943
Operating Expenses
Salaries
12,879
13,179
12,879
12,579
12,882
12,780
12,845
12,250
12,950
12,963
12,850
12,100
Advertising
400
-
-
500
-
-
410
1,150
1,250
1,200
1,000
Utilities
780
841
740
685
710
795
910
1,180
1,020
650
710
745
Insurance
1,522
1,522
1,522
1,522
1,522
1,522
1,522
1,522
1,522
1,522
1,522
1,522
Repairs
160
40
741
800
280
100
Total Operating Expenses
15,741
15,542
15,141
15,326
15,114
15,838
16,077
15,362
16,642
16,385
16,562
15,467
EBIT
$27,660
$29,719
$31,610
$31,579
$31,691
$29,880
$30,936
$26,040
$37,207
$32,736
$33,702
$33,904
Income Taxes
9,681
10,402
11,063
11,053
11,092
10,458
10,827
9,114
13,023
11,458
11,796
11,867
Interest Expense
1,252
1,252
1,252
1,252
1,252
1,252
1,252
1,252
1,252
1,252
1,252
1,252
Net Income
$16,727
$18,065
$19,294
$19,275
$19,347
$18,170
$18,856
$15,674
$22,933
$20,027
$20,654
$20,786
Type your response here:
Task 2: Preparing a Budget
Using the data above, determine which expenses relate to sales and which ones do not. Use this knowledge to do the following: in 400 words, explain how you might use this information to budget expenses for the next year.
Type your response here:
____________________________________________________________
Evaluation
Your teacher will use this rubric to evaluate the completeness of your work as well as the clarity of thinking that you exhibit.
Total Points: 100
Task 1: Analyzing Data
Task Points: [60]
- calculation of average expenses:
- calculation of percentages
[30]
[30]
Task 2: Preparing a Budget
Task Points: [40]
- explanation of budget
[40]
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help