Spreadsheet Based Decision Support Systems - Business Decision Making - ثاني ثانوي

LO Chapter 2 10 E 3 55 Decision Support System Fundamentals When running a business, a lot of information is generated about budgets, sup- pliers, cost of production, customers, sales, and much more. Before this data is processed, the business needs to decide on its objectives: what does it hope to achieve, what information does it need to guide its decisions, and how will it measure success? Businesses use key performance indicators (KPIs) to set goals and track progress. This is when data can be processed so that the business owners can make informed decisions. A decision support system (DSS) is a computer-based application that assists busi- ness decision-makers who deal with unstructured problems. A DSS can represent or "model" the outcomes of different decisions, so that decision makers can test different scenarios before deciding on the best solution to a problem. Some DSS programs even make recommendations on the best solution to a problem. LEARNING OBJECTIVES Once you have completed this chapter, you should be able to: CAMER Q 1 Explain why business decision makers need to model decisions 2 Apply the principles of modeling to follow a decision-making process 3 Explain the decision-making process using relevant computer programs 4 Test what you've learned by using Scenario Manager وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 351 Decision Support System Fundamentals 351 30/06/2023 14:28

Lesson 1 Chapter 10 www.ien.edu.sa Spreadsheet-Based Decision Support Systems Businesses use decision support systems (DSS) to help them understand how different decisions will impact the future of the business. DSS are designed to model different scenarios so that decision makers can understand and better anticipate what may or may not happen. There are three types of models: • Descriptive models describe a system or a process, providing a simple overview of how something is currently working. Predictive models use mathematics to examine and estimate how future events or outcomes might be affected by certain conditions and decisions. Prescriptive models are designed to recommend a course of action for a business by examining detailed data and identifying the options that best support the desired outcome. In this book we will look at predictive data models. DSS can be built using computing languages like C++, or spreadsheet software like Excel, Google Sheets, or Power Bl. Once a DSS has been developed, the information contained within it can be used to generate reports, scorecards, and interactive dashboards, like the example in Figure 10-1. Spreadsheets offer built-in arithmetic, statistical, and financial functions to sup- port the data analysis. Because spreadsheets provide a convenient, flexible, and easily accessible platform for most businesspeople, this book will focus on using Microsoft Excel. As you will see, many kinds of problems, such as those in accounting, operations, and finance, can be modeled using spreadsheets. 352 10 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 352 DEFINITIONS Dashboard: A window that graphically summarizes information about how a business is operating. Data: A collection of uninterpreted information. 30/06/2023 14:28

DEFINITIONS Decision support system (DSS): Interactive software designed to help you compile useful information from raw data, documents, and business knowledge. Model: (n.) A collection of information that shows how an organization is working, or could be working in the future; (v.) to produce such a collection of information. QUICK TIP Companies will often use a timeframe in which certain goals should be reached, sometimes over several years. Each year is then referred to as Year 1, Year 2, etc. Business decision makers and managers use key performance indicators (KPIs) to track their progress against predetermined objectives. Imagine it is Year 1 of a three-year period, and you are trying to build a model of a company's net income (the amount of money a business has left over after it has paid its expenses) and cash flow (the total amount of money coming into or going out of the business) for Years 2 and 3 successively. DEFINITION Key performance indicator (KPI): A measure used to evaluate the success of an organization in achieving its planned objectives. FIGURE 10-1: Dashboards are a great way to compile visual representations of the data you have modeled and created Analysis Month Year 25% Analysis Mon Tue Wed Thu Fri Sat 100 01 Month Year Analysis Month Year Analysis Month Year Analysis Month Year Daily charts 500 02 03 04 400 50% 75% 100% January February March April Analysis Month Year 01 100 وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 353 100 100 300 200 100 100 15:00 01:00 11:00 22:00 04:00 15:00 01:00 11:00 Direction Circle Time Distance 1 ITEM 01 21 9:99 209,79 2 ITEM 02 4 5:5 22 3 ITEM 03 5 20 100 01 01 01 Daily charts Type of Load Month v Day Year ✓ Position 04 Position 05 May June July August September October November Activity Analysis Month Year Position 03 O SAR 9600 Direction Circle Time 1 ITEM 01 21 9:99 Activity 209,79 Activity 21 Activity 209,79 ЄO SAR 960 2 ITEM 02 4 5:5 22 4 22 3 ITEM 03 5 20 100 5 100 25% 50% Position 01 Position 02 Month Year 100% Decision Support System Fundamentals 353 30/06/2023 14:28

