Microsoft Office Specialist: Excel for Business Finance Associate — Practice Set 1

Question 1

You are working in Microsoft Excel and need to bring in a quarterly transactions list that a colleague exported from the company's accounting system as a .csv file. Which approach lets you import this data as a connected, refreshable query rather than a one-time paste?

Accepted Answer

Data > Get Data > From File > From Text/CSV opens Power Query, which imports the CSV as a connected query that can be refreshed later if the source file changes. Copying and pasting creates a static, disconnected copy. Insert > Object embeds the file rather than importing structured data, and Review > Compare is used to compare two workbooks, not import external data.

Question 2

A financial analyst needs to share a workbook containing a P&L summary with an external auditor who uses a spreadsheet application that only supports the plain-text CSV format. What is the correct way to accommodate the auditor?

Accepted Answer

File > Save As with the CSV (Comma delimited) file type exports the active worksheet's data as plain text values separated by commas, which any spreadsheet tool can read. Renaming a PDF's extension does not change its underlying format and will not produce valid CSV content. Emailing the original .xlsx does not solve the compatibility problem, and zipping the file does not change its format.

Question 3

A vendor payment register was imported from two overlapping exports, and several invoice rows now appear twice with identical values across all columns. What is the most efficient way to ensure each invoice appears only once?

Accepted Answer

Data > Remove Duplicates scans the selected range and automatically deletes rows that are exact duplicates based on the columns you specify, which is fast and reliable. Manually reviewing rows is error-prone in a large register. Sorting only reorders rows without removing anything, and Conditional Formatting only highlights duplicates visually without deleting them.

Question 4

A finance team imported a list of transaction descriptions where dates are stored as text such as "03/15/2026 - Vendor Payment" in a single column. They need to separate the date and the description into two columns, and also confirm the resulting dates transform correctly into usable date values. Which two features should they use? Choose 2.

Accepted Answer

Text to Columns splits a single column of text into multiple columns based on a delimiter, which handles separating the date from the description. DATEVALUE then converts the resulting date text into a true Excel date serial number so it can be used in date calculations. Conditional Formatting only affects appearance, SORT reorders rows rather than splitting text, and CONCAT would merge data back together rather than transform it.

Question 5

A controller is preparing a shared budget workbook. Only the assumptions section (cells B2:B10) should be editable by department heads, while every formula elsewhere in the sheet must remain locked from accidental edits. All cells in the sheet are locked by default. What is the correct sequence of steps?

Accepted Answer

Cell locking only takes effect once sheet protection is turned on, so you must first unlock the specific input cells (B2:B10) via Format Cells > Protection > clear Locked, and only then enable Review > Protect Sheet so every other (still-locked) cell becomes read-only while B2:B10 stays editable. Protecting the sheet first locks everything including B2:B10, and unlocking afterward requires removing protection again. Hiding columns does not restrict editing, and Data Validation restricts what values can be typed, not whether a cell can be edited at all.

Question 6

An analyst is validating a raw expense export and needs to visually flag any amount entered as negative when all valid entries in that column should be positive. Which tool is best suited to automatically flag these values without altering the underlying data?

Accepted Answer

Conditional Formatting can apply a visual rule (such as a red fill) to any cell whose value is less than 0, immediately surfacing out-of-range figures without changing the actual data — appropriate for identifying data outside defined standards. Data Validation restricts what can be typed going forward but does not flag existing values already in the sheet in the same visual way. TRIM removes extra spaces from text, and Freeze Panes only affects scrolling behavior.

Question 7

A financial analyst is comparing this year's revenue, cost of goods sold, and net income to last year's figures to see how much each line item grew or shrank in dollar and percentage terms. Which type of analysis are they performing?

Accepted Answer

Horizontal analysis compares the same line items across two or more periods (such as this year versus last year) to evaluate dollar and percentage change over time. Vertical analysis instead expresses each line item as a percentage of a base figure within a single period (such as revenue). Ratio analysis calculates relationships between figures like liquidity or profitability ratios, and time value of money analysis evaluates cash flows across time using discounting, not simple period comparison.

Question 8

An analyst wants to express every line item on the income statement as a percentage of total revenue, to see, for example, what portion of revenue is consumed by cost of goods sold in the current period. Which type of analysis should they perform?

Accepted Answer

Vertical analysis expresses each line item on a financial statement as a percentage of a base amount within the same period, such as total revenue on an income statement, making it easy to see the relative proportion each item represents. Horizontal analysis instead compares the same item across multiple periods. Variance analysis compares actual results to a budget or benchmark, and trend forecasting projects future values, neither of which describes expressing items as a percentage of a base within one period.

Question 9

A budget analyst has budgeted marketing spend of $50,000 for the quarter, and actual spend came in at $58,000. Which formula correctly calculates the budget versus actual variance as a percentage over budget?

Accepted Answer

