D E Budget Assumptions LEFT AND Runctions First Month's Sales in Units Assumption Unit Sales Growth Per Month Assumption Unit Sales Price Assumption TRIM Runction Gross Marin Commission Ending Inventory 10 11 12 SUBSTITUTE Function Minimum Cash Balance Assumption Interest Rate Assumption Collections Payments UPPER Function LOWER Function 14 15 Month 16 17 21 CONCATENATU/RIGHT Function Period: Period 2 Period Per Month 20 Operating en 21 22 23 MID Edition 7200 Payroll 7500 Uuties 7900 Other 26 27 28 25 32 34 35 40 41 42 Input Supporting schedulin Opbudget Case + Ready H 13 47 90 51 52 34 Parents 35 19 65 70 71 72 74 BE 66 ON CAN Puste wires. Certer Contal format Formalingas Table Styles Sie Total income Cost of Goods Sold Purch Con Total Cort of Good Sold Grow Profit Operation Tot Operating per Net Income A1 64 Operating Budget Calori Body Ceneral PE. 3.2.E- Pe BT MOTS Corte Cod fat For alle COM Dan The very hard Com 4 re 10 21 0 25 11 10,000 12.00 $ 50% 10% 250 60% Budget Assumptions First Month's Sales 3 Sales Price 5 Gross Margin 7 Commission 8 Sales Growth 9 Ending Inventory 10 Minimum Cash Balance 11 Interest Rate 12 13 14 Month 1 15 Month 2 16 Month 3 17 18 Operating Expenses 19 Payroll 20 Utilities 20,000 12% Collections 25% Payments 60% 40% 50% 25% 0% Per Month $ 192,000! $84,000 $234,000 21 Other Side D. 4555 Step 17 of 40: Beginning Inventory D February March April 10000 10250 10500 6150 6300 5450 16150 16550 16950 M 10750 6600 17350 October November December 000 12250 12500 12750 0 7500 7650 7550 80 19750 20150 20400 1 Walls Widgets January 2 Budgeted Sales in Units 3 Add: Ending Inventory 4 Total Needs 5 Less Beginning Inventory 6 Required Purchases in Units 7 Required Purchases in Dollars 8 Budgeted Sales in Dollars 9 Sales Commissions in Dollars 10 YOUR TURN: The Beginning Inventory formula is similar in nature to the Ending Inventory formula, but is a percentage of current month sales (as opposed to the following month's sales as you used earlier). In other words: January Sales multiplied by Ending Inventory Percentage. M 0 1 Wally's Widgets January February March April October November December 2 Budgeted Sales in Units 10000 10250 10500 10750 2000 12250 12500 12750 3 Add: Ending Inventory 6300 6150 6600 5450 50 7650 7500 7650 4 Total Needs 16150 16550 10950 17350 19750 20150 20400 5 Less Beginning inventory 8 Required Purchases in Units 2 7 Required Purchases in Dollars If you calculate a number outside of Excelsuch as with your calculator and Budgeted Sales in Dolars thon type it into the spreadsheet for this exercise, the cell turn red. You will not Sales Commissions in Dolars betfuil credit for any such cells that contain static values instead of formulas 10 Students Excel We're here to help. Email us at supportistudentsexcel.com 22.14 00:03 / 00:52 B D E F 1 LEFT/FIND Functions First Month's Sales in Units Assumption YOUR TURN: Based on this paragraph enter these assumptions: Wally's Widgets is starting operations as of January 1. The company sells a single product, and expects first month's sales to be 10.000 units, with sales increasing by 250 units per month thereafter. The sales price for the producr is $12.00. dules Operating Budget Ah Badget Cash Flow Budget v3 out Formulas Data R Percent Style Comma Style bt you want to do Conditional Formatting 11 A D 0-00 06 Decrease Decimal A Currency Style pply the Currency and Comma Style formats on the Home menu as Is B2:B4. Use Decrease Decimals to remove decimal places from B2:33. We're here to help. Email us at support@students excel.com NEXT > Pet Fama Hly AA WES 2 Sosa Festa Merge Center Condition Fating Set Ah SUMIFinputs SAS SA$21.5A 10 Inputs $1351 H RE KOKIE February 1210 March 126,000 April 129,000 MY 112,000 une 115.000 Wuly 100.000 A 141.000 September 10,000 000 W Oktober 147.000 November December LOOD cost of Good Sold 6,0 6. 12.000 2.400 12.100 9.00 11, 65.400 12.900 71.400 14100 72,00 10 2.400 14.00 TA 3. 15.000 15.100 DOS'TE 1200 12.000 Commons BO 100 BOC 7,00 0.00 Od 22.00 Total cost of Goods Sold 700 61,200 78,300 S0700 L. 51.100 100 51.00 54, 7,100 DE 192,000 i2.c 13,000 192.000 192.000 192.000 192.000 192.000 OVE 100 Paesi 193.000 4.00 2.000 192,000 M.000 14.000 192.000 1.000 214.000 . 192.000 4000 2000 000 GRE NOVE 214.000 000 2000 2000 2000 214,000 214.000 510.000 Sie 510.000 510.000 54500 510.000 DOIS 0001 St. 4910 Si LED 50.00 4,100 100 510.000 SMO 50,000 461.7K Total Operating tapenes Net Income 156,00 OSO Supporting Schedules Operating out 1-Gross Margin)Sales Price 36 D F G H M 1 Wally's Widgets January February March April May lune July August September October November December Budgeted sales in Units 10,000 10,250 10,500 10,750 11,000 11,250 11,500 11,750 12,000 12.250 12,500 12,750 Add: Ending Inventory 6,150 6,300 6,450 6.600 6,750 6,00 7,050 7,200 7,350 7,500 7,650 7,650 Total Needs 16,150 16,550 16,950 17,350 17,750 18,150 111,550 16.950 19,350 19,750 20,150 20,400 5 Less Beginning Inventory 6.000 6,00 6,450 5,600 6,250 6.900 7,050 7,200 7,350 7.500 7,650 6 Required Purchases in Units 10.150 10,400 10,650 10,300 11,150 11,400 11,650 11.900 12.150 12,400 12,650 12,750 Required Purchases in Dollars $0.900 62.400 63.900 65,400 66,0 68.400 69,00 71,400 72.900 74,400 75,900 76,500 Budgeted sales in Dollars 120,000 121,000 126,000 129,000 132,000 135,000 131,000 141,000 144,000 147,000 150,000 151.000 Sales Commision in Dollars 12.000 12.100 12.600 12,600 11,200 12.500 13.800 14,100 14,400 14,700 15,000 15,300 10 11 Collection Ratio January February March April May Huly August September Oktober November December 12 January 25 SON 25N ON ON ON ON ON ON February ON 25 SON 25N ON ON ON ON ON ON 14 March ON ON 25 SON 25 ON ON ON ON ON 15 April ON ON 25N SON 25N ON ON ON ON 16 May ON SON 25N ON ON ON ON ON ON 25 SON ON ON ON os 18 ay ON ON ON 25 ON ON ON 19 August ON ON ON ON ON 25 50N ON 20 September ON ON ON 0% ON 09 25 25W ON 21 October ON ON 04 ON ON ON 09 ON 25N 25 Inputs Supporting Schedules Operating Budget Cash Budget NO NO NO NO NO ND 25 NO NO 999999 NOS NO NO NO NO NO 25N D E Budget Assumptions LEFT AND Runctions First Month's Sales in Units Assumption Unit Sales Growth Per Month Assumption Unit Sales Price Assumption TRIM Runction Gross Marin Commission Ending Inventory 10 11 12 SUBSTITUTE Function Minimum Cash Balance Assumption Interest Rate Assumption Collections Payments UPPER Function LOWER Function 14 15 Month 16 17 21 CONCATENATU/RIGHT Function Period: Period 2 Period Per Month 20 Operating en 21 22 23 MID Edition 7200 Payroll 7500 Uuties 7900 Other 26 27 28 25 32 34 35 40 41 42 Input Supporting schedulin Opbudget Case + Ready H 13 47 90 51 52 34 Parents 35 19 65 70 71 72 74 BE 66 ON CAN Puste wires. Certer Contal format Formalingas Table Styles Sie Total income Cost of Goods Sold Purch Con Total Cort of Good Sold Grow Profit Operation Tot Operating per Net Income A1 64 Operating Budget Calori Body Ceneral PE. 3.2.E- Pe BT MOTS Corte Cod fat For alle COM Dan The very hard Com 4 re 10 21 0 25 11 10,000 12.00 $ 50% 10% 250 60% Budget Assumptions First Month's Sales 3 Sales Price 5 Gross Margin 7 Commission 8 Sales Growth 9 Ending Inventory 10 Minimum Cash Balance 11 Interest Rate 12 13 14 Month 1 15 Month 2 16 Month 3 17 18 Operating Expenses 19 Payroll 20 Utilities 20,000 12% Collections 25% Payments 60% 40% 50% 25% 0% Per Month $ 192,000! $84,000 $234,000 21 Other Side D. 4555 Step 17 of 40: Beginning Inventory D February March April 10000 10250 10500 6150 6300 5450 16150 16550 16950 M 10750 6600 17350 October November December 000 12250 12500 12750 0 7500 7650 7550 80 19750 20150 20400 1 Walls Widgets January 2 Budgeted Sales in Units 3 Add: Ending Inventory 4 Total Needs 5 Less Beginning Inventory 6 Required Purchases in Units 7 Required Purchases in Dollars 8 Budgeted Sales in Dollars 9 Sales Commissions in Dollars 10 YOUR TURN: The Beginning Inventory formula is similar in nature to the Ending Inventory formula, but is a percentage of current month sales (as opposed to the following month's sales as you used earlier). In other words: January Sales multiplied by Ending Inventory Percentage. M 0 1 Wally's Widgets January February March April October November December 2 Budgeted Sales in Units 10000 10250 10500 10750 2000 12250 12500 12750 3 Add: Ending Inventory 6300 6150 6600 5450 50 7650 7500 7650 4 Total Needs 16150 16550 10950 17350 19750 20150 20400 5 Less Beginning inventory 8 Required Purchases in Units 2 7 Required Purchases in Dollars If you calculate a number outside of Excelsuch as with your calculator and Budgeted Sales in Dolars thon type it into the spreadsheet for this exercise, the cell turn red. You will not Sales Commissions in Dolars betfuil credit for any such cells that contain static values instead of formulas 10 Students Excel We're here to help. Email us at supportistudentsexcel.com 22.14 00:03 / 00:52 B D E F 1 LEFT/FIND Functions First Month's Sales in Units Assumption YOUR TURN: Based on this paragraph enter these assumptions: Wally's Widgets is starting operations as of January 1. The company sells a single product, and expects first month's sales to be 10.000 units, with sales increasing by 250 units per month thereafter. The sales price for the producr is $12.00. dules Operating Budget Ah Badget Cash Flow Budget v3 out Formulas Data R Percent Style Comma Style bt you want to do Conditional Formatting 11 A D 0-00 06 Decrease Decimal A Currency Style pply the Currency and Comma Style formats on the Home menu as Is B2:B4. Use Decrease Decimals to remove decimal places from B2:33. We're here to help. Email us at support@students excel.com NEXT > Pet Fama Hly AA WES 2 Sosa Festa Merge Center Condition Fating Set Ah SUMIFinputs SAS SA$21.5A 10 Inputs $1351 H RE KOKIE February 1210 March 126,000 April 129,000 MY 112,000 une 115.000 Wuly 100.000 A 141.000 September 10,000 000 W Oktober 147.000 November December LOOD cost of Good Sold 6,0 6. 12.000 2.400 12.100 9.00 11, 65.400 12.900 71.400 14100 72,00 10 2.400 14.00 TA 3. 15.000 15.100 DOS'TE 1200 12.000 Commons BO 100 BOC 7,00 0.00 Od 22.00 Total cost of Goods Sold 700 61,200 78,300 S0700 L. 51.100 100 51.00 54, 7,100 DE 192,000 i2.c 13,000 192.000 192.000 192.000 192.000 192.000 OVE 100 Paesi 193.000 4.00 2.000 192,000 M.000 14.000 192.000 1.000 214.000 . 192.000 4000 2000 000 GRE NOVE 214.000 000 2000 2000 2000 214,000 214.000 510.000 Sie 510.000 510.000 54500 510.000 DOIS 0001 St. 4910 Si LED 50.00 4,100 100 510.000 SMO 50,000 461.7K Total Operating tapenes Net Income 156,00 OSO Supporting Schedules Operating out 1-Gross Margin)Sales Price 36 D F G H M 1 Wally's Widgets January February March April May lune July August September October November December Budgeted sales in Units 10,000 10,250 10,500 10,750 11,000 11,250 11,500 11,750 12,000 12.250 12,500 12,750 Add: Ending Inventory 6,150 6,300 6,450 6.600 6,750 6,00 7,050 7,200 7,350 7,500 7,650 7,650 Total Needs 16,150 16,550 16,950 17,350 17,750 18,150 111,550 16.950 19,350 19,750 20,150 20,400 5 Less Beginning Inventory 6.000 6,00 6,450 5,600 6,250 6.900 7,050 7,200 7,350 7.500 7,650 6 Required Purchases in Units 10.150 10,400 10,650 10,300 11,150 11,400 11,650 11.900 12.150 12,400 12,650 12,750 Required Purchases in Dollars $0.900 62.400 63.900 65,400 66,0 68.400 69,00 71,400 72.900 74,400 75,900 76,500 Budgeted sales in Dollars 120,000 121,000 126,000 129,000 132,000 135,000 131,000 141,000 144,000 147,000 150,000 151.000 Sales Commision in Dollars 12.000 12.100 12.600 12,600 11,200 12.500 13.800 14,100 14,400 14,700 15,000 15,300 10 11 Collection Ratio January February March April May Huly August September Oktober November December 12 January 25 SON 25N ON ON ON ON ON ON February ON 25 SON 25N ON ON ON ON ON ON 14 March ON ON 25 SON 25 ON ON ON ON ON 15 April ON ON 25N SON 25N ON ON ON ON 16 May ON SON 25N ON ON ON ON ON ON 25 SON ON ON ON os 18 ay ON ON ON 25 ON ON ON 19 August ON ON ON ON ON 25 50N ON 20 September ON ON ON 0% ON 09 25 25W ON 21 October ON ON 04 ON ON ON 09 ON 25N 25 Inputs Supporting Schedules Operating Budget Cash Budget NO NO NO NO NO ND 25 NO NO 999999 NOS NO NO NO NO NO 25N