354 10 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 354 1-1 Forecasting You need to forecast net income and cash flow in those years. This is because decision makers in the business need to know how much money they might make and whether they will meet their KPIs. DEFINITION Forecast: A way to predict a future outcome, using relevant data to inform your prediction. The business is likely to use your forecast to make decisions, so the estimates need to be as accurate as possible. Of course, no one can fully predict the future, so you need to base your estimates on the best and most relevant information available. You decide that the estimates should be based on three factors: . . Year 1 results: You choose these because they accurately show how the business has performed so far and are a good indication of how Years 2 and 3 will perform. Estimates of the underlying economic variables: We can estimate how an economy might behave based on relevant economic indicators. The cost of the products the company sells: Knowing how much a product costs to make, relative to its selling price, is crucial to under- standing how much profit can be made. Your model will use an income statement and cash flow framework-business indicators in this case. The user can enter values for two possible states of the economy in Years 2 and 3: an "O" for an optimistic outlook or a "P" for a pes- simistic outlook. In this example, we will assume that the economic outlook for both years is optimistic. DEFINITIONS Optimism: The belief or hope that things that happen in the future will be good, positive, and useful. Pessimism: The opposite of optimism; the belief that events in the future will be bad, unhelpful, and negative. The state of the economy is expected to affect the number of units the company can sell, as well as each unit's selling price. In an optimistic economy, more units can be sold at a higher price. Your model needs to take account of the way 30/06/2023 14:28

QUICK TIP "What-if Analysis" is a popular application for spreadsheet models. The decision maker can change the different inputs of the model and see how these changes affect the outcomes. They can try out different options before committing to a particular course of action. things like economic conditions will affect demand for your products and what you will be able to sell them for. The decision maker should be able to enter values into your model to see two possible trends in the cost of goods sold, entering "U" for up or "D" for down. A "U" means that the cost of an item sold will be higher than it was in Year 1; a "D" means it will cost less. The decision maker can then use the DSS to play "what if?" with the input variables, changing them to model different scenarios so they can see the effect this will have on net income and cash flow. These variables will be used in equations with the constants. DEFINITIONS Input variable: A data point that the decision maker can change or influence. For example, the quantity of raw materials purchased, etc. Constant: A data point in a DSS that is known in advance and will not change, or that the decision maker has no influence over. For example, Lease Payments-these are already established and so are a constant for the purposes of the decision. www.ien.edu.sa وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 355 The user of the forecast can ask these questions: . • What if the economy is good and costs go up? What will net income and cash flow be in that case? What will happen if the economy is bad and costs go down? What will be the company's net income and cash flow in that case? With a properly designed spreadsheet software model, the answers to such questions can quickly be found. Scan the QR code to access the workbook (Chapter 10 data file 1.xlsx). This workbook will be used in this lesson and Lesson 2. Before starting the work in this chapter, or if you have forgotten how to work with Microsoft Excel, you can use the second QR code to access a lesson designed to refresh your basic spreadsheet skills. Decision Support System Fundamentals 355 30/06/2023 14:28

