MIS - Building a Decision Support System (DSS) in Microsoft Excel. Problem is: You are now...

 

Transcribed Image Text:

MIS" - Building a Decision Support System (DSS) in Microsoft Excel. Problem is: You are now in late 1999, you want to forecast your company's net income and cash flow in the next 2 years (2000 and 2001), based on current year's (1999) data. Use “what-if" analysis to do the forecast, in which we consider 2 factors (to be treated as inputs in Excel): • Economic outlook: optimistic or pessimistic Purchasing price (Cost) outlook: up or down the economic outlook will be optimistic but the purchasing price will go up, then how will the net profit and cash flow be in the next 2 years? If the economic outlook will be pessimistic but the purchasing price will go down, then how will the net profit and cash flow be in the next 2 years? Thus how will the cash flow and net income be affected in next 2 years in each of the 4 situations (scenario or case)? Economy is istic (P) Economy is Optimistic (O) U, P D, P U, O Cost goes UP (U) Cost goes DOWN (D) | D, O 1 A B C 1 Tutorial C Exercise 1999 2000 2001 2 3 CONSTANTS 4 Tax rate expected 5 Number of Business days 0.35 300 NA 0.33 NA 300 NA 7 INPUTS Economic Outlook (0=optimistic; 8 P=pessimistic) Purchasing price outlook 9(U=Up; D=Down) NA NA 10 11 Summary of key results 12 Net income for year 13 Year-end cash on hand NA NA 74 15 Calculations 16 Number of units sold/day 17 Selling price/unit 18 Cost of goods sold/unit 19 Number of units sold/year 1000 7 NA 20 Income statement and 21 cash flow statement Beginning of year cash 22 on hand NA 24 Sales 25 Cost of goods sold | 26 Pre-tax profit margin 27 Tax expense 28 Net income NA NA NA NA NA 29 End of year cash on hand (beginning of yr. Cash, 30 plus net income for year) 10000 Excel spreadsheet: designed like an income statement. • The input values entered (by users) for economic & purchase price outlook in cells C8 and C9 are for both years 2000 and 2001. A B 7 INPUTS ECONOMIC OUTLOOK (O = 8 OPTIMISTIC; P = PESSIMISTIC) NA NA PURCHASING PRICE OUTLOOK 9 (U UP; D= DOWN) NA NA • If (economic outlook is optimistic), Then units sold per dayt 6% of previous year Else units sold per day 1% of previous year Units sold per day should be integer (whole number), INT() removes all decimal places (no round off). C16=If(SC$8="O", INT(B16*1.06), INT(B16*0.99)) Horizontal Copy & paste formula to right => D16=If(SC$8="O", INT(C16*1.06), INT(C16*0.99)) Absolute addressing: placing $$ around C => $C$. When copy & paste formula, $C$8 will not change to SD$8. A B D 16 NUMBER OF UNITS SOLD/DAY 1000 =IF(SC$8="0", INT(1.06'B16), INT(0.99'B 16)) =IF($C$8="0", INT(1.06'C16) INT(0.99'C16)) • If (economic outlook is optimistic), Then selling price T 7% of previous year Else selling price remains the same as previous year Write Excel formula for cell C17 which is year 2000's selling price, and for cell D17, which is 2001's selling price. • If (purchase price outlook is UP) Then cost of goods sold per unit 125% of previous year Else cost of goods sold per unit T 1% of previous year Write Excel formula for cell C18, which is year 2000's CGS, and for cell D18, which is 2001's CGS. • Average number of units sold per year (Row 19): a function of the number of business days and average number of units sold per day. Beginning of year cash on hand = cash on hand at the end of previous year. (Row 22) • End of year cash on hand beginning of year cash on hand + net income of current year. (Row 30) • Revenue from Sales of current year (Row 24) is a function of the number of units sold per year and the selling price per unit. Note: Cents do not worth much and revenue is not affected much by removal of cents. • Cost of goods sold of current year (Row 25) is the function of the number of units sold per year and the cost of good sold per unit. Note: Cents do not worth much and CGS is not affected much by removal of cents. • Pre-tax profit margin (Row 26) is the profit before tax. • Income tax of current year (Row 27) is only paid on positive pre-tax profit, and tax office does not consider cents. • Net income (Row 28) is the profit after tax. • Both cells: C12 and C28 hold the same net income for year 2000. Similarly, D12 and D28 hold the same net income for year 2001. • C13 and C30 hold the same year-end cash on hand for year 2000. Similarly, D13 and D30 hold same year- end cash on hand for year 2001. Forecast Outputs: Situation (scenario or case) 1: • Optimistic Economy (C8="O") and • Purchase Cost goes DOWN (C9="D") A B D 12 NET INCOME FOR YEAR 13 YEAR-END CASH ON HAND 950248| 1084852 960248 2045100 NA NA Situation (scenario or case) 2: Optimistic Economy (C8="O") and • Purchase Cost goes UP (C9="U") B NA D 728520 12 NET INCOME FOR YEAR 13 YEAR-END CASH ON HAND 796845 NA 806845 1535365 Situation (scenario or case) 3: • Pessimistic Economy (C8="P") and • Purchase Cost goes UP (C9="U") A B C 12 NET INCOME FOR YEAR 13 YEAR-END CASH ON HAND NA 646718 441919 NA 656718 1098637 Situation (scenario or case) 4: • Pessimistic Economy (C8="P") and • Purchase Cost goes DOWN (C9="D") 6. A B D 12 NET INCOME FOR YEAR 13 YEAR-END CASH ON HAND NA 789991 752877 NA 799991 1552868 What-if Exercise 1999 2000 2001 CONSTANTS Tax rate expected Number of Business days 0.35 300 NA 0.33 NA 300 INPUTS P=pessimistic) NA Purchasing price outlook (U=Up; D-Down) Summary of key results Net income for year NA 2001 1999 NA 2000 Year-end cash on hand NA 2001 Calculations Average number of units sold/day Selling price/unit Cost of goods sold/unit Average Number of units sold/year Income statement and cash flow statement Beginning of year cash on hand 1999 1000: 2000 7 3 NA 1999 2000 2001 NA Revenue from sales NA Cost of goods sold Pre-tax profit margin Tax expense NA NA NA Net income NA End of year cash on hand (beginning of yr. Cash, plus net income for year) #### a r . y . copied and pasted from other formula? Upload your excel file to SOUL. A C Summary of key resuts 2 Net rcome for yer 1999 200 2001 NA : Yerend cash on hard NA Calculations * Areage nurber dlurts sodidey 100 1 Salng picelrk : Cost af gods soldint Aenge Nunber diuit sodyer NA 199 200 2001 3 Income statement and 1999 21 cash flow statement 2000 2001 Beginig d ye cash NA 2 on hand 2 Reverue from sales NA * Cos f good sod NA NA 7 Tarenpense * Net icome NA NA 1 End of year cash on hand begiming of y. Cash. 3 plus net income for year) 1000 CamScanner 图 MIS" - Building a Decision Support System (DSS) in Microsoft Excel. Problem is: You are now in late 1999, you want to forecast your company's net income and cash flow in the next 2 years (2000 and 2001), based on current year's (1999) data. Use “what-if" analysis to do the forecast, in which we consider 2 factors (to be treated as inputs in Excel): • Economic outlook: optimistic or pessimistic Purchasing price (Cost) outlook: up or down the economic outlook will be optimistic but the purchasing price will go up, then how will the net profit and cash flow be in the next 2 years? If the economic outlook will be pessimistic but the purchasing price will go down, then how will the net profit and cash flow be in the next 2 years? Thus how will the cash flow and net income be affected in next 2 years in each of the 4 situations (scenario or case)? Economy is istic (P) Economy is Optimistic (O) U, P D, P U, O Cost goes UP (U) Cost goes DOWN (D) | D, O 1 A B C 1 Tutorial C Exercise 1999 2000 2001 2 3 CONSTANTS 4 Tax rate expected 5 Number of Business days 0.35 300 NA 0.33 NA 300 NA 7 INPUTS Economic Outlook (0=optimistic; 8 P=pessimistic) Purchasing price outlook 9(U=Up; D=Down) NA NA 10 11 Summary of key results 12 Net income for year 13 Year-end cash on hand NA NA 74 15 Calculations 16 Number of units sold/day 17 Selling price/unit 18 Cost of goods sold/unit 19 Number of units sold/year 1000 7 NA 20 Income statement and 21 cash flow statement Beginning of year cash 22 on hand NA 24 Sales 25 Cost of goods sold | 26 Pre-tax profit margin 27 Tax expense 28 Net income NA NA NA NA NA 29 End of year cash on hand (beginning of yr. Cash, 30 plus net income for year) 10000 Excel spreadsheet: designed like an income statement. • The input values entered (by users) for economic & purchase price outlook in cells C8 and C9 are for both years 2000 and 2001. A B 7 INPUTS ECONOMIC OUTLOOK (O = 8 OPTIMISTIC; P = PESSIMISTIC) NA NA PURCHASING PRICE OUTLOOK 9 (U UP; D= DOWN) NA NA • If (economic outlook is optimistic), Then units sold per dayt 6% of previous year Else units sold per day 1% of previous year Units sold per day should be integer (whole number), INT() removes all decimal places (no round off). C16=If(SC$8="O", INT(B16*1.06), INT(B16*0.99)) Horizontal Copy & paste formula to right => D16=If(SC$8="O", INT(C16*1.06), INT(C16*0.99)) Absolute addressing: placing $$ around C => $C$. When copy & paste formula, $C$8 will not change to SD$8. A B D 16 NUMBER OF UNITS SOLD/DAY 1000 =IF(SC$8="0", INT(1.06'B16), INT(0.99'B 16)) =IF($C$8="0", INT(1.06'C16) INT(0.99'C16)) • If (economic outlook is optimistic), Then selling price T 7% of previous year Else selling price remains the same as previous year Write Excel formula for cell C17 which is year 2000's selling price, and for cell D17, which is 2001's selling price. • If (purchase price outlook is UP) Then cost of goods sold per unit 125% of previous year Else cost of goods sold per unit T 1% of previous year Write Excel formula for cell C18, which is year 2000's CGS, and for cell D18, which is 2001's CGS. • Average number of units sold per year (Row 19): a function of the number of business days and average number of units sold per day. Beginning of year cash on hand = cash on hand at the end of previous year. (Row 22) • End of year cash on hand beginning of year cash on hand + net income of current year. (Row 30) • Revenue from Sales of current year (Row 24) is a function of the number of units sold per year and the selling price per unit. Note: Cents do not worth much and revenue is not affected much by removal of cents. • Cost of goods sold of current year (Row 25) is the function of the number of units sold per year and the cost of good sold per unit. Note: Cents do not worth much and CGS is not affected much by removal of cents. • Pre-tax profit margin (Row 26) is the profit before tax. • Income tax of current year (Row 27) is only paid on positive pre-tax profit, and tax office does not consider cents. • Net income (Row 28) is the profit after tax. • Both cells: C12 and C28 hold the same net income for year 2000. Similarly, D12 and D28 hold the same net income for year 2001. • C13 and C30 hold the same year-end cash on hand for year 2000. Similarly, D13 and D30 hold same year- end cash on hand for year 2001. Forecast Outputs: Situation (scenario or case) 1: • Optimistic Economy (C8="O") and • Purchase Cost goes DOWN (C9="D") A B D 12 NET INCOME FOR YEAR 13 YEAR-END CASH ON HAND 950248| 1084852 960248 2045100 NA NA Situation (scenario or case) 2: Optimistic Economy (C8="O") and • Purchase Cost goes UP (C9="U") B NA D 728520 12 NET INCOME FOR YEAR 13 YEAR-END CASH ON HAND 796845 NA 806845 1535365 Situation (scenario or case) 3: • Pessimistic Economy (C8="P") and • Purchase Cost goes UP (C9="U") A B C 12 NET INCOME FOR YEAR 13 YEAR-END CASH ON HAND NA 646718 441919 NA 656718 1098637 Situation (scenario or case) 4: • Pessimistic Economy (C8="P") and • Purchase Cost goes DOWN (C9="D") 6. A B D 12 NET INCOME FOR YEAR 13 YEAR-END CASH ON HAND NA 789991 752877 NA 799991 1552868 What-if Exercise 1999 2000 2001 CONSTANTS Tax rate expected Number of Business days 0.35 300 NA 0.33 NA 300 INPUTS P=pessimistic) NA Purchasing price outlook (U=Up; D-Down) Summary of key results Net income for year NA 2001 1999 NA 2000 Year-end cash on hand NA 2001 Calculations Average number of units sold/day Selling price/unit Cost of goods sold/unit Average Number of units sold/year Income statement and cash flow statement Beginning of year cash on hand 1999 1000: 2000 7 3 NA 1999 2000 2001 NA Revenue from sales NA Cost of goods sold Pre-tax profit margin Tax expense NA NA NA Net income NA End of year cash on hand (beginning of yr. Cash, plus net income for year) #### a r . y . copied and pasted from other formula? Upload your excel file to SOUL. A C Summary of key resuts 2 Net rcome for yer 1999 200 2001 NA : Yerend cash on hard NA Calculations * Areage nurber dlurts sodidey 100 1 Salng picelrk : Cost af gods soldint Aenge Nunber diuit sodyer NA 199 200 2001 3 Income statement and 1999 21 cash flow statement 2000 2001 Beginig d ye cash NA 2 on hand 2 Reverue from sales NA * Cos f good sod NA NA 7 Tarenpense * Net icome NA NA 1 End of year cash on hand begiming of y. Cash. 3 plus net income for year) 1000 CamScanner 图