Excel File Edit View Insert Format Tools Data Window Help 4 6 1 100 27 Fri 10 38 Q Building a Model Marvel Renovations xlsx Q v Search Sheet Home Insert Page Layout Formulas Data Review View ' Share K Cut Aria 10 A A 29 Wrap Text General Normal Bad Good Neutral Calculation X AutoSum Copy Fill Paste Format Merge Center $ 2 08 08 Conditional Format Check Cell Explanatory Input Linked Cell Not Insert Delete Format Sort Formatting as Table Clear Filter R52 X D E G H K LMN 0 Q R U V W X Z AA AB AC AD AE AF AG AH AI Input Data Collections during month of sale 20 Collections during month after sale 60 Note When the percent collected during Collections during second month after sale 25 the second month after sale is changed, Lease payments $7,00 the percent for collections during month Target cash balance 30,000 after sale is automatically changed so General and administrative salaries $25,000 that 100 of sales are collected during Depreciation charges $8,000 the three month period 10 Income tax payments (Sep Dec) $30,000 11 Miscellaneous expenses $5,000 12 New office suite payment (Oct) $95,000 13 Cash on hand July 1 $70,000 15 Sales, labor, and RM adjustment factor 16 a Prepare a monthly cash budget for the last six months of the year 19 May June July August September October November December January 20 Original sales estimates $75,00 $115,000 $145,000 $125,000 $120,000 $95,000 $75,000 $55,000 $45,000 21 Original labor and raw mat estimates $80,000 $75,000 $105,000 $85,000 $65,000 $70,000 $30,000 $35,000 22 23 Forecasted Sales 4 Sales (gross) 25 26 Collections 27 During month of sale 28 During 1st month after sale 29 During 2nd month after sale 30 Total collections 31 32 Purchases 33 Labor and raw materials 34 Payments for labor and raw materials 35 36 Payments 7 Payments for labor and raw materials 38 General and administrative salaries 39 Lease payments 40 Miscell 1 Income tax payments 42 Design studio payment 43 Total payments 44 45 Net Cash Flows 6 Cash on hand at start of forecast period 47 Net cash flow (NCF) Total collections Total payments Cumulative NCF Prior month cumulative this month's NCF 49 Cash Surplus (or Loan Requirement) 51 Target cash balance 52 Surplus cash or loan needed Cum NCF Target cash 53 54 Max Loan 55 56 b Prepare an estimate of the required financing (or excess funds) that is, the amount of money 57 Marvel's Renovations will need to borrow (or will have available to invest) for each month during that period 58 59 60 61 62 63 Build a Model Ready 100 Excel File Edit View Insert Format Tools Data Window Help 4 6 1 100 27 Fri 10 39 Q Building a Model Marvel Renovations xlsx Q v Search Sheet Home Insert Page Layout Formulas Data Review View ' Share K Cut Aria 10 A A 29 Wrap Text General Normal Bad Good Neutral Calculation I X AutoSum Copy Fill Paste BI UTY A Merge Center $ 2 08 08 Conditional Format Check Cell Explanatory Input Linked Cell Not Insert Format Delete Format Sort Formatting as Table Clear Filter R52 X H K LMN 0 Q V W Z AA AB AC AD AE AF AG AH AI b Prepare an estimate of the required financing (or excess funds) that is, the amount of money $7 Marvel's Renovations will need to borrow (or will have available to invest) for each month during that period 59 60 61 62 63 64 e If its customers began to pay late, this would slow down collections and thus increase the required loan amount Also, if s sales dropped off, this would have an effect on the required loan Do a sensitivity analysis that shows the effects of these 66 two factors on the max loan requirement Assume the purchases of labor and raw material also vary by the sales adjustment 67 factor 68 69 Answer 70 71 72 109 110 114 115 116 117 118 Build a Model Ready 100 Pipeline Ocular Therapeutic x D Home DEXTENZA Content X Google Flights x Review trip itinerary United x Mail Pooja Natarajan Outl x 3 UEMS Student Shipping H X Homework Help Q A from (x A ucumberlands blackboard com ultra courses 133892 1 cl outline Apps G https www goog YouTube M gmail il Cast in Deception International Grad DA The Comprehensi GCKey Sign In N Netflix ) World Education S Drugs FDA FDA About Us Careers Careers EnClear Opportunities A T Mulunaunal, Of Uloval, Corpulauons Help 2 Multinational versus Domestic Financial Management 3 Understand Exchange Rates X Tools 4 Understand the Fixed Exchange Rate System 5 Understand Floating Exchange Rates 6 Government Intervention in Foreign Exchange Markets My Groups 7 Other Exchange Rate Systems No Local Currency, Pegged Rates, and Managed Floating Rates Forward Exchange Rates and Risk Management Group 3 Team 8 Interest Rate Parity Cumberland 9 Purchasing Power Parity 10 Inflation, Interest Rates, and Exchange Rates 18 11 International Money and Capital Markets 12 Multinational Capital Budgeting 13 International Capital Structures 14 Multinational Working Capital Management Problem Set 15 Attached Files Building a Model Marvel Renovations xIsx (19 843 KB) Start with the partial model in the file attached Marvel Pence, CEO of Marvel's Renovations, a custom building and repair company, is preparing documentation for a line of credit request from his commercial banker Among the required documents is a detailed sales forecast for parts of 2020 and 2021 Sales Labor and Raw Materials X May, 2020 $75,000 $80,000 June, 2020 $115,000 $75,000 July, 2020 $145,000 $105,000 August, 2020 $125,000 $85,000 September, 2020 $120,000 $65,000 October, 2020 $95,000 $70,000 November, 2020 $75,000 $30,000 December, 2020 $55,000 $35,000 January, 2021 $45,000 N A Estimates obtained from the credit and collection department are as follows collections within the month of sale, 20 collections during the month following the sale, 60 collections the second month following the sale, 25 Payments for labor and raw materials are typically made during the month following the one in which these costs were incurred Total costs for labor and raw materials are estimated for each month as shown in the table General and administrative salaries will amount to approximately $25,000 a month lease payments under long term lease contracts will be $7,000 a month depreciation charges will be $8,000 a month miscellaneous expenses will be $5,000 a month income tax payments of $30,000 will be due in both August and December and a progress payment of $95,000 on a new office suite must be paid in October Cash on hand on July 1 will amount to $70,000, and a minimum cash balance of $30,000 will be maintained throughout the cash budget period a Prepare a monthly cash budget for the last 6 months of 2020 b Prepare an estimate of the required financing (or excess funds) that is, the amount of money Marvel's Renovations will need to borrow (or will have available to invest) for each month during that period c If its customers began to pay late, this would slow down collections and thus increase the required loan amount Also, if sales dropped off, this would have an effect on the required loan amount Perform a sensitivity analysis that shows the effects of these two factors on the maximum loan requirement Submit your answers in a Word and or Excel document Priv Tern

