Computer Information System

1

IS 312: Fall 2020 Semester Excel Project Specifications

Due Date specified in Canvas Module Assignment

Use the Excel file “IS312_ExcelProject_F20.xls” in this package. The workbook has 5 worksheets. Do the following specified tasks for these worksheets. I.“PAYROLL” WORKSHEET (20 points) 1. Use Excel functions to calculate values of the highlighted cells. Read the notes at the bottom of the worksheet carefully. Use Absolute Addresses / References of Assumptions in calculations. 2. Select and print out the calculation result in PDF format. 3. Select and print out the cell formulae of your calculations in PDF format. II. “PROJECTION” WORKSHEET (20 points) 1. Use information and assumptions on yearly growth rates in Projection worksheet to project revenues from all sources in the next 5 years and their totals. Use Absolute Addresses / References of Assumptions in calculations.

2. Use “Total Projected Revenues from All Sources” to create a chart showing the trend line in the next 5 year periods. (The curve should starts from the origin)

3. Select and print out the calculation result in PDF format. 4. Select and print out the cell formulae of your calculations in PDF format. 5. Select and print out the chart in PDF format.

$8,000,000

$10,000,000

$12,000,000

$14,000,000

$16,000,000

$18,000,000

1 2 3 4 5 6

Projected Revenue by Period

2

III. “PIVOT TABLE & CHART” WORKSHEET 1. Use Pivot Table and data in Sales worksheet to create “Summary of Sales by Sales Reps” as shown in the following picture.

2. Select and print out the ”Summary of Sales by Sales Reps” calculation result only in PDF format. 3. Use information in the “Summary of Sales” table to create the following Pivot chart. Place it next to the “Summary of Sales by Sales Reps” table.

4. Select and print out the chart in PDF format.

3

IV. “MORTGAGE SCHEDULE” WORKSHEET (40 points) 1. You plan to refinance your property valued at $300,000. You are considering the following alternatives. Use the given information in the worksheet as Absolute References to calculate the “Loan Payment Schedule” for each alternative:

• 30 year fixed mortgage rate of 3.8 % • 30 year fixed mortgage rate of 3.8 % with additional monthly payment of $300.00

to the principal • 15 year fixed mortgage rate of 3.2 % • 15 year fixed mortgage rate of 3.2 % with additional monthly payment of $300.00

to the principal 2. Calculate how earlier you can pay off your loan and amount of saved interest if you make additional monthly payment and/or refinance for 15 years instead of 30 years. Print out of your calculation showing the last period when the loan is paid-off. 3. In a Word page discuss which one is the most beneficial among these 4 alternatives,. Save your arguments and discussion in a PDF file. 4. Select and print out in PDF format the calculation results for the first 12 periods and the last 12 periods (where the remaining balance is paid-off in full) of each schedule and alternative. 5. Select and print out in PDF format the cell formulae for the first 12 periods and the last 12 periods (where the remaining balance is paid-off in full) for each schedule and alternative. V. DELIVERABLES - Put YOUR NAME in the caption “Prepared by …” of each worksheet. All selected areas in for printing should showing the caption of the worksheet with your name. -All print-outs should be in landscape format, showing the gridlines and saved as PDF. (For print-out of formulae, adjust column widths large enough, narrow margins, scale to fit in 1 page to show complete cell formulas. See attached samples). Any kind of screenshot is not accepted. -Assemble all PDF pages in proper order into a single PDF file entitled “Excel-312- F20_Your Name”. - Submit your PDF file by the due date specified in CANVAS ASSIGNMENT. You have only ONE (1) attempt to submit your project and CAN’T resubmit again. Late Project will be rejected by Canvas.

________________

CYBERIA FINANCE FORECAST Prepared by YOUR NAME

2017 2018 2019 2020 2021 2022 Revenue Unit Sold 120,000 134,400 150,528 168,591 188,822 211,481 Unit Price $2.75 $2.89 $3.03 $3.18 $3.34 $3.51 Gross Revenue $330,000.00 $388,080.00 $456,382.08 $536,705.33 $631,165.46 $742,250.59

Fix Cost Production Facility $50,000.00 $52,500.00 $55,125.00 $57,881.25 $60,775.31 $63,814.08 Administration $35,000.00 $37,100.00 $39,326.00 $41,685.56 $44,186.69 $46,837.90 Variable Cost Unit Mfg Cost $1.75 $1.82 $1.89 $1.97 $2.05 $2.13 Variable Mfg Cost $210,000.00 $244,608.00 $284,919.40 $331,874.12 $386,566.97 $450,273.21

Earning Before Taxes $35,000.00 $53,872.00 $77,011.68 $105,264.40 $139,636.49 $181,325.41

Initial Values Increase First Year Sales 120,000 0.12 Unit Price $2.75 0.05 Unit Mfg Cost $1.75 0.04 Production Facility $50,000.00 0.05 Administration $35,000.00 0.06 First Year of Forecast 2017

R ev

en ue

C os

t A

ss um

pt io

ns

CYBERIA FINANCE FORECAST Prepared by YOUR NAME

2017 2018 2019 2020 2021 2022 Revenue Unit Sold 120000 =D6*(1+$F$20) =E6*(1+$F$20) =F6*(1+$F$20) =G6*(1+$F$20) =H6*(1+$F$20) Unit Price 2.75 =D7*(1+$F$21) =E7*(1+$F$21) =F7*(1+$F$21) =G7*(1+$F$21) =H7*(1+$F$21) Gross Revenue =D6*D7 =E6*E7 =F6*F7 =G6*G7 =H6*H7 =I6*I7

Fix Cost Production Facility 50000 =D11*(1+$F$23) =E11*(1+$F$23) =F11*(1+$F$23) =G11*(1+$F$23) =H11*(1+$F$23) Administration 35000 =D12*(1+$F$24) =E12*(1+$F$24) =F12*(1+$F$24) =G12*(1+$F$24) =H12*(1+$F$24) Variable Cost Unit Mfg Cost 1.75 =D14*(1+$F$22) =E14*(1+$F$22) =F14*(1+$F$22) =G14*(1+$F$22) =H14*(1+$F$22) Variable Mfg Cost =D6*D14 =E6*E14 =F6*F14 =G6*G14 =H6*H14 =I6*I14

Earning Before Taxes =D8-(D11+D12+D15) =E8-(E11+E12+E15) =F8-(F11+F12+F15) =G8-(G11+G12+G15) =H8-(H11+H12+H15) =I8-(I11+I12+I15)

Initial Values Increase First Year Sales 120000 0.12 Unit Price 2.75 0.05 Unit Mfg Cost 1.75 0.04 Production Facility 50000 0.05 Administration 35000 0.06 First Year of Forecast 2017

R ev

en ue

C os

t A

ss um

pt io

ns