I have this homework and I know it's easy but I want to have it done correctly.

Cash Budget for Toy Planet, Inc. Toy Planet is a plastic toy manufacturer with a very seasonal production and sales. Construct a Cash Budget for the company given the following assumptions: 1 Actual and expected sales for July through March are in the worksheet "Exp. Sales." Construct your Cash Budget for the period October through February 2 30% of total sales are in cash and the rest on credit. According to the firm's credit policy, the firm collects 60% of credit sales the following month, 30% two months after an 3 Inventory purchases are estimated to represent 45% of the of the next month's sales (e.g. June purchases are 45% of ex The firm pays 40% of its inventory purchases in the same month and 60% in the following month; nevertheless, the firm enjoys a 2% discount if it pays during the first month of the purchase. 4 Monthly wages and other variable expenses are expected at 25% of the current month's sales 5 Fixed expenses are about 30,000 each month. 6 A major capital outlay of $70,000 for office space updating and repair is expected at the end of October. However, the m expenditure and can postpone it until November or December, but no later than that. 7 Amortized long-term debt with $50,000 payments due every quarter matures in February 2020 8 Sales taxes are 5% of quarterly sales and must be paid in January, April, July, and October 9 Income tax prepayments of $25,000 are also quarterly and due January, April, July, and October 10 The bank that extended long-term debt to the company requires minimum cash balance of $50,000 every month. Any shortfall in cash must be covered by short-term borrowing. This borrowing incurrs interest of 7% per year Ending cash balance in September was $85,000. Use Scenario Manager to answer the following questions 11 What would be a better timing of the investment outlay of $70,000 if the objective is to minimize short-term borrowing 12 Mr. George Seinfeld, the Toy Planet's CEO, is considering three credit proposals from the firm's suppliers. In the first proposal the firm will pay 60% of its purchases in the same month and 40% in the following month; in the second proposal the firm will pay 40% in the same month and 60% in the following month; in the third proposal the firm will pay 20% of its purchases in the same month and 80% in the following month. Suppliers have offered 3%, 2%, and 1% discounts over the payments made during the same month of the purchase if th What credit proposal results in the lowest need for short-term borrowing and the lowest total interest cost? Excel tasks 1 Create dates using a DATE or EDATE function. Link all dates to just one input date. 2 Create title that automatically updates when the dates are changed 3 Complete the model using formulas wherever possible 4 Use functions IF, OR, MIN, and MAX 5 Highlight inputs 6 Use Scenario Manager 7 Name cells month, 30% two months after and the remainder in the third month after the sale g. June purchases are 45% of expected July sales.) e end of October. However, the management is flexible about the timing of this e of $50,000 every month. nterest of 7% per year minimize short-term borrowing and interest cost? e firm's suppliers. n the following month; in the following month. ame month of the purchase if the firm pays according to the first, second, and third proposal respectively. st total interest cost? TOY PLANET Actual and expected sales for 2015-16 July August September October November December January February March *Actual Sales 177,250 * 186,520 * 196,523 * 300,000 450,000 500,000 200,000 180,000 170,000 TOY PLANET Cash Budget For the Period October,2015 Throu Input Method Sales Preliminary Calculations Cash Collections Cash Sales First Month After Second Month After Third Month After Total Collections Inventory Purchases Payments for Inventory Purchases During Same Month Discount First Month After Total Payments for Inventory Purchases Cash Budget Cash Collections LessCash Disbursments Payment for Inventory Purchases Wages and Other Variable Expenses Fixed Expenses Capital Investment Long-Term Debt Installment Interest on Short-term Borrowing Sales Tax Income tax Prepayments Total Disbursements Beginning Cash Balance + Collections - Disbursements Unadjusted Cash Balance +Short-term Borrowing (Repayment) Ending Cash Balance 30.0% of sales current month 42.0% of sales previous month 21.0% of sales two months before 7.0% of sales three months before 100.0% 45% of next month sales 40% of inventory purchase current month 2% from same month pyments 60% of inventory purchase previous month from line 14 from line 21 25% of current sales 30,000 same each month 70,000 in October but can be postpond to Nov,Dec TOY PLANET Cash Budget riod October,2015 Through March,2016 7/1/2015 177,250 8/1/2015 186,520 Forecast period 9/1/2015 10/1/2015 11/1/2015 12/1/2015 1/1/2016 196,523 300,000 450,000 500,000 200,000 135,000 2/1/2016 180,000 90,000 82,540 39,169 12,408 224,116 135,000 126,000 41,270 13,056 315,326 150,000 189,000 63,000 13,757 415,757 60,000 210,000 94,500 21,000 385,500 54,000 84,000 105,000 31,500 274,500 202,500 225,000 90,000 81,000 76,500 81,000 (1,620) 81,000 160,380 90,000 (1,800) 121,500 209,700 36,000 (720) 135,000 170,280 32,400 (648) 54,000 85,752 30,600 (612) 48,600 78,588 224,116 315,326 415,757 385,500 274,500 160,380 75,000 30,000 70,000 209,700 112,500 30,000 0 170,280 125,000 30,000 0 85,752 50,000 30,000 0 78,588 45,000 30,000 0 3/1/2016 170,000 Homework 3 - GRADED FIRE312, Summer 2016 This homework is due on Sunday, 5/29/2016, by the end of the day. Please upload the completed homework to the Blackboard. Provide all answers in a single workbook that is easy to readavigate. You can use multiple worksheets. This homework is individual. Any evidence of copying from your classmates will be penalized by assigning a grade of zero. The Blackboard is restricted to allow you to submit the homework only once! Make sure that the file you upload and submit is the correct one. Assignment: The Precise Speed Inc., a laser printer manufacturer, has estimated the following sales for 2016: Actual Sales 2015 Nov. Dec. $375,000 $266,667 Jan. $200,000 Feb. $350,000 Estimated Sales 2016 Mar. Apr. May $450,000 $350,000 $250,000 Jun. $200,000 Jul. $300,000 20% of sales are for cash, 80% of sales are on credit. The firm collects 60% of the credit sales during the first month after the sale and the remainder during the following second month. Inventory purchases constitute 60% of the next month's sales. All inventory purchases are made on credit. The company pays for 50% of inventory purchases during the first month after the purchase was made and for the remainder in the next month. Monthly wages, taxes and other expenses are expected to be 30% of the current month's sales. A major capital expenditure of $50,000 is expected in March. Installments of amortized long-term debt are expected to be $90,000 every quarter in February, May, August, and November (write a formula for these payments using dates as inputs as we did in class). The company needs a minimum cash balance of $25,000. The cash balance at the end of December 2015 was $35,000. a. Using the simple cash budget example from Chapter #3 in the textbook or from the class, help the financial staff of the Precise Speed Inc. to prepare their cash budget for January through June 2016. Assume that the firm uses any cash surplus above the required minimum cash balance to pay off its short-term borrowing monthly and that the annual interest on its short-term borrowing is 8%. Construct a budget title that indicates the time period for the budget. The title should be linked to the dates in the budget and should change whenever the dates change. b. Assume that the Precise Speed Inc. has some flexibility in scheduling the major capital expenditure of $50,000 expected in March. Specifically, it can postpone the expenditure until April or May, but no later than that. Use the Scenario Manager to see in what month (March, April, or May) the Precise Speed Inc. should make this expenditure in order to minimize the maximum borrowing and short-term interest. Create a scenario summary using the example from Chapter #3 in the textbook or using the example we did in class. Make sure to name the cells that are inputs to the scenario manager. Please answer the question: What month would be the best for scheduling the capital expenditure? Cash Budget for Toy Planet, Inc. Toy Planet is a plastic toy manufacturer with a very seasonal production and sales. Construct a Cash Budget for the company given the following assumptions: 1 Actual and expected sales for July through March are in the worksheet "Exp. Sales." Construct your Cash Budget for the period October through February 2 30% of total sales are in cash and the rest on credit. According to the firm's credit policy, the firm collects 60% of credit sales the following month, 30% two months after an 3 Inventory purchases are estimated to represent 45% of the of the next month's sales (e.g. June purchases are 45% of ex The firm pays 40% of its inventory purchases in the same month and 60% in the following month; nevertheless, the firm enjoys a 2% discount if it pays during the first month of the purchase. 4 Monthly wages and other variable expenses are expected at 25% of the current month's sales 5 Fixed expenses are about 30,000 each month. 6 A major capital outlay of $70,000 for office space updating and repair is expected at the end of October. However, the m expenditure and can postpone it until November or December, but no later than that. 7 Amortized long-term debt with $50,000 payments due every quarter matures in February 2020 8 Sales taxes are 5% of quarterly sales and must be paid in January, April, July, and October 9 Income tax prepayments of $25,000 are also quarterly and due January, April, July, and October 10 The bank that extended long-term debt to the company requires minimum cash balance of $50,000 every month. Any shortfall in cash must be covered by short-term borrowing. This borrowing incurrs interest of 7% per year Ending cash balance in September was $85,000. Use Scenario Manager to answer the following questions 11 What would be a better timing of the investment outlay of $70,000 if the objective is to minimize short-term borrowing 12 Mr. George Seinfeld, the Toy Planet's CEO, is considering three credit proposals from the firm's suppliers. In the first proposal the firm will pay 60% of its purchases in the same month and 40% in the following month; in the second proposal the firm will pay 40% in the same month and 60% in the following month; in the third proposal the firm will pay 20% of its purchases in the same month and 80% in the following month. Suppliers have offered 3%, 2%, and 1% discounts over the payments made during the same month of the purchase if th What credit proposal results in the lowest need for short-term borrowing and the lowest total interest cost? Excel tasks 1 Create dates using a DATE or EDATE function. Link all dates to just one input date. 2 Create title that automatically updates when the dates are changed 3 Complete the model using formulas wherever possible 4 Use functions IF, OR, MIN, and MAX 5 Highlight inputs 6 Use Scenario Manager 7 Name cells month, 30% two months after and the remainder in the third month after the sale e.g. June purchases are 45% of expected July sales.) he end of October. However, the management is flexible about the timing of this nce of $50,000 every month. interest of 7% per year o minimize short-term borrowing and interest cost? he firm's suppliers. in the following month; ing month; % in the following month. same month of the purchase if the firm pays according to the first, second, and third proposal respectively. est total interest cost? TOY PLANET Actual and expected sales for 2015-16 July August September October November December January February March *Actual Sales 177,250 * 186,520 * 196,523 * 300,000 450,000 500,000 200,000 180,000 170,000 Scenario Summary Current Values: Changing Cells: CAPEX_OCt 70,000 CAPEX_NOV 0 CAPEX_DEC 0 $B$18 40% Discount 2% Result Cells: Total_interest 2,764 maximum_borrowing 216,906 Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray. Capital Outlay in October Capital Outlay in November Capital Outlay in December 70,000 0 0 40% 2% 0 70,000 0 40% 2% 0 0 70,000 40% 2% 2,764 216,906 2,351 216,498 1,940 146,498 First Credit Proposal Suppliers Second Credit Proposal Suplliers Third Credit Proposal Suplliers 70,000 0 0 60% 3% 70,000 0 0 40% 2% 70,000 0 0 2% 1% 4,690 298,356 2,764 216,906 649 57,351 Scenario Summary Current Values: Changing Cells: CAPEX_OCt 0 CAPEX_NOV 0 CAPEX_DEC 70,000 $B$18 20% Discount 1% Result Cells: Total_interest 1,904 maximum_borrowing 138,819 Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray. Capital Outlay in October Capital Outlay in November Capital Outlay in December 70,000 0 0 20% 1% 0 70,000 0 20% 1% 0 0 70,000 20% 1% 2,733 201,400 2,318 200,991 1,904 138,819 First Credit Proposal Suppliers Second Credit Proposal Suplliers Third Credit Proposal Suplliers 0 0 70,000 60% 3% 0 0 70,000 40% 2% 0 0 70,000 20% 1% 2,006 160,290 1,940 146,498 1,904 138,819 TOY Cash For the periodOcto Input Sales Preliminary Calculations Cash Collections Cash Sales First Month After Second Month After Third Month After Total Collections Inventory Purchases Payments for Inventory Purchases During Same Month Discount First Month After Total Payments for Inventory Purchases Cash Budget Cash Collections Less Cash Disbursments Payment for Inventory Purchases Wages and Other Variable Expenses Fixed Expenses Capital Investment Long-Term Debt Installment Interest on Short-term Borrowing Sales Tax Income tax Prepayments Total Disbursements Beginning Cash Balance + Collections - Disbursements Unadjusted Cash Balance +Short-term Borrowing (Repayment) Ending Cash Balance Minumum Cash Balance Cumulative short-term borrowing (short-term debt balance) All inputs are highlighted in blue 30% 42% 21% 7% 100% 45% 20% 1% 80% 25% 30,000 70,000 50,000 0.58% 5% 25,000 85,000 50,000 TOY PLANET Cash Budget For the periodOctober 15y through February 16y Forec Method 7/1/2015 177,250 8/1/2015 186,520 9/1/2015 10/1/2015 196,523 300,000 of sales current month of sales previous month of sales 2 months before of sales3 months before of next month sales 90,000 82,540 39,169 12,408 224,116 135,000 of inventorey purchase current month from same month payments of inventorey purchase previous month 202,500 40,500 (405) 108,000 148,095 From line 14 224,116 from line 21 of sales same each month In October but can be postponed to Nov, Dec. Installments due in months 2,5,8,11 monthly intrest rate on prior month debt balances Quaterly sales, due in months 1,4,7,10 due in months 1,4,7,10 148,095 75,000 30,000 0 0 28014.65 25000 306,110 borrowing positive, repayment negative ending cash balance in September 85,000 85,000 (81,993) 3,007 46,993 50,000 0 46,993 Forecast Period 11/1/2015 12/1/2015 1/1/2016 450,000 500,000 200,000 2/1/2016 180,000 135,000 126,000 41,270 13,056 315,326 150,000 189,000 63,000 13,757 415,757 60,000 210,000 94,500 21,000 385,500 54,000 84,000 105,000 31,500 274,500 225,000 45,000 (450) 162,000 206,550 90,000 18,000 (180) 180,000 197,820 81,000 16,200 (162) 72,000 88,038 76,500 15,300 (153) 64,800 79,947 315,326 415,757 385,500 274,500 206,550 112,500 30,000 0 50000 274 0 0 399,324 197,820 125,000 30,000 70,000 0 764 0 0 423,584 88,038 50,000 30,000 0 0 810 62500 25000 256,348 79,947 45,000 30,000 0 50000 56 0 0 205,003 50,000 (83,998) (33,998) 83,998 50,000 50,000 (7,828) 42,172 7,828 50,000 50,000 129,152 179,152 (129,152) 50,000 50,000 69,497 119,497 (9,666) 109,830 130,991 138,819 9,666 - 3/1/2016 170,000 =IF(OR(MONTH(K5)=2,MONTH(K5)=5,MONTH(K5)=8,MONTH( 1,904