AutoSave OFF A ACE ... Case Study Chapter 8 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) 11 AA D Wrap Text v General Insert v OV d max Delete Paste B U v A Merge & Center v v % 9 Conditional Format Cell Sort & Find & Analyze Sensitivity Formatting as Table Styles Format Filter Select Data Clipboard Font Alignment Number Styles Cells Editing Analysis Sensitivity D62 X V fx B C D E F G H K M N P Q R S T Insert formulas and cell references into the "gold" cells below. March April May June July Quarter Total (April, May, June) Sales Projections: [1] Quantitie 320,000 360,000 288,000 267,000 268,200 915,000 boxes of muesli Revenue E 3,060,000 E 2,448,000 E 2,269,500 E 7,777,500 Production Plan: [2] 10 Required for Sales 360,000 288,000 267,000 Projected Inventory Levels 11 + Desired Ending Finished Goods Inventory 57,600 53,400 53,640 58,000 14,400 - Beginning Finished Goods Inventory 72,000) 57,600 53,400) 57,000 14,200 Required Production 345,600 283,800 267,240 896,640 boxes of muesli 56,000 4,000 13 ,800 15 Raw Materials Purchases: [3] 55,000 13,60 Required Production 345,600 283,800 267,240 54,000 13,40 Kilograms of Raw Materials for Production 172,800 141,900 133,620 53,000 13,200 + Desired Ending Raw Materials Inventory 14,190 13,362 13,962 52,000 13,00 Beginning Raw Materials Inventory 17,280) (14,190 13,362) 20 Required Purchases of Raw Materials (Kg) 169,710 141,072 134,220 445,002 Kg 51,000 12,800 April May June Cost of Raw Materials Purchases E 1,849,839 E 1,537,685 E 1,462,998 E 4,850,522 Finished Goods Inventory -Raw Materials Inventory Other Purchases: [4] Fixed Manufacturing Overhead E 317,500 E 317,500 E 317,500 Less Depreciation (not a cash expense ( 250,000) ( 250,000) E 250,000 Manufacturing Overhead Cash Purchases E 67,500 67,500 67,500 E 202,500 27 Fixed Direct Labor 80,000 E 80,000 E 80,000 E 240,000 Projected Changes in Receivables and Payables Fixed Selling & Admin E 133,000 E 133,000 E 133,000 E 399,000 29 Variable Selling & Admin (E.20 per unit sold) E 72,000 E 57,600 E 53,400 E 183,000 E 1,000,000 30 Total Other Purchases E 352,500 338,100 333,900 E 1,024,500 E 900,000 E 800,000 E 700,000 Balance in Accounts Receivable: [5] E 600,000 Beginning Balance E 816,000 E 918,000 E 734,400 E 816,000 Account Balances E 500,000 + Sales for the Month E 3,060,000 E 2,448,000 E 2,269,500 E 7,777,500 E 400,000 35 -Collections from prior Month (E 816,000) (E 918,000) (E 734,400) (E 2,468,400 E 300,000 36 Collections from current Month (E 2,142,000) (E 1,713,600) (E 1,588,650) (E 5,444,250 E 200,000 E 918,000 E 734,400 E 680,850 E 680,850 E 100,000 Ending Balance April May June 39 Balance in Accounts Payable: [6] 40 Beginning Balance E 196,200 E 369,968 E 307,537 E 196,200 Accounts Receivable Accounts Payable 41 + Purchases of Raw Materials E 1,849,839 E 1,537,685 E 1,462,998 E 4,850,522 42 - Payments for Last Month's Payables (E 196,200) ( 369,968) (E 307,537) (E 873,705) chapter 8 Ready 100%AutoSave OFF A ACE ... Case Study Chapter 8 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) 11 AA D Wrap Text v General Insert v OV d Dax Delete J v Paste v % 9 Conditional Format Cell Sort & Find & Analyze Sensitivity B U v A Merge & Center v Formatting as Table Styles Format Filter Select Data Clipboard Font Alignment Number Styles Cells Editing Analysis Sensitivity D62 X V fx B C D E F G H K L M N P Q R S T Insert formulas and cell references into the "gold" cells below. March April May June July Quarter Total (April, May, June) April May June 39 Balance in Accounts Payable: [6] 40 Beginning Balance E 196,200 E 369,968 E 307,537 E 196,200 Accounts Receivable Accounts Payable 41 + Purchases of Raw Materials E 1,849,839 E 1,537,685 E 1,462,998 E 4,850,522 42 - Payments for Last Month's Payables (E 196,200) (E 369,968) (E 307,537) (E 873,705 43 - Payments for This Month's Payables (E 1,479,871) (E 1,230,148) ( 1,170,398) ( 3,880,417) 44 Ending Balance E 369,968 E 307,537 E 292,600 E 970,104 45 Projected Cash Flows 46 Cash Projections: [7] E 2,958,000 E 2,631,600 E 2,323,050 E 7,912,650 E 3,500,000 47 Cash Inflows 48 Cash Outflows: E 3,000,000 49 Payments for Raw Materials Purchases E 1,676,071 E 1,600,116 E 1,477,935 E 2,500,000 50 Payments for Other Purchases E 352,500 E 338,100 E 333,900 E 2,000,000 51 Total Cash Outflows E 2,028,571 E 1,938,216 E 1,811,835 5,778,622 52 Net Cash for the Month E 929,429 E 693,384 E 511,215 E 2,134,028 E 1,500,000 53 E 1,000,000 54 Beginning Ca E 620,000 E 1,549,429 E 2,242,813 E 620,000 E 500,000 55 Ending Cash Balance E 1,549,429 E 2,242,813 E 2,754,028 E 2,754,028 56 Minimum Required Cash Balance E 500,000 E 500,000 E 500,000 April May June 57 Over/Under E 1,049,429 E 1,742,813 E 2,254,028 Cash Inflows Cash Disbursements Cash Balance 58 59 Liquidity Ratios: [8] 6.67 9.68 11.74 60 61 Question 9 is a discussion question 62 63 64 65 66 76 chapter 8 + 100% Ready