Video BlogPower BI | Excel | Financial Modelling | Financial Analysis | Office 365
Our video blogs are packed with valuable learning, tips and tricks to enhance your analysis skills and show you how things are actually done in practice.
The Videos are categorised in-line with dbrownconsulting’s analyst competency Framework. The categories mimic the technical skills expected of anyone in the finance profession which includes Accountants, Analysts, Investment Bankers and similar fields. We also have video for Learning & Development Experts to Highlight best practices in the Talent Industry.
Enjoy and don’t forget to leave a comment.
GREAT TECHNIQUE TO PERMANENTLY PROGRAM YOUR COMPUTER WITH THE NAIRA CURRENCY (₦) SYMBOL
As an analyst, you should definitely format your reports appropriately to correctly convey information. In order to do this effectively, you will need to learn how to program the naira sign into your computer, so anytime you need it. It appears. To do this, you need to program you currency symbol.
1. Go to home, you will see the currency Icon, you need the Naira sign here
It is not here currently. You want a setting so that when you click it, it brings the Naira sign. There is a trick to do this. However, it is not an excel thing, it is a windows thing. You need to do this in control panel.
2. Press the Windows button on your keyboard, type “Region and language”.
3. Click on Region and a dialog box appears.
4. In the dialog box, go to Additional settings.
- 5. In Additional setting, you will see Number, Currency, Time and Date tabs. Go to Currency.
- 6. In Currency symbol section, click on the dropdown and you will see the pound sign and other currency signs. Note: Be sure to have copied the Naira s.g8. ₦), first before you come to this section.
- 7. Delete the pound sign and paste the naira sign with CTRL + V.
- 8. When you are done, you can now click Apply.
9. Click OK, then OK again in the next dialogue box.
You have programmed your currency in your control panel. When you come back to excel, you can see it there. It automatically changes to ₦aira.
10. Highlight the cells you want to format, use the keyboard shortcut (CTRL + SHIFT + 4) or the format command on the Home tab to format.
It shows with Naira sign (₦). When you click the dropdown, you will see other currency symbols there to be used. However, your default has now been set to Naira (₦) sign.
This technique would certainly improve your reporting considerably. It will help your reporting to look far better by representing the Nigeria currency symbol appropriately. Hope this helps you. Ciao
Tip: Let’s get Microsoft to make all the above automatic by changing the default currency for Nigeria from NGN to ₦.
Praise was working on a sales report that was due for presentation to management the next day, she had earlier used Excel’s Number Formatting to change the Sales Figures to a Currency Format. However, Excel’s defaults currency format is not in Naira(₦), and management wants to see sales in Naira (₦). There are several situations we encounter when working in Excel that require us to represent figures in Naira (₦), this article would teach you an easy way to program Excel and your computer to format the default currency as the Naira (₦), rather than representing the Naira symbol with NGN or using a double strikethrough on the “N” alphabet then copying and pasting repeatedly. Note: Using the double strikethrough only works in Microsoft Word, this option cannot be found in Excel, Excel only has the option of a single strikethrough. Let’s learn how to program the Naira symbol. Steps to Program the Naira currency symbol. (₦)
- First, you must have Excel workbook
- Go to the Insert Tab (We are using Excel 2016)
- Click on the symbol command.
- Symbol Dialogue box comes up.
- Change your font to Arial. We select the Arial Font here because there are some font types that do not contain the Naira symbol
- Go to the bottom of the Symbols dialogue box, ensure Unicode (hex), is selected from the drop down.
- Then go to the subset dropdown at the top right corner, click on the dropdown and select Currency Symbols
- Once you select the Currency Symbols, the Naira sign should now be displayed.
- Select the Naira Sign
- Then click on Insert, then Close. The Naira Symbol would be inserted into a cell
- Now we have got the Naira Symbol. It does not end there; for us to be efficient with Excel we need to automate the process.
Note: you can automate in Excel without the use of VBA codes
- The goal here is to automate the Naira symbol into Excel so that, whenever we type NGN Excel would automatically convert it to the Naira symbol.
- Now go to the formula bar, highlight and copy the Naira symbol your early inserted and copy it. (Ctrl+C)
- Go to the File Tab
- Click on Options, (you can use the Shortcut ALT + T + O)
- Under Excel option, go to Proofing;
- Select AutoCorrect options
- Click ok
- Go to Replace and type NGN
- Now we want to replace it with the Naira symbol so paste the Naira symbol we earlier copied. (Ctrl V)
- Click on Add, to add to your auto correct
- Click ok, then ok again
- Note: whatever we do in the Excel option affect all the office suites (Word, PowerPoint etc.)
- Now we have successfully programed Excel to replace NGN with the Naira symbol.
- Now anytime we require the Naira symbol just type NGN (press Enter or Space) and Excel automatically replaces it with the ₦
- You can also program Excel to any currency of your choice.
- Click ok, then ok again
Now you can go ahead and try it Yourself. Summary of Steps
- Open a work book
- Go to the Insert Tab
- Click on the Symbol Command
- On the bottom right of Symbol dialog box ensure it is Unicode (hex)
- Change the font to Arial
- On the Subset dropdown, Select Currency Symbols
- Select the Naira Sign
- Click Insert
- Click Close
- Copy the Inserted Naira symbol
- Go to File
- Select Options
- Go to Proofing
- Select AutoCorrect Options
- Type NGN under “Replace”
- Paste the Naira symbol under “With”
- Click Add
- Click Ok
******************************************************************************************** Other Resources: Click HERE to See what we do We run a free webinar every month, so feel free to join the next one by clicking the link below and join our other Free online resources.
- Register for Next Webinar:https://attendee.gotowebinar.com/rt/5553115737172548353
- Join our Meetup Group:https://www.meetup.com/Nigeria-Excel-Power-BI-User-Group/
- Join Power BI User Group for Nigeria:pbiusergroup.com/lagos
- Subscribe to our YouTube Channel https://www.youtube.com/channel/UCpJud9aV5aFlja3PYAhHT-w
- We will be glad to discuss any related training needs you have; connect with us email@example.com or call +234 (0)700TRAINING (070087246464).
Please send your questions, comments and feedback to: firstname.lastname@example.org.
Power Pivot Drama…the Saga Continues
So, I don’t know about you, but in as much as Power Pivot is super cool, it can be super stressful as well! I am building an automated valuation and reporting tool for a client. They require the model to report on their Bonds, Money Markets, Treasury Bills, Capital Markets, Real Estate, Cash Accounts and Dividend Receivables. The model consists of several fact files and dimension files totaling about 15. I am having fun, to say the least. Initially, I built the model using Excel 2016 but had to transfer it to Excel 2013. That brought about a bit of “confusion” to my model. My goal is to keep you updated on challenges I have had in my Power Pivot journey and how I resolved them. CONFUSION 1: MAX and MIN works as an Aggregator but not as a “normal” Excel MAX AND MIN function So, as you know, MAX and MIN are aggregators in DAX formulas. I had a formula for calculating holding days of a Euro Bond that uses a 30/360 convention as follows: Random Side Note: Formula Formatting is compliments of www.daxformatter.com for more “eye-friendly” DAX formulas. Which one looks better? The above or…… = MAX(30 – DAY(ValueDate), 0) + MIN(30, DAY(Rdate)) + 360*(year(Rdate) – year(ValueDate)) + 30*(MONTH(Rdate) – MONTH(ValueDate) – 1) You can format your formula on the website with the click of a button and then copy and paste it into your Model. Now, back to my story… In Excel 2016, this formula acted “responsibly”. BUT…..Excel 2013, rejected it. 2013 said, “I won’t agree”. I asked 2013, “Why? What was my offence?” 2013 said, “You didn’t pay homage, therefore MAX and MIN as Non-Aggregators are forever banned from the 2013 kingdom” I pled. I cried. I fasted. I prayed. To no avail. Though the fasting and praying did not bring about a change in 2013’s mind, it did bring about a revelation. What was that revelation? =IF() BABY! (Please note, Baby is not included in the formula!) Modified 2013 Approved Formula: Key Takeaways:
- Do not negotiate with terrorists AKA Excel 2013!
- When there is a will, there is a way AKA learn to think outside of the box!
- Always confirm the version of Excel that will be used by the client before building a gangster model AKA ask the right questions ahead of time!
Stay tuned for CONFUSION Number 2.