Microsoft Excel - A Dozen of Solved Mini Projects or Models
Genre: eLearning | MP4 | Video: h264, 1280x720 | Audio: AAC, 48.0 KHz
Language: English | Size: 3.30 GB | Duration: 5h 33mMicrosoft Excel or Excel based ready projects or models from a Chartered Accountant, fully solved spreadsheets included.

What you'll learn
The students would be learning practical aspects of making useful presentation using Microsoft Excel.
The students will learn how to solve mini projects using Microsoft Excel. 12 such mini projects will be part of the lesson.
Students will be able to combine various formula and functions to achieve desired objectives.
The students will migrate from being an Excel lover to an Excel Expert, so that he can show off his work and take pride in his work.
This course is not for the green horns. People who already know the basics of Excel can take this course.
The students will finally be able to develop full fledged Excel models for commercial use.

Description
Knowing functions and formula is not good enough to become an Excel expert. You need to complete some Excel mini projects developing hands on applications. Once you are through with the mini projects, you may be able to handle larger commercial projects and develop bigger excel models which are handsomely rewarding.

This course is a hands on practical training on Excel mini projects. This comprises of 26 lectures. First I shall give you a project in one lecture. I shall give you hints on how to solve the project and what functions to use. In the next lecture I shall give you the solution through video accompanied by an Excel spreadsheet/workbook which you can download. The workbook is not password protected. There will be 12 mini projects, through which I shall teach how to develop an Excel application for office work at bank, educational institutions and companies. You should be able to proudly show off your Excel skills.

This course is of advanced level. You must have basic awareness about Excel spreadsheet and Excel functions.

The mini projects are as follows :

01. Preparing individual report card of students dynamically from the table of marks sent by the controller of examination.

02. Making dynamically the EMI payment schedule of the customer of a bank and understand bank's profitability.

03. Preparing projected Income Statement, Cash flow and Balance Sheet of a business entity based on the information given.

04. Making dynamic picture charts, where pictures will represent numbers.

05. Assessment of a project using five different criteria and comparing two or more projects.

06. Optimizing production and dispatch plan of a large company which has different factories at different locations and customers at different locations, where it maintains the warehouses.

07. Decision on "buy or not to buy" a bond based on its current market price in the secondary market and the current interest rate.

08. Calculating the Price of a Future Contract of a Stock quoted in the stock exchange.

09. Making HR dashboard to monitor the performance of salesmen and show the report with chart also.

10. Calculating Relative Strength Indicator of a Stock

11. Calculating Price volatility of a stock.

12. Determining cost of capital based on CAPM.

For your clarity I am showing one Excel mini project here.

* All Excel mini projects / cases can be downloaded from the first lecture itself, which is not protected.

Mini project 03 ( Keeping in mind world reach, no currency symbol is used. You can use any currency symbol)

Rocky started a business of cloud kitchen on 01.09.2021. He is a franchisee of "GoodFood" chain. He has paid upfront 500000 as franchisee fee to "GoodFood" chain from his personal savings. This covers the right to use "GoodFood" brand and technical knowhow to run a food kitchen. As per income tax rule of his country, this payment with no refund right attached has to be fully capitalized, Rocky has also spent 800000 on kitchen equipment. He has paid a deposit of 100000 to the owner of the premises for hiring the space for cloud kitchen. A loan of 1000000 from a local bank at 7% interest per year was taken payable in 36 EMI. He is now part of the "GoodFood" brand. The "GoodFood" chain also provides him the technical knowhow on procurement of ingredients and cooking of various dishes. For all the services provided "GoodFood" chain claims a monthly royalty of 5% on all the sales. On the other hand he uses "Xmata" for getting order and making delivery of food . "Xmata" has a very popular online portal and delivery app. It takes online order from its customers in that app and portal and picks up the food from Rocky's cloud kitchen. "Xmata" also collects price from the customers, and transfers the same to Rocky's bank account after deducting 15% as service charge. This takes about 7 days from the day of sale. Rocky does not allow any walk-in customers in his kitchen. That is why this business model is known as cloud kitchen. Thus Rocky provides 7 days' credit to "Xamata". He also gets 15 day's credit from his suppliers of meat, fish, egg, vegetables, vegetable oil and other ingredients & spices. For all practical purpose he considers it as a cash business. Cook and other employee cost is 40000 per month. Rent is 30000 per month. His gross profit on sale of food is 60%. From this gross profit rent, employee cost, depreciation of equipment and interest has to be deducted to arrive at net profit. Income Tax is to be paid @ 25% on net profit calculated as per income tax rules. Equipment depreciation is 20% per year at fixed rates. For income tax purpose the equipment is depreciated at 30% on accelerated depreciation basis. Income tax payable to the government has to be calculated and accumulated monthly and deposited quarterly. His average daily sale is estimated to be 10000 in the first month. per day. He runs his cloud kitchen every day. Weekly leaves are granted to the employees on rotational basis. The sales are projected to grow @ 5% per month for first 12 months and stabilize at that level thereafter. 5% GST has to be collected from sale of food and deposited to the government. GST collected in a month has to be deposited the next month. No input GST credit is allowed to cloud kitchen as per GST rules. GST has to be collected monthly, and a month's GST collection has to be deposited to the government next month. (If in your country there is no GST you can ignore this point.).

Currency : No currency symbol is used. The numbers can be in any currency.

Task to be completed using Microsoft Excel

a. Prepare projected Income Statement for 4 months, i.e. September 2021 to December 2021
b. Prepare projected Cash Flow Statement for 4 months, i.e. September 2021 to December 2021
c. Prepare projected Balance Sheet as on 31.12.2021.
The minimum proof of your success is that the total of assets and liabilities in the projected Balance Sheet as on 31.12.2021 should be the same.

Hints:
First you have to create an opening Balance Sheet as on 01.09.2021.
For various end of the dates of month EOMONTH() function has to be used.
For EMI calculation you have to prepare an EMI Schedule. PMT() function pay be used.
Interest in that schedule will be a charge in the Income Statement.
Principal repayment as per EMI schedule will figure in the Cash Flow statement and will also be reduced from the Liabilities side from the balance of Bank Loan.
A schedule for income tax calculation has to be prepared, where book depreciation should be added to the net profit as per books and accelerated deduction as per income tax rules has to be deducted.
All the fields which contain assumptions, picked up from the description above should be marked in yellow and be unprotected and kept in one place.
All other cells which are calculations should be protected, so that they are not accidentally changed or overwritten.

You will get a PDF files of all the projects / cases to download along with the first lecture, which is open and not protected from public viewing.

Please assess the cases / projects and then follow the lectures sequentially.

Who this course is for:
For all Excel lovers. Will be particularly useful for students of finance, statistics and operation research.