Excel Exercise
#1
Exercise #1 Please derive the revenue waterfall from sales bookings
One common technique used in contract model is waterfalls approacch.
All contracts are 1 year contract and will be fully recognized in revenue in 4 quarters
Booked deals are spread evenly (ratably) as revenue over the lifetime of the contract
You can assume that you can immediately recognize revenue upon booking the deal (i.e. you book the deal in Q1’20, you can begin to recognize revenue for that deal in that quarter)
Please show your work on how you derive at the revenue recognized for that quarter
Q1’20 Q2’20 Q3’20 Q4’20 Q1’21 Q2’21 Q3’21 Q4’21 Q1’22 Q2’22 Q3’22 Q4’22
Sales booking 120 150 160 200 216 230 230 236
Revenue ? ? ? ? ? ? ? ? ? ? ? ?
#2
2020 Budget
Business Unit Month GL Account Amount Exercise #2 Please summarize the budgeted amounts for each GL Account by Business Unit and Month.
Unit A OCT Benefits 13,899 Please group all Revenue Accounst and Expense Accounts separatedly
Unit A OCT Service Revenue 35,000
Unit A OCT Expendable Materials & Supplies 8,099
Unit A OCT COGS 38,533
Unit A OCT Hourly Labor 36,829
Unit A OCT Misc Fees 475
Unit A OCT Maintenance 631
Unit A OCT Rent 7,144
Unit A OCT Retail Sales 59,000
Unit A OCT Salary Labor 6,333
Unit A OCT Professional Service Fees 3,511
Unit A OCT Subscription Sales 13,037
Unit A NOV Benefits 14,778
Unit A NOV Service Revenue 25,000
Unit A NOV Expendable Materials & Supplies 8,099
Unit A NOV COGS 56,986
Unit A NOV Hourly Labor 36,829
Unit A NOV Misc Fees 475
Unit A NOV Maintenance 631
Unit A NOV Rent 9,614
Unit A NOV Retail Sales 101,500
Unit A NOV Salary Labor 6,333
Unit A NOV Professional Service Fees 5,356
Unit A NOV Subscription Sales 31,795
Unit A DEC Benefits 13,432
Unit A DEC Service Revenue 28,000
Unit A DEC Expendable Materials & Supplies 8,099
Unit A DEC COGS 46,143
Unit A DEC Hourly Labor 36,829
Unit A DEC Misc Fees 475
Unit A DEC Maintenance 631
Unit A DEC Rent 7,942
Unit A DEC Retail Sales 76,500
Unit A DEC Salary Labor 6,333
Unit A DEC Professional Service Fees 4,249
Unit A DEC Subscription Sales 23,674
Unit B OCT Benefits 27,323
Unit B OCT Service Revenue 325,000
Unit B OCT LLC Fees 300
Unit B OCT Expendable Materials & Supplies 21,000
Unit B OCT COGS 68,400
Unit B OCT Hourly Labor 54,465
Unit B OCT Misc Fees 1,000
Unit B OCT Maintenance 1,268
Unit B OCT Rent 57,000
Unit B OCT Salary Labor 33,572
Unit B OCT Professional Service Fees 12,355
Unit B OCT Third Party Labor 42,750
Unit B OCT Other Operating Income 55,000
Unit B NOV Benefits 29,886
Unit B NOV Service Revenue 375,000
Unit B NOV LLC Fees 300
Unit B NOV Expendable Materials & Supplies 25,126
Unit B NOV COGS 81,900
Unit B NOV Hourly Labor 54,465
Unit B NOV Misc Fees 1,000
Unit B NOV Maintenance 1,128
Unit B NOV Rent 68,250
Unit B NOV Salary Labor 33,572
Unit B NOV Professional Service Fees 14,480
Unit B NOV Third Party Labor 51,188
Unit B NOV Other Operating Income 80,000
Unit B DEC Benefits 29,922
Unit B DEC Service Revenue 350,000
Unit B DEC LLC Fees 300
Unit B DEC Expendable Materials & Supplies 23,750
Unit B DEC COGS 77,400
Unit B DEC Hourly Labor 54,465
Unit B DEC Misc Fees 1,000
Unit B DEC Maintenance 1,119
Unit B DEC Rent 64,500
Unit B DEC Salary Labor 33,572
Unit B DEC Professional Service Fees 13,713
Unit B DEC Third Party Labor 48,375
Unit B DEC Other Operating Income 80,000
#3
Exercise #3
A) Calculate weighted average
Subject Grade contribution Grade
Algebra 0.3 75
Geometry 0.2 65
Literature 0.5 90
Weighted average grade = ?
B) Show how you calculate total Sales for CA using excel formula
State Sales
CA 125
TX 90
CO 45
TX 65
CA 95
NY 35
NY 180
CA 45
FL 30
IL 70
CA 60
IL 10
NY 25
CO 215
Total Sales for CA = ?
C) Use excel formula to return sales by state by year
Data:
State 2016 2017 2018 2019 2020
CA 8,416 1,051 4,760 1,851 2,317
CO 9,382 1,094 4,833 2,089 2,395
TX 9,346 1,094 4,879 2,052 2,365
NY 8,964 1,060 4,734 1,960 2,259
FL 9,257 1,162 5,065 1,988 2,412
IL 8,851 1,090 4,726 1,904 2,313
Return:
State Year Sales
TX 2018 ?