Fit Life Inc., a sports equipment retailer, needs to prepare a cash budget for the first quarter of 2018. The financial staff at Fit Life has forecasted the following sales figures:

January February March April May

$100,000 $150,000 $300,000 $250,000 $150,000

Actual sales in October, November, and December 2017 were $125,000, $146,000, and $125,000, respectively. Cash sales are 40% of the total, and the rest are on credit. Under the current credit policy the firm expects to collect 60% of credit sales the following month, 30% two months after, and the remainder in the third month after the sale. Each month, the firm makes inventory purchases equal to 45% of the of the next months sales. The firm pays for 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 same month as the purchase. Estimated disbursements include monthly wages and other expenses representing 25% of the same months sales; a major capital outlay of $30,000 expected in January; a dividend payment of $25,000 in February; $40,000 of long-term debt maturing in March; and a tax payment of $60,000 in April. The interest rate on its short-term borrowing is 7%. It has a required minimum cash balance of $10,000 every month, and has an ending cash balance of $30,000 for December 2017. Here's the pseudocode for current borrowing: If we have less than the required amount of cash, then we borrow enough to get to the minimum. If not, then we need to know if we have any cumulative borrowing we could pay down. If there's cumulative borrowing, then we have to determine if how much we can pay. We cannot pay more than our extra cash or more than cumulative borrowing (recall MIN function and use - if we are repaying). If there's no cumulative borrowing to repay, then it's 0.

a. Using the above information, create a cash budget for January to June 2018. The cash budget should account for short-term borrowing and payback of outstanding loans.

b. Using Excels outline feature, group the worksheet area at the top of the cash budget so that the preliminary calculations can be easily hidden or unhidden.

c. The Fit Lifes CFO is considering three credit proposals from the firms supplier. In the first proposal the firm will pay 75% of its purchases in the same month and 25% in the following month; in the second proposal the firm will pay half in the same month and half in the following month; in the third proposal the firm will pay 25% of its purchases in the same month and 75% in the following month. Suppliers have offered 4%, 3%, and 2% discounts over the payments made during the same month of the purchase if the firm pays according to the first, second, and third proposals, respectively. Also create a scenario to reflect the current credit policy (total of four scenarios). The CFO has asked you to use the Scenario Manager to see which proposal has the lowest total interest cost. You'll need a cell that adds up the short-term interest for January-April.

d. The CFO is now considering three credit policies from the firms customers. In the first policy the firm will sell 60% on cash and will collect 60% of the balance during the first month, and the remaining balance during the second month. In the second policy, 50% of sales will be on cash, and the firm will collect 50%, 30%, and 20% of credit sales during the first, second, and third months, respectively. The last policy consists of 40% sales on cash, and 40%, 30%, and 30% of the remaining balance will be collected during the first, second, and third months, respectively. The CFO has asked you to use the Scenario Manager to see what credit proposal has the lowest total interest cost. Also create a scenario to reflect the current credit policy (total of four scenarios). You'll again use the cell that adds up the short-term interest for January-April. You can create the 4 scenarios for part c and the 4 scenarios for part d in the same sheet. You will need to use different changing cells for the second set of scenarios (part d). When you do a scenario summary, just delete the columns that you do not need for that report. For both c and d, create a scenario summary and be sure to clean it up as demonstrated in class.

AutoSave Off H2B Chapter4 (1).xlsx - Excel Chanati Sushma C 7 File Home Insert Draw Page Layout Formulas Data Review View Help Power Pivot O Search Share Comments Insert Calibri == 11 A ab Wrap Text General AY O s 9X Delete LIV Paste BIU ara- EE E Merge & Center Ideas y $ -% -28 Sensitivity Format Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Editing Clipboard Font Alignment Number Cells Ideas Sensitivity A4 B K L M N O P T U V z AL October E December Januar H March November G Februar April May 4 5 Sales 6 weicos: 7 7 Cash 8 First Month 9 Second Month 10 Third Month 11 Total Collections 12 Purchases 13 Pawments 14 First Month 15 Second Month 16 Total Payments 17 18 Collections 19 Zess sw.semes: 20 Inventory Payments 21 Wages 22 Lease Payment 23 Interest 24 Short-term Interest Expenselno) 25 Dividend (Common 26 Taxes 27 Capital Outlays 28 Total Disbursements 29 Beginning cash balance 30 Collections-disbursements 31 Unadjusted Cash Balance 32 Current Borrowing 33 Current Investing 34 Ending Cash Balance 35 cumulative borrowing Investing) 36 Cumulative Interest Expenselno) 37 Notes: 38 Minimum Acceptable Cash 39 Maximum Acceptable cash 40 Borrowing Rate Annual) 41 Lending Rate Annual) ) Scenario Summary Cash Budget | Cash Budget (2) Cash Budget (3) Ready Average: 43115.5 Count: 44 Sum: 344924 B + 60% Type here to search 5:08 PM 9/18/2020 AutoSave Off H2B Chapter4 (1).xlsx - Excel Chanati Sushma C 7 File Home Insert Draw Page Layout Formulas Data Review View Help Power Pivot O Search Share Comments Insert Calibri == 11 A ab Wrap Text General AY O s 9X Delete LIV Paste BIU ara- EE E Merge & Center Ideas y $ -% -28 Sensitivity Format Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Editing Clipboard Font Alignment Number Cells Ideas Sensitivity A4 B K L M N O P T U V z AL October E December Januar H March November G Februar April May 4 5 Sales 6 weicos: 7 7 Cash 8 First Month 9 Second Month 10 Third Month 11 Total Collections 12 Purchases 13 Pawments 14 First Month 15 Second Month 16 Total Payments 17 18 Collections 19 Zess sw.semes: 20 Inventory Payments 21 Wages 22 Lease Payment 23 Interest 24 Short-term Interest Expenselno) 25 Dividend (Common 26 Taxes 27 Capital Outlays 28 Total Disbursements 29 Beginning cash balance 30 Collections-disbursements 31 Unadjusted Cash Balance 32 Current Borrowing 33 Current Investing 34 Ending Cash Balance 35 cumulative borrowing Investing) 36 Cumulative Interest Expenselno) 37 Notes: 38 Minimum Acceptable Cash 39 Maximum Acceptable cash 40 Borrowing Rate Annual) 41 Lending Rate Annual) ) Scenario Summary Cash Budget | Cash Budget (2) Cash Budget (3) Ready Average: 43115.5 Count: 44 Sum: 344924 B + 60% Type here to search 5:08 PM 9/18/2020