DEFINITIONS Formula: A mathematical expression of a calculation that your spreadsheet software will complete for you. Function: A pre-defined set of formulas that work together to calculate a commonly-used operation. For example, =Average(), to calculate the mean of a set of values. The function is much simpler than using all of the individual formulas that would be required to find the average. QUICK TIP In the spreadsheet, "NA" means that a cell will not be used in any formula. The Year 1 values are only needed for certain calculations, so most of Year 1's cells show NA- which stands for "not applicable." FIGURE 10-2: The spreadsheet being used by the DSS AutoSave Off Search File Arial Data Home Insert Page Layout Formulas A A Review View Automate Help Acrobat 11 Wrap Text General Paste BIU 4 Merge & Center ▾ K-%9 Alignment Number Undo Clipboard Font J27 fx A 2 B C D E 1 Chapter 10 Lessons 1 and 2 2 3 Constants Year Year 2 4 Overhead expense rate NA 0.33 Year 3 0.35 5 Number of business days NA 300 300 6 7 Inputs Year 1 Year 2 Year 3 8 Economic outlook (O=Optimistic, P=Pessimistic) NA 9 Purchase price outlook (U=Up, D=Down) NA 10 11 Summary of key results Year Year 2 Year 3 12 Net income NA 13 14 End-of-year cash on hand 15 Calculations 16 Number of units sold in a day 17 Selling price per unit NA Year 1 Year 2 Year 3 1000 26 18 Cost of goods sold per unit 11 19 Number of units sold in a year NA 20 21 Income statement and Cash flow statement Year Year 2 Year 3 22 Beginning-of-year cash on hand NA 32 Sales (revenue) NA QUICK TIP When using a Constant or Input value in a calculation you should ALWAYS use a cell reference to point to these values (e.g., =C4). NEVER retype the hard value of constants or inputs in other cells in your spreadsheet. These values should only appear ONCE! The spreadsheet (Figure 10-2) contains the data we will use for the DSS in this chapter. It contains the following sections: 1. Constants This section of Figure 10-2 records values that are used in spreadsheet calcu- lations. The constants are the input values used by the model, and they do not change. The constants for this business are Overhead expense rate and Number of business days. Overhead expenses are ongoing costs that a business incurs that aren't direct costs or the cost of goods sold. Expenses like rent, insurance, employees' salaries, etc. are all necessary for the business to function. Many companies forecast overhead by expressing it as a percentage of revenue. 356 10 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 356 30/06/2023 14:28

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 357 2. Inputs The inputs shown in Figure 10-2 are for the economic outlook and purchase price outlook (the outlook for input costs). Inputs can be entered for each year. The model covered here is for Years 2 and 3. It will let you enter an "O" (for optimistic) for Year 2's economy in one cell and a "P" (for pessimistic) for Year 3's economy in another cell. Alternatively, one input (of "O" or "P") for the two-year period can be entered in one cell, if the decision maker wants to model a scenario with an optimistic or pessimistic outlook for both years. 3. Summary of business indicators This section of the spreadsheet captures Years 2 and 3 Net income and End- of-year cash on hand, which are the two outputs in this model. The sum- mary repeats the results in one easy-to-see location. Collecting business indicators in one place also makes for easier charting, which can be done using the Charts menu, under the Insert menu. 4. Calculations This area is used to compute the following data: the number of units sold in a day the selling price per unit the cost of goods sold per unit . the number of units sold in a year. FIGURE 10-3: The Income statement and Cash flow statement in this lesson's spreadsheet AutoSave off Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Arial 10 A A ab Wrap Text Genera Paste BIU 3 3 A Merge & Center Undo Clipboard E Font E Alignment ES 141 fx A D 21 Income statement and Cash flow statement 22 Beginning-of-year cash on hand 23 Sales (revenue) 25 Gross income 24 Cost of goods sold 26 Overhead expense 27 Net income 28 End-of-year cash on hand 29 30 B C Year 1 Year 2 Year 3 NA NA NA NA NA NA SAR 37,500.00 Decision Support System Fundamentals 357 30/06/2023 14:28

QUICK TIP With the insertion point in C12, the cell's contents―in this case, the formula |=C27-appears in the editing window above the lettered column indicators, as shown in Figure 10-4. 5. Business Indicators This section (shown in Figure 10-3) is the "body" of the spreadsheet. It shows: the beginning-of-year cash on hand . sales (revenue) the cost of goods sold, which is the units sold during the year multiplied by the price paid to acquire or make the unit sold gross income before expenses overhead expenses, which is the income before overhead net income after expenses end-of-year cash on hand. 1-2 Constructing the Spreadsheet Model You already have the spreadsheet (see the QR code on page 355), so you can use this to get started with the formulas. You can enter formulas in any order you choose, but because it is best to enter the simple formulas before moving on to more complex ones, we will start with these. 1. Entering the "Simple" Formulas STEP 1 The Summary of business indicators section (see Figure 10-4) echoes results shown in the Cash flow statement. Consider Figures 10-2 and 10-3, and notice that cell C27 in Figure 10-3 contains the Year 2 Net income. You need to echo that amount to cell C12, so the formula in C12 is =C27 (as shown in Figure 10-4). FIGURE 10-4: Echoing Year 2 Net income to Summary of business indicators section AutoSave File: Search Home Insert Page Layout Formulas Data Review View Automate Help Acrobat X Aria - Paste BIU-- Undo Cipboard Fort C12 fx-C27 199 AA M = A- Б Wrap Texe Text Merge & Center- Migment 9 AutoSave HO Search File Arial Home Insert Page Layout Formulas Data X Review View Automate Help Acrobat A A M = -> Cond Formu Paste BIU- や A- Wrap Texe Merge & Center Currency % Number Undo Cipboard Fort Б Migment Б Number 5 C12 fx-C27 B D 12 Net income 11 Summary of business indicators 13 End-of-year cash on hand 14 15 Calculations Year 1 NA NA Year 2 Year 3 <-027 Year 1 ber of units sold in a day 1000 B D 11 Summary of business indicators 12 Net income 13 End-of-year cash on hand Year 1 Year 2 Year 3 NA SAR 0.00 NA 14 Year 2 Year 3 15 Calculations Year 1 Year 2 Year 3 ber of units sold in a day 1000 358 10 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 358 Cond Formu 30/06/2023 14:28

