This question's explanation and your answers so far.
問96
頻出度★★☆
A さんは，製品 N の売上の動向を分析するため，昨年と今年の売上高の推移を基に，表計算ソフトを用いて図1のワークシートを作成した。
図1の D 列の移動合計欄には，当該月を含む過去12か月の売上の合計を，E 列の売上累計欄には今年の売上高の累計を計算する式が入力されている。また，製品 N の売上の動向を視覚的に確認するため，図2の売上高の推移グラフを作成した。
A さんは売上傾向の分析と経済状況の動向から，製品 N の来年の年間売上高を今年の 10%増と見込んだ。また，月ごとに売上目標を定めて目標達成を管理するため，次の図のように，各月の売上傾向から目標売上高を計算する次のワークシートを作成した。
C14+(D14*0.1) applies the 10% to the wrong figure. The basis for the 10% increase is this year's annual sales, C14, so the increase must be C14*0.1. D14 is the annual total of the averages of last year and this year (65,450 thousand yen), not this year's sales, so this formula gives 70,300+6,545 = 76,845 thousand yen, which is 485 thousand yen less than the correct 77,330 thousand yen (= 70,300*1.1). It is a typical mistake of mixing up values from different columns.
○イ
正解The correct answer is イ 「C14*1.1」. This worksheet is built in the following order: first, column D gives the average of last year's and this year's sales for each month (D2=(B2+C2)/2); next, column E gives each month's ratio to the annual total D14, that is, the monthly sales ratio (E2=D2/D$14); finally, next year's annual target sales are distributed over the months according to those ratios to give the monthly target sales in column F (F2=F$14*E2). In other words, F14 is next year's annual target sales itself, the base amount that the distribution starts from, and unless it is fixed first, F2 through F13 cannot be calculated. Next year's annual sales are projected to be 10% higher than this year's, so it is enough to multiply cell C14 (70,300 thousand yen), which holds this year's annual sales, by 1.1: F14 = C14*1.1 = 77,330 thousand yen. Two points lead you to イ: a 10% increase means “1.1 times this year's figure”, and the cell to refer to is C14, this year's annual total.
×Incorrectウ
C14*E14 does not reflect the 10% increase. The formula 「D2/D$14」 in E2 is copied down to E3 through E14, so E14 becomes D14/D$14 = 65,450/65,450 = 1 (naturally, the ratios add up to 1). Therefore C14*E14 = 70,300*1 = 70,300 thousand yen, which is simply this year's annual sales and cannot serve as next year's target. Overlooking that the relative reference in E2, once copied down the rows, becomes 1 at E14 is what leads to this choice.
×Incorrectエ
SUM(F2:F13) would create a circular reference (circular definition). Cells F2 through F13 are calculated by referring to F14, as in 「F$14*E2」, so putting the sum of F2 through F13 into F14 means that F14 is needed in order to obtain F14 — an endless loop in which no value can be determined. Numerically the sum of F2 through F13 does equal F14, because the ratios add up to 1, but as a formula it does not hold, and spreadsheet software reports a circular reference error.
ここがポイント
In spreadsheet formula questions, the basic move is to trace what each column represents and in what order the cells are calculated (the dependencies). Here the flow is column D (averages) → D14 (annual total of the averages) → column E (monthly sales ratios) → F14 (next year's annual target) → column F (monthly targets), and F14 is not a result derived from other cells but the input value that the distribution starts from. Since F2 through F13 refer to F$14, any formula that derives F14 from F2 through F13 becomes a circular reference and cannot be used.
“10% higher than this year” means 1.1 times this year's figure (the original value plus the original value × 0.1), and what you multiply must always be the baseline figure for this year (here, this year's annual sales C14). Also, a relative reference without $ shifts its row and column when copied, so copying D2/D$14 from E2 down to E14 gives E14 = D14/D14 = 1. It is important to build the habit of writing out concretely what each cell contains after copying and checking it.