Question

Excel File Edit View Insert Format Tools Data Window Help 4 6 1 100  27 Fri 10 38 Q      Building a Model   Marvel Renovations xlsx Q v Search Sheet Home Insert Page Layout Formulas Data Review View '  Share K Cut Aria 10   A  A       29 Wrap Text General Normal Bad Good Neutral Calculation X   AutoSum Copy Fill Paste Format  Merge   Center $   2 08 08 Conditional Format Check Cell Explanatory     Input Linked Cell Not Insert Delete Format Sort   Formatting as Table Clear Filter R52 X D E G H K LMN 0 Q R U V W X Z AA AB AC AD AE AF AG AH AI Input Data Collections during month of sale 20  Collections during month after sale 60  Note  When the percent collected during Collections during second month after sale 25  the second month after sale is changed, Lease payments $7,00 the percent for collections during month Target cash balance  30,000 after sale is automatically changed so General and administrative salaries $25,000 that 100  of sales are collected during Depreciation charges $8,000 the three month period  10 Income tax payments (Sep   Dec) $30,000 11 Miscellaneous expenses $5,000 12 New office suite payment (Oct) $95,000 13 Cash on hand July 1 $70,000 15 Sales, labor, and RM adjustment factor 16 a  Prepare a monthly cash budget for the last six months of the year  19 May June July August September October November December January 20 Original sales estimates $75,00 $115,000 $145,000 $125,000 $120,000 $95,000 $75,000 $55,000 $45,000 21 Original labor and raw mat  estimates $80,000 $75,000 $105,000 $85,000 $65,000 $70,000 $30,000 $35,000 22 23 Forecasted Sales 4 Sales (gross) 25 26 Collections 27 During month of sale 28 During 1st month after sale 29 During 2nd month after sale 30 Total collections 31 32 Purchases 33 Labor and raw materials 34 Payments for labor and raw materials 35 36 Payments 7 Payments for labor and raw materials 38 General and administrative salaries 39 Lease payments 40 Miscell 1 Income tax payments 42 Design studio payment 43 Total payments 44 45 Net Cash Flows 6 Cash on hand at start of forecast period 47 Net cash flow (NCF)  Total collections   Total payments Cumulative NCF  Prior month cumulative   this month's NCF 49 Cash Surplus (or Loan Requirement) 51 Target cash balance 52 Surplus cash or loan needed  Cum NCF   Target cash 53 54 Max  Loan 55 56 b  Prepare an estimate of the required financing (or excess funds) that is, the amount of money 57 Marvel's Renovations will need to borrow (or will have available to invest) for each month during that period  58 59 60 61 62 63 Build a Model   Ready   100 Excel File Edit View Insert Format Tools Data Window Help 4 6 1 100  27 Fri 10 39 Q      Building a Model   Marvel Renovations xlsx Q v Search Sheet Home Insert Page Layout Formulas Data Review View '  Share K Cut Aria 10   A  A       29 Wrap Text General Normal Bad Good Neutral Calculation I X   AutoSum Copy Fill Paste BI UTY   A  Merge   Center $   2 08 08 Conditional Format Check Cell Explanatory     Input Linked Cell Not Insert Format Delete Format Sort   Formatting as Table Clear Filter R52 X H K LMN 0 Q V W Z AA AB AC AD AE AF AG AH AI b  Prepare an estimate of the required financing (or excess funds) that is, the amount of money $7 Marvel's Renovations will need to borrow (or will have available to invest) for each month during that period  59 60 61 62 63 64 e  If its customers began to pay late, this would slow down collections and thus increase the required loan amount  Also, if s sales dropped off, this would have an effect on the required loan  Do a sensitivity analysis that shows the effects of these 66 two factors on the max loan requirement  Assume the purchases of labor and raw material also vary by the sales adjustment 67 factor  68 69 Answer  70 71 72 109 110 114 115 116 117 118 Build a Model   Ready   100 Pipeline   Ocular Therapeutic x D Home   DEXTENZA   Content X Google Flights x Review trip itinerary   United x Mail   Pooja Natarajan   Outl x 3 UEMS   Student Shipping   H X Homework Help   Q A from (x   A ucumberlands blackboard com ultra courses  133892 1 cl outline Apps G https   www goog      YouTube M gmail il Cast in Deception   International Grad    DA The Comprehensi  GCKey   Sign In      N Netflix ) World Education S   Drugs FDA  FDA   About Us   Careers Careers   EnClear    Opportunities   A  T  Mulunaunal, Of Uloval, Corpulauons Help 2  Multinational versus Domestic Financial Management 3  Understand Exchange Rates X Tools 4  Understand the Fixed Exchange Rate System 5  Understand Floating Exchange Rates 6  Government Intervention in Foreign Exchange Markets My Groups 7  Other Exchange Rate Systems  No Local Currency, Pegged Rates, and Managed Floating Rates Forward Exchange Rates and Risk Management Group 3   Team 8  Interest Rate Parity Cumberland 9  Purchasing Power Parity 10  Inflation, Interest Rates, and Exchange Rates 18 11  International Money and Capital Markets 12  Multinational Capital Budgeting 13  International Capital Structures 14  Multinational Working Capital Management Problem Set  15 Attached Files  Building a Model   Marvel Renovations xIsx (19 843 KB) Start with the partial model in the file attached  Marvel Pence, CEO of Marvel's Renovations, a custom building and repair company, is preparing documentation for a line of credit request from his commercial banker  Among the required documents is a detailed sales forecast for parts of 2020 and 2021 Sales Labor and Raw Materials X May, 2020 $75,000 $80,000 June, 2020 $115,000 $75,000 July, 2020 $145,000 $105,000 August, 2020 $125,000 $85,000 September, 2020 $120,000 $65,000 October, 2020 $95,000 $70,000 November, 2020 $75,000 $30,000 December, 2020 $55,000 $35,000 January, 2021 $45,000 N A Estimates obtained from the credit and collection department are as follows  collections within the month of sale, 20   collections during the month following the sale, 60   collections the second month following the sale, 25   Payments for labor and raw materials are typically made during the month following the one in which these costs were incurred  Total costs for labor and raw materials are estimated for each month as shown in the table  General and administrative salaries will amount to approximately $25,000 a month  lease payments under long term lease contracts will be $7,000 a month  depreciation charges will be $8,000 a month  miscellaneous expenses will be $5,000 a month  income tax payments of $30,000 will be due in both August and December  and a progress payment of $95,000 on a new office suite must be paid in October  Cash on hand on July 1 will amount to $70,000, and a minimum cash balance of $30,000 will be maintained throughout the cash budget period  a  Prepare a monthly cash budget for the last 6 months of 2020  b  Prepare an estimate of the required financing (or excess funds) that is, the amount of money Marvel's Renovations will need to borrow (or will have available to invest) for each month during that period  c  If its customers began to pay late, this would slow down collections and thus increase the required loan amount  Also, if sales dropped off, this would have an effect on the required loan amount  Perform a sensitivity analysis that shows the effects of these two factors on the maximum loan requirement  Submit your answers in a Word and or Excel document  Priv Tern

Accepted Answer

The Answer is in the image, click to view ...

Question

Excel File Edit View Insert Format Tools Data Window Help 4 6 1 100% 27 Fri 10:38 Q .. . Building a Model - Marvel

Step by Step Solution

Step: 1

Get Instant Access with AI-Powered Solutions

Step: 2

Step: 3

Ace Your Homework with AI

Recommended Textbook for

Elementary Statistics

Students also viewed these Accounting questions

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question