QUICK TIP If the destination for your copy is a range of contiguous cells, select the upper-left cell of the destination range and paste (Ctrl+V). Cell C27 has a zero value, but you can still copy the formula in the cell. Copy cell C12's formula to cell D12. Copying puts =D27 into D12. Year 3's Net income is in D27, as shown in step 2. STEP 2 To perform the copy operation (as shown in Figure 10-5), use the following steps: Click in the cell or range of cells that you want to copy. Press Ctrl+C. Select the destination cell. Press Ctrl+V. Press the Escape key to deactivate the copied cell or range. FIGURE 10-5: Performing the copy operation AutoSave C Search CE AutoSaw off Search Film Home Insert Page Layout Formulas Data Review View Automate Help Acrobat File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat X X Arial 12 A A M= Parte BIU- Wrap Text Merge & Certer Currency Arial AA -% Cond Forma BIU- Unde Cpboard Fiv Fort Agriment Number Б Und Cheboard Forn Wrap Tec Merge & Center Currency - %> Algemant Mather 11 Cond Forma D12 مگر 012 fx -027 11 Summary of business indicators 12 Net income 13 End-of-year cash on hand Year 1 Year 2 D Year 3 E NA SAR 0.00 NA 11 Summary of business indicators 12 Net income 13 End-of-year cash on hand 14 15 Calculations Year 1 Year 2 Number of units sold in a day 1000 B Year 1 Year 2 D Year 3 E NA SAR 0,00 SAR 0.00 NA 14 Year 3 15 Calculations Year 1 Year 2 Year 3 Number of units sold in a day 1000 وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 359 Select the cell(s) you want to copy and press Ctrl+C. Press Ctrl+V to paste the data into the destination cell(s). As you can see in Figure 10-6, End-of-year cash on hand for Year 2 is echoed to cell C13. Echo the cash results in cell C28 to cell C13. (Enter the formula =C28 in cell C13, as shown in Figure 10-6.) Copy the formula from C13 to D13. Decision Support System Fundamentals 359 30/06/2023 14:28

360 10 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 360 FIGURE 10-6: Echoing Year 2 End-of-year cash on hand to Summary of business indicators section File AutoSave Off Search Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Arial 12 A A 三 Paste BIU Undo Cipboard Font C13 fx -C28 Wrap Text Currency Merge & Center K4-% Alignment Number Conditio 00-0 Formatti B 11 Summary of business indicators Year 1 Year 2 D Year 3 12 Net income NA SAR 0.00 13 End-of-year cash on hand NA SAR 0.00 SAR 0.00 SAR 0.00 14 15 Calculations Year 1 Year 2 Year 3 16 Number of units sold in a day 1000 17 Selling price per unit 26 18 Cost of goods sold per unit 11 19 Number of units sold in a year NA 20 21 Income statement and Cash flow statement Year 1 Year 2 Year 3 22 Beginning-of-year cash on hand 23 Sales (revenue) 24 Cost of goods sold NA NA NA 26 25 Gross income Overhead expense 27 Net income NA NA NA 28 End-of-year cash on hand SAR 37,500.00 29 30 21 As you can see in Figure 10-7, Beginning-of-year cash on hand is the cash on hand at the end of the prior year. Cell B28 has the End-of-year cash on hand for Year 1. This is because businesses always start the new year with the cash. on hand from the previous year. • In cell C22 for Year 2, type =B28, as shown in Figure 10-7. FIGURE 10-7: Echoing Year 1 End-of-year cash on hand to Year 2 Beginning-of-year cash on hand File AutoSave Off Search Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Arial Paste BIU Undo Clipboard Font C22 Xfx =828 A 10 AA Wrap Text Currency v = 1 Merge & Center - % Alignment Num B 22 Beginning-of-year cash on hand 21 Income statement and Cash flow statement 23 Sales (revenue) Year 1 NA NA 24 Cost of goods sold NA 25 Gross income NA 26 Overhead expense NA 27 Net income NA 28 End-of-year cash on hand SAR 37,500.00 29 C Year 2 SAR 37,500.00 Year 3 30/06/2023 14:28

