Description
Unformatted Attachment Preview
Group
Wedding
Invitations
Birthday Party
Invitations
Business Party
Invitations
Advertising
Legal Fees
Shipping
Deceember
Goal Increase
#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
A
4.62
5.59
3.83
4.72
4.65
5.73
4.30
5.24
4.51
4.56
4.22
5.73
5.83
4.18
5.03
4.99
3.27
5.85
3.51
5.44
4.77
4.71
4.22
3.74
4.17
5.66
4.38
4.98
5.60
4.77
5.24
3.55
5.28
4.69
3.27
B
9.06
8.82
9.82
9.28
7.31
8.27
9.38
7.92
8.65
8.21
8.21
8.65
8.44
7.69
7.15
9.55
7.31
8.34
9.06
8.42
7.57
7.88
9.22
8.22
9.37
7.61
9.51
7.19
7.53
8.44
7.88
8.33
9.99
9.42
7.19
C
3.53
5.89
5.93
4.94
3.54
3.69
4.56
5.55
4.11
3.69
4.56
4.37
4.77
3.27
4.73
3.85
5.63
5.47
4.86
3.06
5.90
5.08
3.47
5.89
4.21
5.40
4.81
3.30
5.63
3.85
5.89
4.75
3.06
4.34
4.37
D
5.78
6.62
6.25
6.05
5.64
4.36
6.47
5.68
4.99
5.47
4.99
4.29
5.89
5.59
6.60
6.88
4.68
6.36
4.41
4.19
5.65
4.68
5.54
4.20
6.74
4.29
4.21
5.47
4.76
5.47
5.89
5.92
4.17
4.37
4.43
E
4.47
2.66
3.98
4.78
2.33
2.46
3.45
4.64
2.80
3.44
3.21
3.98
3.12
2.67
2.89
2.08
3.12
3.49
3.47
4.41
3.40
2.15
4.09
3.68
3.62
3.21
4.79
3.92
3.06
2.80
3.44
2.81
4.31
2.51
2.16
F
4.82
4.92
5.94
4.75
4.15
6.51
5.34
5.26
6.74
4.50
4.01
5.34
5.80
4.28
5.96
4.01
5.74
5.88
5.74
6.96
4.24
6.86
4.17
6.66
6.66
6.54
5.35
4.37
4.22
6.66
4.22
5.21
4.39
5.60
5.12
G
19.55
20.04
18.34
20.72
18.58
18.82
19.54
18.34
20.08
19.53
20.42
19.21
20.16
20.96
20.20
20.86
18.22
18.57
20.88
20.54
19.31
19.16
20.08
19.93
20.92
18.36
19.85
18.79
19.67
18.47
19.12
18.92
19.75
19.28
18.27
Order
Date
01/06/20
01/23/20
02/09/20
02/26/20
03/15/20
04/01/20
04/18/20
05/05/20
05/22/20
06/08/20
06/25/20
07/12/20
07/29/20
08/15/20
09/01/20
09/18/20
10/05/20
10/22/20
11/08/20
11/25/20
12/12/20
12/29/20
01/15/21
02/01/21
02/18/21
03/07/21
03/24/21
04/10/21
04/27/21
05/14/21
05/31/21
06/17/21
07/04/21
07/21/21
08/07/21
08/24/21
09/10/21
09/27/21
10/14/21
10/31/21
11/17/21
12/04/21
Region
East
Central
Central
Central
West
East
Central
Central
West
East
Central
East
East
East
Central
East
Central
East
East
Central
Central
East
Central
Central
East
West
Central
Central
East
Central
Central
Central
East
Central
Central
West
Central
West
West
Central
Central
Central
Representative
Jones
Kivell
Jardine
Gill
Sorvino
Jones
Andrews
Jardine
Thompson
Jones
Morgan
Howard
Parent
Jones
Smith
Jones
Morgan
Jones
Parent
Kivell
Smith
Parent
Gill
Smith
Jones
Sorvino
Jardine
Andrews
Howard
Gill
Gill
Kivell
Jones
Morgan
Kivell
Sorvino
Gill
Sorvino
Thompson
Andrews
Jardine
Jardine
Invitation
type
Birthday
Business
Birthday
Wedding
Birthday
Business
Birthday
Birthday
Birthday
Business
Birthday
Business
Business
Birthday
Wedding
Graduation
Business
Wedding
Wedding
Graduation
Birthday
Graduation
Business
Business
Business
Business
Graduation
Birthday
Wedding
Birthday
Business
Wedding
Graduation
Graduation
Graduation
Wedding
Birthday
Wedding
Business
Birthday
Business
Business
Units
(100)
95
50
36
27
56
60
75
90
32
60
90
29
81
35
2
16
28
64
15
96
67
74
46
87
4
7
50
66
96
53
80
5
62
55
42
3
7
76
57
14
11
94
Unit
Cost
1.99
3.49
4.99
1.99
2.99
4.99
1.99
4.99
1.99
0.85
4.99
1.99
1.14
2.27
2.5
1.57
3.14
1.35
0.97
2.32
1.29
3.17
8.99
15
4.99
19.99
4.99
1.99
4.99
1.29
1.04
1.5
1.87
2.49
2.16
2.48
1.29
1.99
1.72
1.29
0.58
1.99
Total
18,905
17,450
17,964
5,373
16,744
29,940
14,925
44,910
6,368
5,100
44,910
5,771
9,234
7,945
500
2,512
8,792
8,640
1,455
22,272
8,643
23,458
41,354
130,500
1,996
13,993
24,950
13,134
47,904
6,837
8,320
750
11,594
13,695
9,072
744
903
15,124
9,804
1,806
638
18,706
12/21/21 Central
Andrews
Business
28
1.21
3,388
Introduction
“NYP,” New York Paper Co., is using its financial results for August 2019 (Table 1) as a base for
projecting the company’s budget for the remaining four months of the year (September-December).
Table 1: Financial results for August 2019 (000)
REVENUE
August
Wedding Invitations
4,507
Birthday Party Invitations
3,841
Business Party Invitations
2,652
Total Sales Revenue
11,000
EXPENSES
Printing Costs
Salaries
Administration
Advertising
Legal Fees
Shipping
Total Expenses
Net Income before Taxes
Taxes
Net Profit (loss)
4,400
3,500
550
1,544
788
928
11,710
-710
0
-710
Enter the figures for August of 2019 in your worksheet “BUDGET” in the workbook “GROUP ##
EXCEL GROUP PROJECT SPRING 24”. Make sure that you use formulas where appropriate
for this base month. All figures should be expressed in thousands of dollars (000), and amounts
should be formatted with commas and no decimals.
Assumptions/Assignments
Following are the assumptions/assignments about how expenses relate to revenues and the
growth assumptions for the next four months. These assumptions/assignments are unique for each
group. Using wrong assumptions/assignments will cause a zero 0 grade for the project.
Table 2 below presents a partial example of the assignments. The complete table is found in the
worksheet “GROUP ASSIGNMENTS” in the workbook “GROUP ## EXCEL GROUP PROJECT
SPRING 24”.
Table 2 Group Assignments
Group
Wedding Invitations
Birthday Party
Invitations
Business Party
Invitations
Advertising
Legal Fees
Shipping
December
Goal Increase
#
1
2
A
5.44
5.03
B
8.42
7.15
C
3.06
4.73
D
4.19
6.60
E
4.41
2.89
F
6.96
5.96
G
20.54
20.20
Projected Growth Per Month1
Use VLOOKUP to copy the group’s individual assignments/growth assumptions to your worksheet.
Place them all together in the lower left corner of your “BUDGET” worksheet with a boxed outline.
Table 3 Individual Assignments
Projected Growth Per Month
Wedding Invitations
Birthday Party Invitations
Business Party Invitations
Advertising
Legal Fees
Shipping
December Increase Goal
%
4.51
8.65
4.11
4.99
2.80
6.74
20.08
1. Sales revenue from wedding invitations, birthday party invitations, and business party
invitations will grow at A%, B%, and C% per month, respectively.
2. Printing costs are calculated as 40% of the Sales Revenue for the month, and Administration
expenses are calculated as 5% of the Sales Revenue for the month.
3. Salaries are fixed for the period. Please create an assumption table.
4. The other expense items will grow at the following rates (per month): Advertising will grow at
D% per month, Legal Fees will grow at E% per month, and Shipping will grow at F% per month.
5. The tax rate is 28% on profits for the month. Assume that taxes are calculated and paid each
month. Note that the company does not pay taxes when it loses money.
6. Conditional Formatting. If any of the numbers for Net Income before Taxes, Taxes, and
Net Profit (loss) is greater than 0, then the font for that cell should be green. If any of
the numbers for Net Income before Taxes, Taxes, and Net Profit (loss) is less than 0, then
the font for that cell should be red; otherwise, the font for that cell should be orange.
There are several parts to this project:
1
For example: If the August expense was $100 and the projected growth is 5% per month than the projected September expense
will be $100 x 1.05 =$105; projected October expense will be $105 x 1.05 =$110.25; projected November expense will be $110.25
x 1.05 =$115.76; and December expense will be $115.76 x 1.05=121.55.
A. Forecast the budget for the next four months of 2019 (Sept-Dec) in a worksheet “BUDGET” in
the workbook “GROUP ## EXCEL GROUP PROJECT SPRING 24”. Include a footer on this sheet
with the names of all group members.
B. Create a trend chart showing the trend over the entire period in total sales revenue, total
expenses and net profits. Be sure to select the right graph type and label the chart in order to
indicate that you are displaying the results in thousands of dollars. Save the chart in a worksheet
“BUDGET” in the workbook “EXCEL GROUP PROJECT SPRING 24”. The chart should not be
“embedded” (which looks copied with gridlines in the background), but on a new sheet clearly,
large and presentable with all proper labels. Similar to the one below.
C. Create a pie chart showing the proportional distribution of expenses in December. Be sure to
label the chart well. Save the chart in a worksheet “DISTRIBUTION” in the workbook “GROUP ##
EXCEL GROUP PROJECT SPRING 24”. The chart should not be “embedded” (which looks copied
with gridlines in the background), but on a new sheet clearly, large, and presentable with all proper
labels – similar to the one below:
D. After you finished the budget calculations copy the entire worksheet “BUDGET” to a new
worksheet “SOLVER”. To copy OPEN the PASTE option. When it opens up click the icon in the top
row, second from the left.
Your worksheet “SOLVER” should look like:
Use the NET Profit value for December (cell G20) and calculate your objective value for improved
December result. The increase % for your group is in column G (see Table 2 Group Assignment
above). Store the desired value in cell D30.
Use Excel Solver to manipulate projected growth per month (cells C24:C29) to achieve the
December goal calculated and stored in cell D30.
Save the Solver solution.
D. Use the data in the “SALES” worksheet to create a PIVOT TABLE. The table must show the
amount of sales and the number of units for each representative, for each year (just the year-no
month, no day), for each invitation type. Your pivot table should look approximately like the table
below. Name the resulting worksheet “PIVOT TABLE”.
Purchase answer to see full
attachment
