CIS 2200 INTRODUCTION TO INFORMATION SYSTEMS AND TECHNOLOGIES
Excel Group Project
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
Wedding Invitations
Birthday Party Invitations
Business Party Invitations
Total Sales Revenue
EXPENSES
Printing Costs
Salaries
Administration
Advertising
Legal Fees
Shipping
Total Expenses
Net Income before Taxes
August
4,507
3,841
2,652
11,000
4,400
3,500
550
1,544
788
928
11,710
-710
Taxes
Net Profit (loss)
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
# A
B
C
D
E
F
G
1 5.44 8.42 3.06 4.19 4.41
6.96 20.54
2 5.03 7.15 4.73 6.60 2.89 5.96 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
4.51
Birthday Party Invitations
8.65
Business Party Invitations
4.11
Advertising
4.99
Legal Fees
2.80
Shipping
December Increase Goal
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.
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. There are several parts to this project:
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.
16,000
14,000
Trends in total Sales, Revenues, Expenses, and Net Profits
13,895
12.641
12,331
19,050
11,710
11,000
12,000
10,000
8,000
6,000
4,000
2,000
TOTAL ($) IN THOUSANDS
0
-2,000
August
September
Plot Area
October
MONTH
TOTAL SALES REVENUES
-TOTAL EXPENSES
■Net Profit (loss)
November
December 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:
DISTRIBUTION OF EXPENSES IN THOUSANDS IN DECEMBER 2019
Legal Fees
$880
6%
Advertising
$1,876
14%
Administration
$692
5%
Shipping
$1,205
9%
Salaries
$3,500
26%
Printing Costs
$5,538
40%
Chart Area
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.
Paste
Paste Values
园
L123
Other Paste Options
Paste Special...
Your worksheet "SOLVER" should look like: A
B
Table 1: Financial results for August 2019 (000)
D
E
F
G
REVENUE
August
September October November December
1
Wedding Invitations
4,507
4,704
4,910
5,126
5,350
2
Birthday Party Invitations
3,841
4,206
4,606
5,044
5,524
3
Business Party Invitations
2,652
2,780
2,913
3,053
3,200
4
TOTAL SALES REVENUES
11,000
11,690
12,430
13,223
14,074
5
EXPENSES
6
Printing Costs
4,400
4,676
4,972
5,289
5,630
7
Salaries
3,500
3,500
3,500
3,500
3,500
8
Administration
550
585
622
661
704
Advertising
1,544
1,609
1,677
1,747
1,821
10
Legal Fees
788
826
865
907
950
11
Shipping
928
978
1,030
1,085
1,143
12 TOTAL EXPENSES
11,710
12,173
12,665
13,190
13,748
13 Net Income before Taxes
-710
-483
-235
34
327
14
Taxes
0
0
0
9
91
14 Net Profit (loss)
-710
-483
-235
24
235
#
PROJECTED GROWTH PER MO
%
2 Wedding Invitations
4.38
3 Birthday Party Invitations
9.51
4 Business Party Invitations
4.81
5 Advertising
6| Legal Fees
4.21
4.79
SOLVER
GOAL
7 Shipping
8 December Increase Goal
5.35
19.85
281.93
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.