QUICK TIP A fill handle is a small, square marker in the bottom corner of an active spreadsheet cell or cells, which allows you to copy the cell(s) formula to other adjacent cells. Copy the formula in cell C22 to cell D22. Click on the fill handle in the lower right corner of cell C22 and drag it to cell D22. In cell C23, enter =C17*C19, as shown in Figure 10-8. The formula =C17*C19 multiplies Selling price per unit by the Number of units sold in a year. (Cells C17 and C19 are empty now, which is why Sales shows a zero after the formula is entered.) Copy the formula from C23 to cell D23. FIGURE 10-8: Entering the formula to compute Year 2 sales AutoSave aff وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 361 . Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat 2. Arial 10 V A A Wrap Text Currency Paste BIU v v == Merge & Center - % Undo Clipboard Font E Alignment E Num C23 fx =C17*C19 A 21 Income statement and Cash flow statement 22 Beginning-of-year cash on hand 23 Sales (revenue) 24 Cost of goods sold 25 Gross income 26 Overhead expense 27 Net income 28 End-of-year cash on hand 29 B Year 1 NA NA NA NA NA NA SAR 37,500.00 C Year 2 D Year 3 SAR 37,500.00 SAR 0.00 SAR 0.00 In C24, enter =C18*C19, which equals Cost of goods sold per unit multiplied by Number of units sold in a year. Copy the formula to D24. In cell C25, the formula for Gross income (Sales or revenue minus the cost of goods sold) is =C23-C24. Enter the formula and copy it to D25. In this example, the accountants want to use an estimated amount for over- head expenses in these forecasts. They suggest that overhead will be approx- imately 33% of gross income in Year 2 and 35% in Year 3. Overhead expense (C26) is calculated as the product of Gross income and the Overhead expense rate (=C25*C4). In cell C27, Net income (Gross overhead minus overhead expense) =C25-C26. Enter the formula and then copy it to cell D27. The End-of-year cash on hand is the Beginning-of-year cash on hand plus Net income. In cell C28, enter =C22+C27. Copy the formula to cell D28. The Income statement and Cash flow statement section at this point is shown in Figure 10-9. Decision Support System Fundamentals 361 30/06/2023 14:28

FIGURE 10-9: Status of Income statement and Cash flow statement Search Review View Automate Help Acrobat AutoSave Off File Home Insert Page Layout Formulas Data Arial 10 Α' Α Paste BIU 田 - A 3 Undo Clipboard CE Font D28 fx =D22+D27 21 Income statement and Cash flow statement 22 Beginning-of-year cash on hand 23 Sales (revenue) 24 Cost of goods sold 25 Gross income 26 Overhead expense 27 Net income 28 End-of-year cash on hand 229 30 12 DE DE ab Wrap Text Currency Merge & Center 3 %9 Alignment E Number B Year 1 Year 2 Year 3 NA SAR 37,500.00 SAR 37,500.00 NA SAR 0.00 SAR 0.00 NA SAR 0.00 SAR 0.00 NA SAR 0.00 SAR 0.00 NA SAR 0.00 SAR 0.00 NA SAR 0.00 SAR 0.00 SAR 37,500.00 SAR 37,500.00 SAR 37,500.00 2. Entering the "Complex" Formulas The next step is to finish the spreadsheet by filling in the "complex" formulas. STEP 1 In cell C8, enter an "O" for Optimistic economy, and in C9, enter a "U" for Up meaning, for example, that purchase prices will be high. There is nothing special about these values—they just give the worksheet formulas input values to pro- cess. In this example, the inputs will cover both Years 2 and 3. Enter =C8 in cell D8 and Enter =C9 in cell D9. This automatically copies the value of Year 2 to Year 3, as shown in Figure 10-10. FIGURE 10-10: Copying data from the previous year AutoSave Off 362 10 Chapter رة ا Ministry of Education 2024-1446 Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Arial 10 AA Paste BIU 3 3 Wrap Text Merge & Center General 17-%9 12 Alignment ESA Number Business Decision Making S1 S2 S3.indb 362 Undo Clipboard Б Font H13 fx A 7 Inputs 8 Economic outlook (O-Optimistic, P=Pessimistic) 9 Purchase price outlook (U-Up, D-Down) 10 B D Year 1 Year 2 Year 3 NA 0 =C8 NA U =C9 Remember that cell addresses in the Calculations section are already being referred to in formulas in the Income statement and Cash flow statement section. 30/06/2023 14:28

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 363 For Years 2 and 3, you need to change the format of the Number of units sold in a day and the Number of units sold in a year cells. With the Number of units sold in a day and the Number of units sold in a year cells selected, open the Format Cells dialogue box by right-clicking with your mouse (see Figure 10-11). FIGURE 10-11: The right-click menu contains the Format Cells dialogue box Search the menus X Cut Copy Paste Options: Paste Special... Smart Lookup Insert... Delete... Clear Contents Quick Analysis Filter Sort Get Data from Table/Range... New Comment New Note Format Cells... Pick From Drop-down List... Define Name... Link Under the Number tab, make sure the number of decimal places is set to 0. This is because you cannot sell a part or fraction of a unit. Click OK. Decision Support System Fundamentals 363 30/06/2023 14:28

