Excel Exercise

Durga-prasad_01

BloomAMGInterviewExcelExercise.xlsx

Home >Business & Finance homework help >Operations Management homework help >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

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	?