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. VLOOKUP() & dynamic report card,

02. Dynamic EMI and Bank's Profit,

03. Business Model development - Projected Income Statement, Cash flow and Balance Sheet,

04. Dynamic Picture Chart,

05. Five Project Assessment Criteria,

06. To buy or not to buy a bond from the secondary market,

07. Transportation Problem : Operations Research,

08. Valuation of Future Contracts,

09. Marketing Dashboard-Social Media,

10. Relative Strength Index (RSI) calculation,

11. Profit optimization : Operations Research,

12. Chartless Chart and Chart on a Map,

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.