Percentage variance is calculated as (Actual - Budget) / Budget, so (58000-50000)/50000 correctly returns 0.16, meaning spending was 16% over budget. Option B divides by the actual figure instead of the budget baseline, producing an incorrect percentage. Option C returns a ratio, not a variance percentage, and option D returns only the raw dollar difference, not a percentage.

Question 10

A company's target gross margin benchmark is 40%. An analyst has calculated the actual gross margin for five product lines and needs to quickly identify which product lines fall more than 5 percentage points below that benchmark. What is the most efficient way to do this in Excel?

Accepted Answer

An IF formula (or a Conditional Formatting rule) that tests whether a margin is less than 35% (40% benchmark minus 5 points) automatically flags every product line that differs from the benchmark by more than the allowed threshold, which is far faster and less error-prone than manual inspection. Deleting rows destroys data, and alphabetical sorting does not surface which values are out of range.

Question 11

An analyst needs to calculate the current ratio (current assets divided by current liabilities) for a company to assess short-term liquidity. Current assets are in cell B2 and current liabilities are in cell B3. Which formula correctly calculates this ratio?

Accepted Answer

The current ratio is defined as current assets divided by current liabilities, so =B2/B3 correctly computes it, indicating how many dollars of current assets exist for every dollar of current liabilities. Dividing in the reverse order (B3/B2) would compute a different, incorrect ratio. Subtraction gives working capital in dollars rather than a ratio, and multiplication has no meaning in this context.

Question 12

An analyst wants to know the present value today of $10,000 to be received in 5 years, assuming an annual discount rate of 6%. Which Excel function is designed to calculate this time value of money figure directly?

Accepted Answer

The PV function calculates the present value of a future sum given a rate and number of periods, so =PV(6%,5,0,-10000) returns the value today of $10,000 received in 5 years at a 6% discount rate. FV instead calculates a future value from a present amount, NPER solves for the number of periods, and PMT calculates a periodic payment amount, none of which answer the 'present value of a future amount' question being asked.

Question 13

A forecast model returns #REF! in a cell that used to calculate net income correctly. The analyst suspects a row referenced by the formula was deleted. Which Excel feature would best help them trace exactly which cells the broken formula depends on and pinpoint the error's source?

Accepted Answer

Formula Auditing tools such as Trace Precedents and Trace Dependents draw arrows to the cells a formula relies on (or that rely on it), and Error Checking can walk through the logic step by step to identify exactly where a #REF! error originates, such as a deleted referenced row. Freeze Panes only affects scrolling and has no auditing function, Data Validation restricts input rather than diagnosing formulas, and Conditional Formatting only changes cell appearance.

Question 14

A loan officer needs to calculate the monthly payment on a $200,000 loan with a 6% annual interest rate amortized over 30 years (360 monthly payments). Which function is designed specifically to calculate this periodic payment?

Accepted Answer

PMT calculates the fixed periodic payment required to pay off a loan given a rate, number of periods, and present value, so =PMT(6%/12, 360, -200000) correctly returns the monthly payment on the loan (dividing the annual rate by 12 to match the monthly period count). PV calculates a present value rather than a payment, RATE solves for an interest rate, and NPER solves for the number of periods — none of these directly compute a payment amount.

Question 15

A financial analyst wants to build a table showing, for each month of a car loan, the payment split into interest paid, principal paid, and remaining balance. Which two functions are typically combined with PMT to build this kind of schedule?

Accepted Answer

IPMT calculates the interest portion of a given payment period and PPMT calculates the principal portion; used together with PMT for the fixed total payment, they let you build a full loan amortization schedule showing interest, principal, and the declining balance for each period. SLN and DB are depreciation functions unrelated to loan amortization, and NPV/IRR evaluate investment cash flows rather than break a payment into interest and principal components.

Question 16

A business owner is deciding between two equipment loans: Loan A has a lower interest rate but a longer term, and Loan B has a higher interest rate but a shorter term. They want to compare total interest paid and the monthly cash flow impact of each option before choosing. What is the best approach in Excel?

Accepted Answer

Building a full amortization schedule for each loan (using PMT for payment, IPMT/PPMT to split interest and principal per period) allows the owner to sum total interest paid over the life of each loan and directly compare monthly cash flow impact — a true apples-to-apples scenario analysis. Comparing rates alone ignores the effect of term length on total interest paid. Conditional Formatting only visualizes existing numbers rather than calculating them, and FV of the principal does not reflect loan repayment cash flows at all.

Question 17

An investment analyst needs to calculate the price of a corporate bond with a $1,000 face value, a 5% annual coupon rate, paid semi-annually, given a specified settlement date, maturity date, and yield. Which Excel function is designed specifically for this calculation?

Accepted Answer

The PRICE function is purpose-built to calculate the price per $100 face value of a security that pays periodic interest, given settlement date, maturity date, coupon rate, yield, redemption value, and payment frequency — exactly the bond pricing scenario described. PV is a generic time-value function that does not account for bond-specific conventions like settlement/maturity dates. NPV and IRR evaluate a series of cash flows for investment decisions but are not designed for date-based bond pricing.

