Scribd
Upload
21 views

Excel Test - Advanced - Problem Solving

Uploaded by

Mita Agustina
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
21 views

Excel Test - Advanced - Problem Solving

Uploaded by

Mita Agustina
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
You are on page 1/ 6

Q1) Instructions

Calculate Net Operating Income and create a dynamic model that updates based on the holding period o
period.

T
Assumptions
Rental Income Growth Rate 3.5%
R&M as a % of Rev. 9.6%
Utilities as a % of Rev. 7.8%
Mgmt. Fees as a % of Rev. 4.4%

Holding Period → ← Create Dropdown List

Holding Period
1 2 3 4
Revenue:
Rental Income $ 55,995
Total Revenue
Operating Expenses:
Repairs & Maintenance
Utilities
Management fees
Total Operating Expenses
Net Operating Income

TRUE TRUE TRUE TRUE


TRUE TRUE TRUE TRUE
TRUE TRUE TRUE TRUE
FALSE TRUE TRUE TRUE
FALSE TRUE TRUE TRUE
FALSE TRUE TRUE TRUE
FALSE TRUE TRUE TRUE
FALSE TRUE TRUE TRUE
d on the holding period of 2-10y. Assume no sale proceeds at the end of the holding

T
Holding Period
5 6 7 8 9 10

TRUE TRUE TRUE TRUE TRUE TRUE


TRUE TRUE TRUE TRUE TRUE TRUE
TRUE TRUE TRUE TRUE TRUE TRUE
TRUE TRUE TRUE TRUE TRUE TRUE
TRUE TRUE TRUE TRUE TRUE TRUE
TRUE TRUE TRUE TRUE TRUE TRUE
TRUE TRUE TRUE TRUE TRUE TRUE
TRUE TRUE TRUE TRUE TRUE TRUE
T
Q2) Instructions
Fill the tables to show the top 5 and bottom 5 brands by gross margin percentage

Brand Revenue COGS Gross Margin Gross Margin % Top 5 Gross Margin %
Coca-Cola 928,221 371,488 556,733 60.0% Rank Margin % Brand
Diet Coke 710,961 641,971 68,990 9.7% 1 FALSE
Sprite 464,919 188,255 276,665 59.5% 2 FALSE
Fanta 654,491 331,570 322,920 49.3% 3 FALSE
Vitamin Water 606,332 395,368 210,963 34.8% 4 FALSE
Honest Tea 956,480 737,076 219,404 22.9% 5 FALSE
Powerade 307,895 188,970 118,925 38.6%
Costa Coffee 277,939 112,880 165,059 59.4% Bottom 5 Gross Margin %
Minute Maid 443,868 268,369 175,498 39.5% Rank Margin % Brand
Peace Tea 146,516 85,987 60,529 41.3% 1 FALSE
Georgia Coffee 468,784 414,121 54,663 11.7% 2 FALSE
Aquarius 716,204 329,969 386,235 53.9% 3 FALSE
4 FALSE
5 FALSE
T
FALSE
FALSE
FALSE
FALSE
FALSE

FALSE
FALSE
FALSE
FALSE
FALSE
Q3) Instructions
Create a monthly salary expense table in the light blue area. Please consider prorated expense amounts given a specific resignation date

Resignations Salary per monthResignation date 1/1/2022 2/1/2022 3/1/2022 4/1/2022 5/1/2022 6/1/2022
Research Analyst $5,000 3/15/2022
Sr. Financial Analyst $7,000 6/17/2022
Jr. Financial Analyst $5,000 2/5/2022
HR Recruiter $4,000 8/25/2022
Copywriter $3,500 1/18/2022

T
0 0 0 1 1 1
0 0 0 0 0 0
0 0 1 1 1 1
0 0 0 0 0 0
0 1 1 1 1 1
7/1/2022 8/1/2022 9/1/2022 10/1/2022 11/1/2022 12/1/2022

1 1 1 1 1 TRUE
1 1 1 1 1 TRUE
1 1 1 1 1 TRUE
FALSE FALSE 1 1 1 TRUE
TRUE TRUE 1 1 1 TRUE

You might also like