FIGURE 10-12: Setting the number of decimal places to 0 CE AutoSave Off Search Conditional Format as Cell Formatting Table Styles Number HE Styles File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Arial 12 A A Paste BIU 3 A DI GH Wrap Text General Merge & Center S % Undo Clipboard 151 Font ˊˋˊ 1 Alignment E B17 XVfx 26 A Format Cells 7 Inputs 8 Economic outlook (O-Optimistic, P=Pessimistic) Number Alignment Font Border Fill Protection 9 Purchase price outlook (U-Up, D=Down) Category: 10 General 11 Summary of business indicators 12 Net income Number Sample 26.00 Currency Accounting 13 End-of-year cash on hand 14 15 Calculations 16 Number of units sold in a day 17 Selling price per unit 18 Cost of goods sold per unit 19 Number of units sold in a year 20 Date Time Fraction Scientific 1234.10 Text 1234.10 Special Custom -1234.10 -1234.10 Percentage Decimal places: 2 Negative numbers: Use 1000 Separator () 21 Income statement and Cash flow statement 22 Beginning-of-year cash on hand 23 Sales (revenue) 24 Cost of goods sold 25 Gross income Overhead expense 26 27 Net income 28 End-of-year cash on hand 29 30 31 32 32 364 10 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 364 F G ? Number is used for general display of numbers, Currency and Accounting offer specialized formatting for monetary value. OK Cancel . Next, you need to make sure that the Selling price per unit and Cost of goods sold per unit use decimal places for Years 2 and 3. This is because these figures are in the format of a currency. With the Selling price per unit and Cost of goods sold per unit cells selected, open the Format Cells dialog box by right-clicking with your mouse which (will result in the window shown in Figure 10-12). This time, make sure that the number of decimal places is set to 2. Click OK. STEP 2 Enter formulas for the calculations. Next we will work on the formula for the Number of units sold in a year, which is the Number of business days (shown in C5) multiplied by the Number of units sold in a day (shown in C16). In C19, enter =C5*C16, as shown in Figure 10-13. 30/06/2023 14:28

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 365 FIGURE 10-13: Entering the formula to compute Year 2 Number of units sold in a year Search (Alt+Q) AutoSave On File Home Insert Page Layout Formulas Data Review View Help Acrobat Arial 12 A Wrap Text Number Paste BIU Merge & Center - Conditional Format Formatting Table Clipboard Font 5 Alignment Number Styles C19 =C5*C16 B D E 3 Constants 4 Overhead expense rate 5 Number of business days Year 1 Year 2 Year 3 NA 0.33 0.35 NA 300 300 6 7 Inputs 8 Year 1 Year 2 Year 3 10 Economic outlook (O-Optimistic, 9 Purchase price outlook (U-Up, D=Down) 11 Summary of business indicators NA о 0 NA U U Year 1 Year 2 Year 3 12 Net income NA 13 End-of-year cash on hand NA SAR 0.00 SAR 37,500.00 SAR 0.00 SAR 37,500.00 14 15 Calculations Year 1 Year 2 Year 3 16 Number of units sold in a day 1000 17 Selling price per unit 26 18 Cost of goods sold per unit 19 Number of units sold in a year W=8 11 NA 0 20 21 Income statement and Cash flow statement Year 1 Year 2 22 Beginning-of-year cash on hand NA Sales (revenue) NA SAR 37,500.00 SAR 0.00 Year 3 SAR 37,500.00 SAR 000 Copy the formula to cell D19 for Year 3. Assume that if the Economic outlook is Optimistic, the Year 2 Number of units sold in a day will be 6% more than in Year 1; in Year 3, they will be 6% more than in Year 2. Also assume that if the Economic outlook is Pessimistic, the Number of units sold in a day in Year 2 will be 1% less than those sold in Year 1; in Year 3, they will be 1% less than those sold in Year 2. An IF state- ment is needed in cell C16 to express this logic. An IF statement uses the following syntax: =IF(test condition, result if test is True, result if test is False) DEFINITION IF statement: An IF statement is a programming conditional statement that, if proved true, performs a function or displays information. Syntax: In Excel, syntax is the layout of a function. Functions need to be presented precisely or they will not work. Decision Support System Fundamentals 365 30/06/2023 14:28

366 QUICK TIP In Excel, quotation marks denote text. The input is one letter of text, so quotation marks are needed around the "O". Also note that multiplying by 1.06 results in a 6% increase whereas multiplying by 0.99 results in a 1% decrease, because, in a spreadsheet, the value "1" represents 100%. Therefore, in this example, our calculation would be as follows: =IF(economy variable = Optimistic, Then Number of units sold in a day will go UP 6%, Else Number of units sold in a day will go DOWN 1%) To turn this logic into a formula your spreadsheet can use, make substitu- tions in the above formula so it looks like this: =IF($C$8=“0”,B16*1.06, B16*0.99) Enter the entire IF formula into cell C16, as shown in Figure 10-14. The "$" symbol on both the row and columns show that this cell uses an Absolute Address. Absolute addressing is needed when referring to the input value ($C$8), because you always want your formulas to refer to this specific cell and not change when you copy the formula to another location. Absolute addressing maintains the $C$8 reference when the formula is copied and does not change. QUICK TIP To easily make a cell value absolute, press F4 after typing in the values. In this example, absolute addressing is done for you. DEFINITION Absolute addressing: A cell reference that maintains the cell address no matter where they are copied to. A "$" on the letter of the address maintains the column, and a "$" on the number maintains the row. Copy the formula in C16 to D16 for Year 3. STEP 3 FIGURE 10-14: Entering the formula to compute Year 2 Number of units sold in a day 10 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 366 AutoSave Off Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Arial 10 A A Paste BIU Wrap Text Merge & Center General 3 3 % 9 Undo Clipboard ES Font Alignment ES Number C16 X fx A IF($C$8="O",B16*1.06,B16*0.99) B 15 Calculations 16 Number of units sold in a day Year 1 1000 Year 2 1060 D Year 3 1124 E 17 Selling price per unit 26 18 Cost of goods sold per unit 11 19 Number of units sold in a year NA 318,000 30/06/2023 14:28

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 367 The Selling price per unit is also a function of the Economic outlook. Assume that the two-part rule is as follows: If the Economic outlook is Optimistic, the Selling price per unit in Year 2 will be 7% more than that of Year 1; in Year 3, it will be 7% more than that of Year 2. This is calculated as Selling price per unit × 1.07. If the Economic outlook is Pessimistic, the Selling price per unit in Year 2 and Year 3 will equal the per-unit price in Year 1; that is, the price will not change. Testing the formulas Test your understanding of the selling price calculation by figuring out the formula for cell C17. Enter the formula and copy it to cell D18. You will need to use absolute addressing. The Cost of goods sold per unit is a function of the Purchase price out- look: . If the Purchase price outlook is Up (U), Cost of goods sold per unit in Year 2 will be 1.25 times that of Year 1; in Year 3, it will be 1.25 times that of Year 2. If the Purchase price outlook is Down (D), the multiplier in Years 2 and 3 will be 1.01. To test your understanding of the process so far, figure out the formula for cell C18. Enter and copy the formula to cell D18. Again, you will need to use absolute addressing. Your formulas for selling price and cost of goods sold, given Opt-Up input values, should yield the calculated values shown in Figure 10-15. FIGURE 10-15: Calculated values given Opt-Up input values AutoSave Off File Search Home Insert Page Layout Formulas Data Review View Automate Help Acrobat A A Arial 10 Paste B I U Unda Clipboard E Font 125 fx A E Wrap Text General Merge & Center▾ % Alignment Number B D 15 Calculations 16 Number of units sold in a day 17 Selling price per unit Year 1 Year 2 1000 1060 Year 3 1124 18 Cast of goods sold per unit 19 Number of units sold in a year M=8 26 27.82 29.77 11 13.75 17.19 NA 318,000 337,080 LLI E Decision Support System Fundamentals 367 30/06/2023 14:28

QUICK TIP Use CTRL+. The symbol, called a backtick, is located on a button on the top-left of your keyboard. Assume that you change the input values to Pess-Down. You can check your formula is correct by toggling the spreadsheet between displaying values and formulas. To do this, press CTRL+` to switch between them. Figure 10-16 shows the correct formula. FIGURE 10-16: Toggling between values and formulas allows you to check the formula 368 10 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 368 AutoSave Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat 4 X Arial 10 A A Wrap Text General Paste B I U Merge & Center % Undo Cipboard 6 Fact 5 Alignment 5 Number 626 fx A 15 Calculations 16 Number of units sold in a day B Year 1 17 Selling price per unit 1000 26 18 Cost of goods sold per unit 11 19 Number of units sold in a year NA 20 Year 2 =IF($C$8="0" B16*1.06,B16*0.99) =IF($C$8="0" B17*1.07,817) =IF($C$9="U" B18*1.25.818*1.01) =C5°C16 82 Your formulas should yield the calculated values shown in Figure 10-17. FIGURE 10-17: Calculated values given Pess-Down input values AutoSave Off Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Arial -10 A A Wrap Text General Paste BIU Y v Merge & Center - %" Font Б Alignment T Number Undo Clipboard Ex J13 XVfx A B 15 Calculations 16 Number of units sold in a day Year 1 C Year 2 D E Year 3 1000 990 980 17 Selling price per unit 26 26.00 26.00 18 Cost of goods sold per unit 11 11.11 11.22 19 Number of units sold in a year NA 297,000 294,030 That completes the body of your spreadsheet. The values in the Calculations section ripple through the Income statement and Cash flow statement section because the income statement formulas reference the calculations. Assuming Opt-Up, the income and cash flow numbers should look like those in Figure 10-18. In the next lesson we will learn how to use Scenario Manager with this workbook. 30/06/2023 14:28

FIGURE 10-18: Completed Income statement and Cash flow statement section AutoSave Off File Search Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Arial 10 Α' Α Paste BIU 田 3 A Undo Clipboard E Font Ex C8 fx o A ab Wrap Text General Merge & Center % Alignment EX Number B D 23 Sales (revenue) 26 21 Income statement and Cash flow statement 22 Beginning-of-year cash on hand 24 Cost of goods sold 25 Gross income Overhead expense 27 Net income Year 1 Year 2 NA SAR 37,500.00 NA SAR 8,846,760.00 Year 3 SAR 3,035,254.20 SAR 10,033,995.19 NA SAR 4,372,500.00 SAR 5,793,562.50 NA NA NA 28 End-of-year cash on hand SAR 37,500.00 SAR 4,474,260.00 SAR 1,476,505.80 SAR 2,997,754.20 SAR 3,035,254.20 SAR 4,240,432.69 SAR 1,484,151.44 SAR 2,756,281.25 SAR 5,791,535.45 29 REVIEW QUESTIONS 1. Which forecast will most businesses be more successful in? a. Good economy with low costs. b. Bad economy with high costs c. Good economy with high costs d. Bad economy with low costs 2. What is absolute addressing? a. A way to undo an error made in a spreadsheet b. Using decimal places for figures c. A way to ensure cell references remain constant wherever they are copied d. The method for creating a chart based on your data. 3. What is an input variable? a. A data point in a DSS that is known in advance and will not change b. A data point that the decision maker can change or influence c. A measure of quantifiable indicators of performance against a strategic goal or objective d. A way to predict a future outcome using relevant data to inform your prediction وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 369 Decision Support System Fundamentals 369 30/06/2023 14:28