In today's fast-paced digital landscape, efficient file management is crucial for businesses. One powerful solution that can streamline your document organization and storage is Box. Box is a cloud-based platform that facilitates file storage, sharing, and access across multiple devices. It offers collaboration features, secure storage options, and integrations with various tools to simplify document management and boost efficiency. In this blog, we’ll delve into retrieving and integrating data from Box with Bold BI® and showcase it in a dashboard.
Find your Box data
To access and acquire information from your Box account, you must first log in by entering your account credentials. Once you have successfully signed in, navigate through the various files and folders within your account to locate the specific data you wish to retrieve and extract. To connect to Bold BI, navigate to the data source page and choose the file format from the following types to connect to Bold BI: CSV, JSON, XML, or XLSX.
Let’s now look at how data that is available in your Box account can be integrated into the Bold BI application.
Connecting Box data to Bold BI
To connect Box data to Bold BI:
Click the data source button on the dashboard home page to navigate to the data source creation page.
On the data source pane, click CREATE NEW to begin a new data source connection.
Navigate to the Cloud Storage category and select Box
4.Authenticate with your Box login credentials.
5.The connected account will pop up as shown in the following image. Select the account by clicking Next.
6.Once authentication is completed, provide the data source name. Then select the file you intend to preview and connect.
7.Preview and connect the data source.
8.Once connected, navigate to the data source designer page and drag the table to the canvas. Click Save.
After connecting the data source to your Bold BI account, you can visualize its data by creating a dashboard.
Creating a dashboard with Box data
Let’s start recreating this Financial Management Dashboard with Box integration data. We can create a dashboard from scratch by dragging widgets into the dashboard designer. In this financial management dashboard, users can gain real-time financial insights by tracking expenses and monitoring their budgets.
The Financial Management Dashboard helps users monitor key financial metrics:
- Accounts Receivable: These are the company’s outstanding invoices, or the money clients owe the company.
- Revenue: The money generated from selling goods or services during a specific time period.
- Current Ratio: The current ratio measures the ability of a company to honor short-term obligations or those due within one year.
- Accounts Payable: This refers to the money owed by a company to its creditors or suppliers for goods or services received on credit.
- Gross Profit Margin: This is the percentage of sales revenue the company can convert into gross profit.
- Burn Rate: The rate at which the company depletes its cash pool in a loss-generating scenario.
- Equity Ratio: The equity ratio is a metric that measures the amount of leverage used by a company.
- Debt Equity: The capital structure of a company and the proportions of debt and equity it uses in financing its operations.
- Profit and Loss Summary: The monthly profit and loss summary tracks total sales, cost of goods sold (COGS), and operating expenses (OE).
- Days Sales Outstanding: DSO measures the average number of days it takes for a company to collect payments from customers after a sale has been made.
- Net vs. Gross Working Capital: Net working capital is the average value of all current assets and current liabilities while gross working capital refers to just the assets or the company's total financial resources.
- AR Turnover vs. AP Turnover: AP turnover measures how effectively a company manages its payments to suppliers or vendors while AR turnover measures how efficiently a company collects customer payments.
- Days Inventory Outstanding: This metric measures the average number of days it takes for a company to convert its inventory into sales.
- Days Payable Outstanding: This metric measures the average days it takes for a company to pay its suppliers or vendors after the receipt of goods or services.
- Inventory: Inventory refers to the goods, raw materials, or works-in-progress that a company holds to sell or produce goods and services.
- Accounts Payable by Payment Target: How much is owed to suppliers by when payments are due.
How to configure data in widgets in the dashboard
Using the data from the data source we created, we can configure the listed metrics in a dashboard. Before configuring the data in widgets, we need to create expressions for the metrics.
Some metrics can only be achieved by use of calculated fields called expression columns. To create expression columns for the following columns in widget configuration, use the functions listed in the next table.
| Expression | Function |
| Assets Amount | IF([Type]=1,[AR],0) |
| Liability Amount | IF([Type]=2,[AP],0) |
| Total Equity | SUM(IF([Type]=1,[AR],0)) - SUM(IF([Type]=2,[AP],0)) |
| Equity Ratio | (SUM(IF([Type]=1,[AR],0)) - SUM(IF([Type]=2,[AP],0))) / SUM(IF([Type]=1,[AR],0)) |
| Current Assets Amount | IF([Due_Date] = 'Current' AND [Type] = 1,[AR],0) |
| Current Liability Amount | IF([Due_Date] = 'Current' AND [Type] = 2,[AP],0) |
| Current Ratio | SUM(IF([Due_Date] = 'Current' AND [Type] = 1,[AR],0)) / SUM(IF([Due_Date] = 'Current' AND [Type] = 2,[AP],0)) |
| Net Working Capital | SUM(IF([Due_Date] = 'Current' AND [Type] = 1,[AR],0)) - SUM(IF([Due_Date] = 'Current' AND [Type] = 2,[AP],0)) |
| Gross Working Capital | SUM(IF([Due_Date] = 'Current' AND [Type] = 1,[AR],0)) |
| Days Sales Outstanding | ((SUM(IF([Type]=1,[Amount],0)) - SUM(IF([Type]=1,[AR],0))) / SUM(IF([Type]=1,[Amount],0))) * 31 |
| Days Inventory Outstanding | (SUM([AP]) / SUM(IF([Type]=2,[Amount],0))) * 31 |
| Days Payable Outstanding | (SUM(IF([Type]=2,[AP],0)) * 31) / SUM([COGS]) |
| Working Capital | SUM(IF([Due_Date] = 'Current' AND [Type] = 1,[AR],0)) - SUM(IF([Due_Date] = 'Current' AND [Type] = 2,[AP],0)) |
| Burn Rate | ([Current Assets Amount]-[Current Liabilities Amount])/12 |
| Quick Ratio | ([Amount]+[Current Assets Amount])/[Current Liabilities Amount] |
| AR Turnover Ratio | [Amount]/[Assets Amount] |
| AR Turnover Days | 365/[AR Turnover Ratio] |
| AP Turnover Ratio | [Amount]/[Liabilty Amount] |
| AP Turnover Days | 365/[AP Turnover Ratio] |
| Debt Equity | SUM([COGS]) / SUM(IF([Type]=2,[AP],0)) |
Follow these steps to visualize the listed metrics:
- Add the required widgets to the blank dashboard that you created in the beginning.
- Configure the widgets in the dashboard with their corresponding data sources, as described in the following table.
| Metric/KPI | Configuration |
| Accounts Receivable | Type: Number Card Value: Assets amount |
| Revenue | Type: Number Card Value: Total sales |
| Current Ratio | Type: Number Card Value: Current ratio |
| Accounts Payable | Type: Number Card Value: Liability amount |
| Gross Profit Margin | Type: Radial Gauge Actual Value: Gross profit margin |
| Burn Rate | Type: Number Card Value: Average burn rate |
| Equity Ratio | Type: Number Card Value: Equity Ratio |
| Debt Equity | Type: Number Card Value: Debt equity |
| Profit and Loss Summary | Type: Column Chart Values: Total sales, cost of goods sold, operating expense Column: Converted Date |
| Days Sales Outstanding | Type: Radial Gauge Actual Value: DSO |
| Net vs. Gross Working Capital | Type: Line Chart Values: Net working capital, gross working capital Column: Converted Date |
| AR Turnover vs. AP Turnover | Type: Column Chart Values: AR turnover days, AP turnover days Column: Converted Date |
| Days Inventory Outstanding | Type: Radial Gauge Actual Value: DIO |
| Days Payable Outstanding | Type: Radial Gauge Actual Value: DPO |
| Inventory | Type: Spline Chart Value: Current assets amount Column: Converted Date |
| Accounts Payable by Payment Target | Type: Column Chart Value: Liability amount Column: Converted Date |
Finally, the dashboard is ready. You can share this financial management dashboard with your finance team by enabling access permission to it. Then, simply copy the URL from the address bar and send it. Refer to this overview for more details on sharing dashboards. Once you have completed configuring the widgets in the dashboard, you can also schedule data refreshes so that the dashboard data refreshes regularly.
How to schedule data refreshes for a Box dashboard
You can set up data refresh schedules in the dashboard to ensure your data remains current. For a Box integration, you utilize the Refresh Settings feature to establish a timetable for transferring data from your cloud storage to an intermediate database location.
Follow these steps to configure refresh settings:
- On the dashboard designer page, click the edit icon under the data source pane.
2.On the data source designer page, click Refresh Settings.
The refresh settings schedule will pop up, as shown in the following.
Once the refreshes have been scheduled, the dashboard will automatically refresh and the most recent refresh time will show next to the toolbar at the top.
I hope you have grasped how individuals and businesses can unlock a world of easier financial management by utilizing a dashboard with Box integration. This blog has shown you how to connect to Box and create one of these dashboards in Bold BI®. Try creating one with your data and see how useful it can be!
Top comments (0)