Spreadsheet modeling
Dr. Bharati Wukkadada
Assignment 1
Q. 1 As part of their application for a loan to buy Lakeside Farm, a property they hope to develop as a bed-and-breakfast operation, the prospective owners have
projected:
Monthly fixed cost (loan payment, taxes, insurance, maintenance) $6000
Variable cost per occupied room per night $ 20
Revenue per occupied room per night $ 75
a. Find the total cost per month. Assume 30 days per month.
b. Find total revenue per month.
c. If there are 12 guest rooms available, can they break even? What percentage of rooms would need to be occupied, on average, to break even?
Q. 2 U SE THE FILE AND PERFORM THE FOLLOWING ( E MPLOYEE DATA . XLS )
1) How many emails are in job category 1
2) How many women are there with category 3
3) Find the three highest salary
4) Find three lowest salary
5) Find the age of each employee as of 01/01/2018 or today
6) Find the job category-wise total salaries we are paying
7) Find the total salary gender wise
8) Find out the total number of people born before 01/01/1960
Q. 3 Use File Sales and Distribution and Answer following queries
1. Find out zone wise Total sales and Highlight the highest value
2. Find out state-wise and category-wise total profits
3. Find out category-wise contribution to total profits
4. Find out average sales done state-wise
5. Find out monthly sales done
Q. 4 The sales data for a Ciaz Car is given below
Name Car Sales colour Commission
Raj 15 Black
Rani 19 Brown
Riya 20 white
Ramola 12 grey
Ram 2 golden
Raju 27 grey
Ramesh 9 golden
Amit 15 Brown
Anup 45 Black
Anuj 25 white
Amrita 21 grey
Alok 17 golden
Anaysha 16 grey
Ananya 13 Brown
Amol 21 white
Anjali 24 white
Anish 31 grey
Anshuman 33 Black
Amrita 23 Black
Aney 9 Brown
Amey 44 grey
Amita 32 white
Aliya 21 grey
Anjana 30 Black
Amita 18 Brown
Anup 18 white
Commission table is
Volume Percentage
0 0%
10 5%
20 10%
25 15%
30 20%
1) Find out the commission percentages for above sales people
2) Count how many sales person who sold black color car got commission 20%
3) Find out which colour car got sold more
4) Count how many people bought Grey color car and got 5% or 10% discount.
5) Who sold highest number of Cars?
Q. 5 A) Following are the salaries of 5 employees. Find out total salary of these
employees
Pay Roll No Name Salary Rs. Part time Rs. Arrears
1011 Prasanna 10000 900 1800
1012 Anitha 14000 800 1600
1013 Ravi 18000 700 1700
1014 Saritha 15000 600 1600
1015 Mallika 17000 500 1800
Using Conditional Formatting, list out employees who got
a) Less than Rs. 15000 as salary
b) More than Rs. 700 as Part-time
c) Rs. 1600 as Arrears.
d) total salary above Rs. 17000
e) Highest Salary
B) Create a mortgage calculator with varied interest rates showing the difference of monthly payments, total repayment, and total interest paid. (Use:
Mortgage calculator.xlsx)
Q. 6 ABC Co. Ltd wants to analyze their net income. Use ABC.XLSX and use the following formulas to complete the spreadsheet to calculate net income. Do the
further analysis using data table where copy volumes (Copies/Month/Copier) ranging from 22000 to 32000. You need to track how changes in copy volumes
affect Net Income.
Cell Formula
Fixed Expense per Copier (B7) Monthly Lease Cost + Copier Service Cost + Other Fixed Costs
Revenue (B12) No. Of Copier Leased x Copies/Month/Copier x Price Charged per Copy
Cost of Goods Sold (B13) No. Of Copiers Leased x Copies/ Month/Copier x Variable Cost per Copy
Contribution Margine (B14) Revenue – Cost of Goods Sold
General & Admin. Costs (B15) No. Of Copiers Leased x (Fixed Expense per Copier + Space Rental Rate)
Net Income (B16) Contribution Margin – General & Admin. Costs
Use the Scenario Manager to generate a summary of the below scenarios.
Scenario Name Copy Volume (Copies/Month/Copier)
Expected Demand 35000
Very Low Demand 18,000
Very High Demand 55,000
The Scenario Manager should track the values for Revenue, Cost of Goods Sold, Contribution Margin, and General & Admin. Costs.
