Question1 (40 marks)
Refer to Table 1. Write the Excel formula for each cell marked with “?” in column C such that formula could be copied and pasted into columns D and E using Microsoft Excel without further editing. There is no need to explicitly write the Excel formula for cells marked with “copy & paste”.
New Age Dolls makes the very successful line of Micky and Mimi dolls, which rapidly gained popularity with children and teenagers after only a few years on the market. New Age Dolls produces dolls at two locations: St. Louis (STL) and Boston (BSN), and has been leasing warehouses at both locations. Warehouses are used for storage and for distribution to retail stores. New Age management is not happy with the terms of the leases, which are too expensive. The lease expires at the end of 2021. New Age management has decided to build its own warehouses and has three possible locations: Portland(PT), Atlanta(AT) and Phoenix(PX) (which is entered in cell C22), but New Age would construct warehouses at only two of these locations. To choose these two locations, New Age would like to forecast the net income, debt owed and cash flow for the following three years (2022 to 2024) based on 2021’s data. You are asked to help New Age and write Excel formulas in cells C25 to C76 for this forecast by performing a what-if analysis using Microsoft Excel. One major factor that affects the forecast is the expected state of economy over the three years (2022 to 2024) in row 21:
- Flat (F) – a steady economic outlook for a year
- Hot (H) – a heated-up economic outlook for a year
The expected state of economy can vary from year to year. If the expected state of economy is H for year 2022, F for year 2023, and F for year 2024, then the pattern HFF would be entered in cells C21 to E21.
Assume that there are no input errors (or typing mistakes) in row 21, or c22 cell.
New Age management expects to sell all the dolls they produce in Boston and St. Louis in the three years (2022 to 2024). Production of the two types of dolls will be divided evenly between the two locations. The expected production (and sales) levels are given below:
|Year 2022||Year 2023||Year 2024|
|Micky – St. Louis||500,000 (cell C4)||750,000 (cell D4)||1,000,000 (cell E4)|
|Mimi – St. Louis||750,000 (cell C5)||850,000 (cell D5)||1,000,000 (cell E5)|
|Micky – Boston||500,000 (cell C6)||750,000 (cell D6)||1,000,000 (cell E6)|
|Mimi – Boston||750,000 (cell C7)||850,000 (cell D7)||1,000,000 (cell E7)|
To reduce distribution and storage risks (from strikes, natural disasters etc.), management plans to send half of each plant’s production to each of the two selected warehouse sites. Shipping costs from each plant to each of the three warehouse sites differ. Storage costs at each of the three warehouse sites differ as well. The shipping and storage costs per doll are given below:
|(From)Factory||(To)Warehouse||Year 2022||Year 2023||Year 2024|
|St. Louis||Portland||4.5 (cell C9)||4.95(cell D9)||5.45(cell E9)|
|St. Louis||Atlanta||5.5 (cell C10)||6.05(cell D10)||6.66(cell E10)|
|St. Louis||Phoenix||3.5 (cell C11)||3.85(cell D11)||4.24(cell E11)|
|Boston||Portland||6.0 (cell C12)||6.6 (cell D12)||7.26(cell E12)|
|Boston||Atlanta||4.5 (cell C13)||4.95(cell D13)||5.45(cell E13)|
|Boston||Phoenix||5.5 (cell C14)||6.05(cell D14)||6.66(cell E14)|
The following constants (rows 15 to 17) for the forecast are described below:
- Tax rate (row 15): The corporate tax rate is expected to increase from 29% for year 2022 to 31% for year 2024.
- Minimum cash needed to start next year (row 16): A New Age’s policy is to have at least US$10,000 cash on hand at the end of each year,in order to start next year‘s business. It is assumed that New Age’s banker will lend whatever is needed at the end of a year to begin the next year with US$10,000.
- Administrative costs will be fixed at US$1,200,000 each year.
EasyDue™ 支持PayPal, AliPay, WechatPay, Taobao等各种付款方式!
E-mail: firstname.lastname@example.org 微信:easydue