Question 18

An analyst is building a simple stock valuation model using the dividend discount approach, and separately wants to compute the yield to maturity of a bond the company holds. Which two Excel functions are appropriate for these two respective tasks? Choose 2.

Accepted Answer

NPV discounts a series of projected cash flows (such as expected future dividends) back to a present value, forming the basis of a simple dividend discount valuation model. YIELD calculates a bond's yield to maturity from its price, coupon rate, and maturity date, which is exactly what's needed for the bond yield task. VLOOKUP is a lookup function unrelated to discounting, and neither TEXT (text formatting) nor SUMIF (conditional summation) perform yield or valuation calculations.

Question 19

A treasury analyst has a $5,000 loan balance in cell B1, a monthly interest rate in cell B2, and wants to know how many months it will take to pay it off given a fixed monthly payment in cell B3. Which function correctly solves for the number of periods?

Accepted Answer

NPER solves for the number of payment periods given a rate, a payment amount, and a present value, so =NPER(B2, -B3, B1) correctly calculates how many months are needed to pay off the loan balance. PMT instead calculates the payment amount (which is already known here), RATE solves for an interest rate, and FV calculates a future value — none of which answer 'how many periods' given the other known values.

Question 20

An analyst has two years of monthly sales history and wants Excel to automatically generate a projected trend line for the next 12 months, including a visual chart, using built-in exponential smoothing. Which feature should they use?

Accepted Answer

Data > Forecast > Forecast Sheet uses historical data and exponential smoothing (the AAA version of the ETS algorithm) to automatically generate a forecast worksheet with a chart and confidence interval bounds for future periods. PivotChart summarizes existing data rather than projecting future values, Conditional Formatting only changes visual appearance, and Define Name simply creates a named range with no forecasting capability.

Question 21

A financial planner needs to project next year's revenue by applying a compounding 4% monthly growth rate to the current month's revenue in cell B2, filled across the next 12 months. Which formula, entered in cell C2 and filled right, correctly compounds the growth?

Accepted Answer

Multiplying the prior period's value by (1 + growth rate), i.e., =B2*1.04, correctly applies a 4% compounding growth rate to project the next period's revenue. Adding 0.04 would only add four cents rather than scaling the value by a percentage. Multiplying by just 0.04 would shrink the revenue to 4% of its value instead of growing it, and subtracting 1.04 produces a meaningless negative adjustment.

Question 22

A finance manager wants to see how projected net income changes under three different scenarios: best case, base case, and worst case, each using different assumptions for sales growth and cost inflation, without creating three separate copies of the model. Which Excel feature is best suited to this task?

Accepted Answer

Scenario Manager, found under Data > What-If Analysis, lets you define multiple named sets of input values (such as best case, base case, worst case) for the same model and switch between them to see the resulting outputs, all within a single workbook. Goal Seek instead works backward from a single desired result to find one input value, Text to Columns splits text data, and Remove Duplicates deletes repeated rows — none of which support comparing multiple named scenarios.

Question 23

A treasury team is building a rolling 13-week cash budget and needs each week's ending cash balance to carry forward as the next week's beginning cash balance. Which approach best supports this projection?

Accepted Answer

Linking each week's beginning cash balance formula directly to the prior week's calculated ending balance cell ensures the rolling projection updates automatically and consistently as assumptions change, which is the standard approach for building a cash budget. Manually retyping values is error-prone and does not update dynamically. Conditional Formatting only affects appearance, and a PivotTable summarizes existing data rather than carrying forward a running balance across projected periods.

Question 24

A finance director wants a single-page dashboard summarizing revenue, expenses, and net income trends for executives, combining multiple chart types (such as a column chart for revenue and a line for net income trend) in one visual. Which Excel charting approach best supports this?

Accepted Answer

A combo chart lets you plot two or more chart types (for example, columns for revenue and a line for net income trend) on the same set of axes, which is well suited to an executive dashboard summarizing multiple related metrics at once. A pie chart can only show one category breakdown at a time and cannot show a trend, a plain data table provides no visual summary, and sparklines are tiny in-cell trend indicators not meant to replace a full dashboard chart.

Question 25

An analyst built a dashboard chart, but a colleague using a screen reader reports they cannot understand what the chart shows, and a colorblind reviewer cannot distinguish two of the data series that rely solely on similar-looking red and green colors. What should the analyst do to improve the chart's accessibility?

Accepted Answer

Adding meaningful alt text allows screen readers to describe the chart's purpose and content to users who cannot see it, and choosing a color palette that doesn't rely solely on red/green differentiation (or adding distinct patterns/markers) resolves the colorblindness issue — both are standard practices for evaluating and improving chart accessibility and usability. Ignoring the feedback leaves the chart unusable for those users. Increasing font size alone does not address either underlying issue, and converting the chart to a picture with no text would make it even less accessible to screen readers.

MO-230 — Microsoft Office Specialist: Excel for Business Finance Associate